SitePoint Sponsor

User Tag List

Page 4 of 4 FirstFirst 1234
Results 76 to 80 of 80
  1. #76
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  2. #77
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  3. #78
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #79
    SitePoint Enthusiast
    Join Date
    Oct 2008
    Location
    Pleasanton, CA
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Curtis
    Was it summer when the river ran dry, or was it just another dam?

  5. #80
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cstallins View Post
    I'm gonna' miss you, r.....
    i ain't going anywhere, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •