The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

you now either have uncovered a real mysql bug in that it doesn't like the name `city` (the chance of which is vanishingly slim, seeing as how there are millions of mysql installations around da world), or else you are doing something else wrong

And here is your .sql file.
I shortened tables considerably - there are now 20 members, and the (I hope) appropriate number of phone records, email records, etc. I checked, and all relationships between the seven tables appear to be sound. Please let me know if I need to do something additional or differently to make your helping me any easier.

Driving soon; back online in a couple hours...

CurtisWas it summer when the river ran dry, or was it just another dam?

you will see that you don't get the same error message. what you should get though is an error similar to:

Code:

right syntax to use near 'city = 'Livermore' AND gender = 'M'' at line 2

I hard coded values for all your variables. Do you see the single quote before city? That means that somewhere else further up in your code you have a single quote somewhere it doesn't belong. When city is then appended to the query your query is actually being terminated by the single quote and thus throwing the error.

r937 and guelphdad...Let's just agree right now that you are two tenacious dudes.

i still don't understand why you had to use microsoft word ~ptui~
1. I used Word because it was the first option I thought of. Notepad is far better, however...good call there.

your script failed in several places, because you apparently lost the semicolon at the end of a few statements
2. I didn't lose the semicolons. I manually removed the semicolon from the last statement in several of the groupings because I confused the SQL statements syntax with some programming languages syntax where you're supposed to do that. In some of my (I think) JavaScript arrays I believe that I have to omit the closing semicolon from the last member.

the email and phones tables did not load because you had UNIQUE keys on the email address and phone numbers, but they were all bullpuk@spammer.org and xxx-xxx-xxxx
3. I have UNIQUE keys on some of the fields in my db tables because the system will then throw an error if I accidentally try to INSERT a record with a duplicate phone number, email address, etc. I use the UNIQUE key in this way as a security device of sorts. Before I attached the file and posted it I removed for privacy's sake my club members' email addresses and phone numbers and replaced them with gibberish...then I forgot that the UNIQUE keys were there and might cause problems for you.

So, aside from using the UNIQUE key as a security device of sorts (preventing duplicate data entry), what else is it good for? I mean, why does the key exist?

Alright, I'm going to create a duplicate db, install the tables and data, and see what shakes out when I run your new and improved SELECT query.

15 minutes later ***

Well, I'm glad it's not just me.

You went and lower-cased the IDemail, IDphone and IDcity table names to idemail, idphone and idcity, then failed to make those updates in your SELECT statement. :-) Good to know that some super-human people are only human...

I recreated the seven tables using your new and improved script, then updated and ran your SELECT statement sans WHERE clause and....it works great. All the data is returned perfectly well.

Now, let me uncomment that WHERE clause.......and........

I get message saying MySQL returned an empty result set (i.e. zero rows).

Wha??

OK...the tone I sense in the last line of your post is rather mirthful...nearly cynical.

Something approaching merriment.

Well, the city of Richmond has city_id of 33, and none of our 20 members in the idcity table are assigned city_id 33. But, if I go into the idcity table and update one of the members to city_id of 33, then your query returns that member. Or, if I adjust the WHERE clause to reflect 'Livermore', or any city inhabited by any of our twenty members, then we're golden. OK, that's easy enough.

So, I'm guessing that you are thinking that my tables need to be wiped and recreated, is that correct? And, that I have to then reINSERT a-l-l t-h-o-s-e r-e-c-o-r-d-s...... mmmmm....that sounds like fun!

guelphdad, thank you again VERY much for hunting this thing down. I will check out your suggested path on Saturday as well by playing with that $city whereClause as you suggest. It sounds like you are on to something...

CurtisWas it summer when the river ran dry, or was it just another dam?

anyhow, i think we have demonstrated, finally, that the issue is not with the SQL

so, no, you don't have to recreate and reload -- but now that you have produced an export file (called a dump or backup file, this is exactly what the mysqldump utility produces), you now know how to back up your data so that you could, if you needed to, recreate your data easily

did you think any further about the WHERE 1=1 trick i mentioned? this should make your php processes which assemble the query simpler...

r937, I'll use all lowercase table names from now on. Good tip.
When you suggest that "the issue is not with the SQL", does that mean that the issue is not with my tables, columns, query, etc? Does that mean that the dump I attached is not corrupted?

If that's the case, then the problem lies in the PHP. I'll work through guelphdad's path and see what shakes out later this afternoon.

Thank you again for your continued guidance and patience. If Sunday comes and goes and I'm still down this rat hole, then I'm going to consider rebuilding the tables, re-inserting the data, etc, and along the way employing guelphdad's new and improved "Big Boy" set-up. I'm already going to add your "1=1" to the end of my default WHERE clause in my PHP script.

Please do let me know if any other possible solutions cross your mind.

Oh - and my little 20-member practice db I created last night for this little project is the perfect place to mess with multiple email addresses, phones and cities for some poor member to experiment with your "24-rows" thing we discussed earlier. I'm interested in your SQL trick to alleviate that problem...

CurtisWas it summer when the river ran dry, or was it just another dam?

guelphdad, thank you very much for offering some good input.
I examined your idea at the bottom of post #45. You display this code:
'city = 'Livermore' AND gender = 'M''
and suggest that the single quote before the word "city" is erroneous. Actually it's always there in error messages of this type, because the system output is wrapping single quotes around the piece of the query with which it has a problem. If you look closely at the end of the query text, you'll see two single quotes after the capital "M". The first of those closes the single quote that precedes the capital M, and the second closes the single quote that precedes the word "city". If the system wrapped asterisks around the query string with which it has a problem, it might instead look like this:
*city = 'Livermore' AND gender = 'M'*

So, the issue is not with quotation marks -- at least not here.

CurtisWas it summer when the river ran dry, or was it just another dam?