[RESOLVED] Nested loops and sql queries - wtf?

[adamata]

Looking for help as to why these nested loops with queries aren't working

So, I have a nested loop that gets user data

$user="johndoe";

   $search1 = "SELECT search, filename, searchloc, searchvar from user_searches where user = '$user'";
   $users = mysql_query($search1) or die("User Query failed");

   while ($srow = mysql_fetch_row($users)) {

   $search = $srow[0];
   $filename = $srow[1];
   $searchloc = $srow[2]; //(IE city)
   $searchvar = $srow[3]; //(IE boston)


   include 'search_code.php';

}

Now, inside search_code.php there is a new query that takes that values of searchloc and searchvar and gets a new set of data.

$area_search = "SELECT area, region, state FROM locations where $searchloc = '$searchvar' order by state,area";
$area_results = mysql_query($area_search) or die("Area Query failed");


while ($areas = mysql_fetch_row($area_results)) {
$var1 = $areas[0];

//do some stuff here.  Works just fine when not run in the nested loop

}

So, what happens is that the first pass through for johndoe (IE boston) gets the correct behavior, but the second pass through (IE tampa) fails the inner loop "area_search" query. This would be great if I only have 1 user with one search area. 🙂

What do I need to do to get that inner query to fire correctly each time just like the first?

Any assistance is always appreciate.

[leatherback]

Hi

I see no clear reason why it would not work. #

I assume that the second bit of code is used throughout your project, and that that is why you include it. I would personally place the second bit in a function, and include the function once at the top of the page, and just call the function in the loop. This would on more complex projects facilitate re-use, and create a function that is independent of the variable scope & namings in the surrounding code. But as said: I see no reason why it would not work. Does it work when you just paste the code in the loop?

Also, echo out each SQL statement before using it, so you know what is passed to the database. This will most likely help you solve your problem

[adamata]

So, I think I figured it out but its still acting weird.

Depending on my data set, it passes or fails.

IE

I have a user (found from the first query) that will need 4 searches performed.
the second search works on locations. a region (USA, CANADA, etc) and then a list of cities in each region. The second query takes information from the first and generates a list of locations (by city) to run a search.

IE
-Name- -region- -search criteria-
john smith CANADA blah blah blah
john smith CANADA yadda yadda yadda
john smith USA blah blah blah
john smith CANADA yap yap yap

So, it takes the search criteria and applies to to the 15 city locations in CANADA from another select statement. then writes out the data from the search and goes to the next record. Repeats the process and goes on. I did it this way so I could enter all my search information in one table and just run the search by user.

If I run the code using CANADA as the region (about 15 cities) the script loops through everything just fine and creates the output exactly as needed.
If I then run it for USA (about 130 cities) if will get through the first then fail when trying to do the next inner loop (select statement to grab USA cities)

So, in the case using the example data above, the first three will run fine and then when the query goes back to grab the locations for the fourth CANADA search, it will fail (meaning that the USA worked, but then failed trying to grab the final CANADA location). If I take out the USA search, it all works.

So, I guess my question is why would a large data set cause the following query to fail? Is it timing out? Running out of memory?

I added a "mysql_free_result" in the loop when I was done with the location array (before it got filled again) thinking that it was a memory issue.

I am completely stumped.

[adamata]

I ended up breaking the Region search down by states and it worked great. I guess the larger search was causing some issues.

This is resolved