Unknown column 'city' in 'where clause'

[r937]

dr john, don't confuse the guy by optimizing everything right away plz

he's trying to learn many-to-many relationships

curtis, the query in post #22 is fine

i'm willing to bet the cities table isn't actually what you've shown us

please run this query and show what it produces:

SHOW CREATE TABLE cities

[cstallins]

here ya go:
SQL query: SHOW CREATE TABLE cities;
Rows: 1
Table Create Table
cities CREATE TABLE `cities` (
`city_id` smallint(4) NOT NULL auto_increment,
`city` varchar(20) NOT NULL,
PRIMARY KEY (`city_id`),
UNIQUE KEY `city` (`city`)
) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=utf8

[cstallins]

and here's the one for its sister table, IDcity:
SQL query: SHOW CREATE TABLE IDcity;
Rows: 1
Table Create Table
IDcity CREATE TABLE `IDcity` (
`mem_id` smallint(4) NOT NULL,
`city_id` smallint(3) NOT NULL,
PRIMARY KEY (`mem_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

[r937]

weird

you actually do have a column called city

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

do you know how to dump all your tables?

[cstallins]

enlighten me yet again...

[r937]

what are you using, phpmyadmin? heidisql? mysql query browser?

[cstallins]

I use phpmyadmin

[r937]

okay, use the Export tab, check the check box to generate both structure and data, select all 7 tables, save the export to a text file, and attach the text file to a post here

make sure there is no sensitive data in your tables

[cstallins]

Here's the attached .doc file.

[r937]

that's pretty, but it's not helpful

i wanted you to generate the CREATE TABLE statements (structure) as well as the INSERT statements (data) so that i could set up exactly the same tables over here on my testing system

i'm not gonna type them in off a word doc, sorry

[guelphdad]

the issue is not going to be your sql if you can run the query directly in the sql tab in phpmyadmin and get results.

Strip all references to city out of your script. Run the script with a value for city hard coded in place.

Do you get an error then?

How about if you set a variable right at the beginning of the script for $city and then put that in your query instead.

Do you get an error?

[cstallins]

Well, I did as you asked: phpMyAdmin; Export tab; check the box for a text doc;
choices are
CSV
CSV for MS Excel
Microsoft Excel 2000
Microsoft Word 2000
LaTeX
Open Document Spreadsheet
Open Document Text
PDF
SQL
XML
YAML

But you knew that.

Selecting Word 2000 let me select both Structure and Data check boxes, and the results I attached to the post. How do I specify to include the CREATE TABLE statement results?

[cstallins]

I'll try your advice from post #36 this evening and post here again when I have some results to share.

Thank you so much for your attention. I really do appreciate the ongoing support.

[r937]

choose the SQL option, i think (i;m doing this from memory, because i haven't used phpmyadmin in over 4 years)

keep trying until the output contains CREATE TABLE and INSERT statements

[cstallins]

LOL - I just found the SQL radio button, and it dumps LOTS of stuff :-) Let me remove phone/email data and I'll attach it in a bit...

[cstallins]

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...

[cstallins]

Had to change it to a .doc. I hope the formatting is OK ...

[Dr John]

I might be wrong on this one, but

CREATE TABLE IF NOT EXISTS `IDcity` (
  `mem_id` smallint(4) NOT NULL,
  `city_id` smallint(3) NOT NULL,
  PRIMARY KEY  (`mem_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS `cities` (
  `city_id` smallint(4) NOT NULL auto_increment,
  `city` varchar(20) NOT NULL,
  PRIMARY KEY  (`city_id`),
  UNIQUE KEY `city` (`city`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=48 ;

It's late here and I'm tired, so not sure if the different definitions for city_id can affect the join - smallint(3) vs smallint(4)

[r937]

i still don't understand why you had to use microsoft word ~ptui~

your script failed in several places, because you apparently lost the semicolon at the end of a few statements

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

attached is a "corrected" script that actually runs (you might want to create a separate testing database to try it, so as not to overwrite your actual data)

then run this query:

SELECT l_name, f_name, city, num, address
FROM names
LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id
LEFT JOIN cities ON IDcity.city_id = cities.city_id
LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id
LEFT JOIN phone ON IDphone.phone_id = phone.phone_id
LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id
LEFT JOIN email ON IDemail.email_id = email.email_id
--WHERE city = 'Richmond'
ORDER BY l_name, f_name

notice that the WHERE clause is commented out

these are the results:

l_name	f_name	city	num	address
Abbot	Chris	Concord	xxx-xxx-xxx2	bullpuk8@spammer.org
Adams	J. Richard	Livermore	xxx-xxx-xxx3	bullpuk1@spammer.org
Albertson	Suzanne	San Ramon	xxx-xxx-xxx4	bullpuk2@spammer.org
Allen	Linda	Livermore	xxx-xxx-xxx5	bullpuk3@spammer.org
Anderson	Bob	Livermore	xxx-xxx-xxx6	bullpuk4@spammer.org
Appleby	Wendy	Roseville	xxx-xxx-xxx7	bullpuk5@spammer.org
Arrambide	Juan	Hayward	xxx-xxx-xxx8	
Aufdenkamp	Jan	Pleasanton	xxx-xxx-xxx9	bullpuk6@spammer.org
Bacskai	Judith	Kensington	xxx-xxx-xx10	bullpuk7@spammer.org
Bacskai	Robert	Kensington	xxx-xxx-xx10	bullpuk7@spammer.org
Ball	Karin	San Ramon	xxx-xxx-xx12	bullpuk9@spammer.org
Bastani	Ali	Concord	xxx-xxx-xx13	bullpuk10@spammer.org
Benjamin	Daniel	Livermore	xxx-xxx-xx14	
Berckmoes	Russ	Pleasanton	xxx-xxx-xx15	bullpuk11@spammer.org
Blanco	Gail	Pleasanton	xxx-xxx-xx16	bullpuk12@spammer.org
Boerman	Brian	Folsom	xxx-xxx-xx17	
Boerman	Carla	Folsom	xxx-xxx-xx17	
Bond	Richard	Dublin	xxx-xxx-xx18	bullpuk13@spammer.org
Bousfield	Sarah	Reno	xxx-xxx-xx19	bullpuk14@spammer.org
McKay	Deb	San Ramon	xxx-xxx-xxx4	bullpuk2@spammer.org

notice anything? it's working!!! it does not give the "unknown column 'city'" error message



now uncomment the WHERE clause and see if you can explain what happens

[guelphdad]

I have narrowed it down but not quite got it. Check this code:

 if($_POST['l_name'] || $_POST['f_name']) {
      $whereClause .= " AND city = '$city'";
    } 
    else { 
      $whereClause = "city = '$city'"; 
    }

take the AND out and echo out your sql query at this point:

 if($_POST['l_name'] || $_POST['f_name']) {
      $whereClause .= " city = '$city'";
      echo $sql_result;
    } 
    else { 
      $whereClause = "city = '$city'"; 
    }

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

 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.

I'll still look but someone else may find it first.

[cstallins]

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...

[r937]

your understanding of UNIQUE keys is correct, the purpose is to prevent duplicates

and you also correctly realized why no results came back for Richmond

as for the upper/lower case on the table names, i'm not quite sure how that happened, except to say that it shouldn't matter:

... database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.
-- http://dev.mysql.com/doc/refman/5.0/...nsitivity.html

most mysql developers therefore use lower case names exclusively

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...

[cstallins]

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...

[r937]

well, yes... another solution is not to use a many-to-many relationship structure if you don't actually use it

for instance, do you really plan to relate a single member to multiple cities? multiple emails? multiple phones?

if you do, then this is the appropriate time to ensure that your test data actually has instances of these relationships

so that i can finally get around to showing you the awful thing called cross join effects if you try to retrieve them all in the query as it is now written

[cstallins]

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.