bubblegum.anarchy
Members-
Posts
526 -
Joined
-
Last visited
Never
Everything posted by bubblegum.anarchy
-
maybe this: SELECT network.nid , network.userid , network.friendid , IF (owner.userid IS NULL, friend.username, owner.username) AS username , IF (owner.userid IS NULL, friend.level, owner.level) AS level , IF (owner.userid IS NULL, friend.main_image, owner.main_image) AS main_image FROM network LEFT JOIN users owner ON network.userid = owner.userid LEFT JOIN users friend ON network.userid = friend.userid) WHERE (n.friendid = $userid OR n.userid = $userid) AND ap = 1
-
[SOLVED] A Really easy project, but need help
bubblegum.anarchy replied to tinmanbf's topic in MySQL Help
I do not see any code there that is even attempting to display the time difference. A crobjob or windows task manager can update the database at a particular time of the day, depending on platform. -
mysql_fetch_field alternative with large table
bubblegum.anarchy replied to leo_divinci's topic in MySQL Help
use mysql_num_rows($MysqlQueryString) to see if there are any rows returned: function getColumnID($column) { if(mysql_fetch_field($column)) <- Returns an error is false { $MysqlQueryString = mysql_query('SELECT `'.$column.'` FROM `test` WHERE `id`=1 LIMIT 1'); if (mysql_num_rows($MysqlQueryString)) { $MysqlRow = mysql_fetch_row($MysqlQueryString); return $MysqlRow[$column]; } else return NULL; } else return NULL; } EDIT: I just realized there was no mysq_query call to get a result set!! -
Default value to store semi static variable
bubblegum.anarchy replied to bubblegum.anarchy's topic in MySQL Help
How is ALTER COLUMN implemented? -
I thought using the default value of a table column would be a good way to store a semi static variable (rarely updated) but I seem to have come upon a problem when the record count is increase greatly. The following query to update the default value startes to take over a second to process once the record count is over 100,000 (>500,000 takes about 3 seconds and over a million would take over 6 seconds since the process time is very proportional): ALTER TABLE reimbursement MODIFY rate FLOAT NOT NULL DEFAULT 0.95 COMMENT 'currency rate per kilometre'; The fact that updates are very rare to being with and the process time is arbitrary is beside the point here... the point is, why is the result of the above query: (655360 row(s)affected) (0 ms taken) ... or what ever record count row(s)affected ???? All that needs changing is the default value... ... why is there any affect on current records? ... and is there a simpler way to change the default value (with no affect on current records)? EDIT: What the heck does this mean?: ALTER COLUMN specifies a new default value for a column or removes the old default value. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value, as described in Section 13.1.5, “CREATE TABLE Syntax”.
-
[SOLVED] Returning number of occurence of distinct values
bubblegum.anarchy replied to jellis's topic in MySQL Help
SELECT booking, count(*) FROM bookings GROUP BY booking -
[SOLVED] A Really easy project, but need help
bubblegum.anarchy replied to tinmanbf's topic in MySQL Help
I would do something like this: DROP TABLE IF EXISTS scheduled_time; CREATE TABLE scheduled_time ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'unique idenfier reference', person_id INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'person.id foreign key reference', time_allowed TIME NOT NULL DEFAULT '00:00' COMMENT 'amount of time allowed', time_used TIME NOT NULL DEFAULT '00:00' COMMENT 'amout of time used', INDEX FK_PERSON_ID (person_id) ) TYPE=MyISAM; # insert new scheduled time record INSERT INTO scheduled_time (person_id, time_allowed) VALUES (1, '34:45'); # increment the time used UPDATE scheduled_time SET time_used = addtime(time_used, '02:30') WHERE scheduled_time.id = 1; # select the time remaining SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1; -
The one column in one record is never going to have two differenent values and hence will never return a record the following query: SELECT * FROM table WHERE column = 'ValueOne' AND column = 'ValueTwo' # will never return a record - impossible request
-
If the desire is to have rulesdesc `Other` at the bottom of the list: SELECT * FROM table ORDER BY rulesdesc = 'Other', rulesdesc
-
Holy Crap dude, off coarse SELECT p.pid, p.name_first is only going to display that information.
-
Does the following query also produce expected result? SELECT p.pid, p.name_first FROM table_enc AS e INNER JOIN table_person AS p ON e.pid = p.pid LEFT JOIN table_bac AS h ON e.encounter_nr = h.encounter_nr LEFT JOIN table_patho AS g ON e.encounter_nr = g.encounter_nr LEFT JOIN table_chem AS f ON e.encounter_nr = f.encounter_nr WHERE h.encounter_nr IS NOT NULL OR g.encounter_nr IS NOT NULL OR f.encounter_nr IS NOT NULL
-
Does the following query produce expected results? SELECT p.pid, p.name_first FROM table_enc AS e INNER JOIN table_person AS p ON e.pid = p.pid
-
what is the final `AND u.userid=m.userid` all about? written differently: UPDATE hr_user INNER JOIN hr_building ON hr_user.user_id = hr_building.userid SET hr_user.xp = hr_user.xp + hr_building.land + ( SELECT sum(hr_military.xp) FROM hr_military WHERE hr_military.userid = hr_user.user_id )
-
If all else fails, rebuilding the search_word_list based on the other existing tables is an option, considering that is probably how the table is populated in the first place.
-
As effigy stated. SELECT substr(value, 0, 75) AS value_trim FROM table
-
itazev: There still needs to be an explicit join in the query statement (?)
-
fenway may appreciate the code block with the actual table names rather than the variables, well I would anyway, but maybe fenway is not finding this entire thread as confusing as I am.
-
The INNER JOIN is intentional so as not to return superfluous records... like I said, a LEFT JOIN to the query block I provided, from the products_info table will result in all products being listed. So all that needs to be done is SELECT FROM product_info, INNER JOIN to manufacturers and finally LEFT JOIN to the query block I provided.
-
Personally, I find these types of queries confusing as all heck. The following should list the supplier_id, product_id and price of the lowest offer, and a left join to the resulting set should provide the appropriate information, including off coarse the products that have had no offers: SELECT derived.product_id, product_offers.supplier_id, derived.lowest_price FROM product_offers INNER JOIN ( SELECT product_id, min(price) AS lowest_price FROM product_offers GROUP BY product_id ) AS derived ON product_offers.product_id = derived.product_id AND product_offers.price = derived.lowest_price The above query assumes that product_id is used instead of product_name in the product_offers table.
-
product_offers offerid product_name supplier_id price should probably be: product_offers offerid product_id supplier_id price
-
is $tb_find_baclabor a variable or the table name?
-
now() should not be quoted, assuming that the GetSQLValueString() function returns a quoted string.
-
This may work accordingly: SELECT @id:=id FROM table WHERE id > ifnull(( SELECT id FROM table WHERE marker = 1 ), 0) ORDER BY id LIMIT 1; UPDATE table SET marker = 0 WHERE marker = 1; UPDATE table SET marker = 1 WHERE id = @id; The marker is set to the first record if no marker is present and just stops at the last record ordered by id.
-
fenway: As far as I understand, qwerpoiu338 is trying to move a marker across records in a table, the following table data: id | marker | other_fields_in_table_that_are_irrelevant 1 | 0 | 2 | 0 | 3 | 0 | 7 | 1 | 10 | 0 | 14 | 0 | after a marker move would result in the following (The marker moved to the next record): id | marker | other_fields_in_table_that_are_irrelevant 1 | 0 | 2 | 0 | 3 | 0 | 7 | 0 | 10 | 1 | 14 | 0 | And no qwerpoiu338, the query I posted would only work on records with NO gaps in the auto increment id.