Unknown column 'city' in 'where clause'

[cstallins]

Found it.

Fixed it.

I am drained and elated, and will post on Monday what I found to be the problem.
Commenting out - then slowly uncommenting - small chunks of code, brought my eyes to the source of the issue. That always works for me.

r937, on post #16: Man, you were sooooo close...
and SoulScratch on #65.....could have reached out and touched it...

[cstallins]

I was so focused on finding fault with the main query, the one that handles the form input and brings back the member records for display on the results page, that I gave little thought to the *other* query on the page, the one near line 135 that is responsible for counting the number of rows defined by the main query's WHERE clause. That *other* query counts the result set rows so that my display table can both tell the visitor how many records satisfy their query, and effectively paginate through result sets that number greater than (in my case) 20 rows. I understand that I shouldn't have to query specifically to count the number of returned rows, but my current script design necessitates that. It's on my list of things to make better.

That other and lesser query looks like this:
$query = "SELECT count(*) FROM names WHERE ".$_SESSION['whereClause'];
Several of you who stuck with me to trouble shoot my problem commented briefly on that query, but I didn't recognize that it is querying only my names table, in which l_name, f_name and gender live. What we (or I, since I don't expect any of you to remember what my $whereClause does) failed to recognize is that $whereClause represents the info that the visitor input through the form, which includes any combination of l_name, f_name, gender............and city! So, of course that query generated no error when the visitor searched through the form with any combination of l_name, f_name or gender -- because then my $whereClause doesn't reference the city column. BUT, if ever we entered a city name in the form, either alone or in combination with l_name, f_name or gender, then $whereClause references the city column that....now lives in another table! Doh!!

The fix, of course, is to make sure that this $query statement includes a path to the table where the city column lives, so I amended that statement to this:
$query = "SELECT count(*) FROM names LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id LEFT JOIN cities ON IDcity.city_id = cities.city_id WHERE ".$_SESSION['whereClause'];

And we're golden!

In closing, I made notes of several of your suggestion on making my db design or code stronger/better. Now that we've solved the problem, I intend to implement those. If you'd care to chime in with some suggestions on how a beginning/intermediate developer can improve this db design or PHP script, I'm all ears.

Your time, expertise and willingness to help amaze me, and I've learned a whole lot about db design, SQL, and PHP best practices. Thank you all very much for being such stalwarts.

[r937]

aha!! i knew you were looking at the wrong query

by the way, i think you missed the post where i said you do not need a separate count query

look up the FOUND_ROWS function, please

[cstallins]

I miss NOTHING!
I made a // note in my script that reminds me to substitute your FOUND_ROWS for my count() b.s.

I can't even beGIN to describe how good it feels to beat this thing! You must have felt that a time or three during your long and storied career, yes?

Man.....I'm gonna' miss you, r......;-)
Thank you for being such a huge and friendly help.

[r937]

I'm gonna' miss you, r.....

i ain't going anywhere, eh