bubblegum.anarchy
-
Posts
526 -
Joined
-
Last visited
Never
Posts posted by bubblegum.anarchy
-
-
The query looks fine syntactically, maybe the tbl prefix is an issue... try again with the table name and column names wrapped in backticks:
SELECT * FROM `tbl` WHERE CHAR_LENGTH(`col`) > 5;
-
I recently learnt from fenway that mysql 5.1 has an inbuilt event scheduler:
-
or mysql_insert_id() in php.
-
Also consider wrapping the strings with html content in single quotes so that the following:
$departments .= "<select name=\"user_department\" size=\"5\" id=\"user_department\">$lst_departments</option>";
Would be more readable in this format:
$departments .= '<select name="user_department" size="5" id="user_department">$lst_departments</option>';
-
yeah... event_rsvp would certainly make a good double for an invitation record with date set to null as default making the NOT rsvp'd query a great deal simpler:
event_rsvp.id
event_rsvp.event_id
event_rsvp.member_id <= forgot this member earlier
event_rsvp.date
SELECT member.id , member.name , event.name FROM event_rsvp INNER JOIN event ON event_rsvp.event_id = event.id INNER JOIN member ON event_rsvp.member_id = member.id WHERE event_rsvp.event_id = $event_id AND event_rsvp.date IS NULL
much better!
-
To get a list of all members that have NOT rsvp'd an event:
SELECT member.id , member.name , event.name , event_rsvp.date FROM member LEFT JOIN event_rsvp ON event_rsvp.member_id = member.id LEFT JOIN event ON event.id = event_rsvp.event_id AND event.id = $event_id WHERE event_rsvp.member_id IS NULL;
something like that anyway.
-
The three core tables:
member.id
member.name
event.id
event.name
group.id
group.name
A table to track events rsvp:
event_rsvp.id
event_rsvp.event_id
event_rsvp.date
A table to track members in groups:
member_in_group.member_id
member_in_group.group_id
To get a list of all members and their associated groups:
SELECT member.id , member.name , group_concat(group.name SEPARATOR '\n') AS groups FROM member LEFT JOIN member_in_group ON member.id = member_in_group.member_id LEFT JOIN group ON member_in_group.group_id = group.id GROUP BY member.id ORDER BY member.nameTo get a list of all members that have rsvp'd an event:
SELECT member.id , member.name , event.name , event_rsvp.date FROM event INNER JOIN event_rsvp ON event.id = event_rsvp.event_id INNER JOIN member ON event_rsvp.member_id = member.id WHERE event.id = $event_id; -
Only one php function would be required.. with using a newline separator in the group_concat...
$caption = explode("\n", $record['caption']);
-
Columns can not be created dynamically. A group_concat can group all the captions into one column and add a seperator as well... have a look at the group_concat information here: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html
-
I get conflicting results.
The following table definitions and record inserts:
DROP TABLE IF EXISTS property; CREATE TABLE property ( idINT NOT NULL AUTO_INCREMENT primary key, address CHAR(50) ); INSERT INTO property (address) VALUES ('123 Fake Street, Faketown'); DROP TABLE IF EXISTS deals; CREATE TABLE deals ( property_id INT, deal_type_id INT, sales_pitch TEXT, price INT, INDEX (sales_pitch(10), price), PRIMARY KEY (property_id, deal_type_id) ); INSERT INTO deals (property_id, deal_type_id, sales_pitch, price) VALUES (1, 1, 'buy buy buy', 100000), (1, 2, 'hurry hurry hurry', 100000), (1, 3, 'make an offer', 100000), (1, 4, 'hurry last days', 100000), (1, 5, 'once in a lifetime', 100000), (1, 6, 'golf course nearby', 100000), (1, 7, 'beach side property', 100000);The following explain:
EXPLAIN SELECT deals.property_id , deals.price # , deals.sales_pitch # , property.address FROM deals LEFT JOIN property ON deals.property_id = property.id
results:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE deals ALL NULL NULL NULL NULL 7 1 SIMPLE property eq_ref PRIMARY PRIMARY 4 test.deals.property_id 1 Using index
With only the index property_id in the SELECT values resulted in the type `index`:
EXPLAIN SELECT deals.property_id # , deals.price # , deals.sales_pitch # , property.address FROM deals LEFT JOIN property ON deals.property_id = property.id;
resulted in:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE deals index NULL PRIMARY 8 NULL 7 Using index 1 SIMPLE property eq_ref PRIMARY PRIMARY 4 test.deals.property_id 1 Using index
-
First, a question: Do you understand the following sentence?
I cant figure out how to go about this as I don't be able to put in ore than one WHERE statement?
And now the answer:
SELECT user.id, user.bandname, user.bio, users.history, users.discography, images.path FROM users, images WHERE users.id = images.id AND users.bandid = $bandid or SELECT user.id, user.bandname, user.bio, users.history, users.discography, images.path FROM users INNER JOIN images ON users.id = images.id WHERE users.bandid = $bandid
-
Aliases will be required to distinguish identical column names, something like this:
SELECT person.pid , person.name_first , baclabor.encounter_nr AS baclabor_encounter_nr , patho.encounter_nr AS patho_encounter_nr , chemlab.encounter_nr AS chemlab_encounter_nr , .... etc FROM care_encounter AS encounter...And then in PHP:
<?php echo $rows['chemlab_encounter_nr']; ?>
Consider trimming the selected values to only those that are required.
-
Having a read of the following might be helpful in regards to old_password.
-
The following query:
SELECT person.pid , person.name_first , baclabor.* , patho.* , chemlab.* FROM care_encounter AS encounter INNER JOIN care_person AS person ON encounter.pid = person.pid LEFT JOIN care_test_findings_baclabor AS baclabor ON encounter.encounter_nr = baclabor.encounter_nr LEFT JOIN care_test_findings_patho AS patho ON encounter.encounter_nr = patho.encounter_nr LEFT JOIN care_test_findings_chemlab AS chemlab ON encounter.encounter_nr = chemlab.encounter_nr WHERE baclabor.encounter_nr IS NOT NULL OR patho.encounter_nr IS NOT NULL OR chemlab.encounter_nr IS NOT NULL
Produces the following result:
pid name_first batch_nr encounter_n room_nr dept_nr notes findings_in findings_cu findings_fi type entry_nr rec_date doctor_id findings_da findings_ti status modify_id modify_time create_id create_time batch_nr encounter_n room_nr dept_nr type doctor_id findings_da findings_ti status modify_id modify_time create_id create_time batch_nr encounter_n job_id test_date test_time group_id type validator validate_dt status modify_id modify_time create_id create_time 10000000 pig 1 1 10000001 2007-03-13 10:26:50 priority Chemical Test 0000-00-00 00:00:00 admin 2007-05-05 10:44:52 admin 2007-03-13 10:26:50 10000000 pig 3 9 10000002 2007-04-04 09:57:49 priority Chemical Test 0000-00-00 00:00:00 hidden 2007-05-05 11:02:22 admin 2007-04-04 09:57:49 10000000 pig 5 9 10000002 2007-04-04 14:38:21 priority Chemical Test 0000-00-00 00:00:00 admin 2007-04-13 14:38:24 admin 2007-04-13 14:38:21 10000000 pig 6 12 10000001 2007-05-06 00:00:00 priority Chemical Test 0000-00-00 00:00:00 2007-05-03 14:35:59 0000-00-00 00:00:00 10000005 kib 4 13 0 0 0 0 Bacteriological Test 0000-00-00 0000-00-00 00:00:00 2007-05-05 11:00:01 0000-00-00 00:00:00 10000006 test 4 14 10000003 2007-04-11 16:59:25 priority Chemical Test 0000-00-00 00:00:00 2007-05-05 12:07:20 admin 2007-04-11 16:59:25 10000005 kib 1 15 0 Pathological Test 2007-05-18 00:00:00 2007-05-07 16:10:01 0000-00-00 00:00:00 10000007 benson 5 16 25 dftf 0 0 0 Bacteriological Test 30000001 2007-04-16 2007-04-16 09:57:00 done admin 2007-05-11 08:54:12 admin 2007-04-16 09:57:04
If the above results are not what is required then what is required???????
-
maliary... I'd suggest that Barand would appreciate more than one tables worth of information.. more like all the tables that are described in the query and some test data for each table.
-
This is pretty much the same as your original query Azu:
SELECT count(*) FROM Z LEFT JOIN a ON Z.1 = a.1 LEFT JOIN b ON Z.1 = b.1 LEFT JOIN c ON Z.1 = c.1 WHERE a.1 IS NULL AND b.1 IS NULL AND c.1 IS NULL
-
Use the JOIN syntax
-
Possibly?:
SELECT count(*) FROM Z WHERE X NOT IN ( SELECT 1 FROM a UNION SELECT 2 FROM b UNION SELECT 3 FROM c )
EDIT: the above query is not faster.
-
I do not understand your question, galone.
-
There is probably an issue with the parenthesis used around the select columns.
-
Use a direct link (foreign key):
player.id
player.name
player.team_id :: identifies which team the player record is associated to
player.foot
team.id
team.name
team.coach
A player is linked to a team via player.team_id = team.id
-
By adding an ORDER BY clause...
-
looks ok'ish
-
Make the following code changes and post the results here:
$query = "INSERT INTO mailinglist (email) VALUES ('$email')"; mysql_query($query) or die('Error, insert query failed: '.$query.' error: '.mysql_error());
Get certain rank out of table.
in MySQL Help
Posted
Yeah, I never tested the SELECT @id := in php - does not appear to work.
Use SQL_CALC_FOUND_ROWS in the LIMITed query then SELECT found_rows() and some math to calculate the rank in php.