Nested while loop skips first result

Question

I need to query an MySQL table to get a list of names then, based off that list of names, query reports tied to it. Here is my code:

//query the peoples

$query_people = mysql_query("SELECT * FROM people ORDER BY people_name ASC")
while($fetch_people = mysql_fetch_array($query_people)){
    $people_id = $fetch_people[people_id];
$people_name = $fetch_people[people_name];
$query_report = mysql_query("SELECT * FROM report WHERE report_entity = '$people_name'");

    // output each person's name
    echo($people_id.$people_name);

    //get their reports
    while($fetch_report = mysql_fetch_array($query_report)){
    $report_id = $fetch_report[report_id];
    $report_type = $fetch_report[report_type];
    $report_narr = $fetch_report[report_narr]; 
    echo($report_narr);
   }
} 
?>

When it outputs, I get this:

1Bill

2Bob "Bill's narrative"

3Tom "Bob's narrative"

4 "Tom's narrative"

Any thoughts on why it is skipping Bill's query on the nested loop?

i don't think so - I have another page that is pulling the data out in a different way, but with the same queries which is working fine. — thebarless
– thebarless, Commented Mar 11, 2011 at 4:54
That's odd then, the code looks sound. But maybe I'm missing something too. — GWW
– GWW, Commented Mar 11, 2011 at 4:56

Kevin S · Accepted Answer · 2011-03-11 05:13:25Z

For some reason I can't comment on your question, so I will post an answer instead. It looks like you have some sort of off-by-one index problem.

As you see "Bill's narrative" is being printed with Bob and "Bob's narrative" is being printed with Tom. Also you have no narrative with Bill and you only have a narrative with index 4. However, since you don't really use indexes to do any of your queries, it may be one of two things:

1) Data in the table isn't stored correctly.

2) The queries you are doing aren't returning what you expect.

For both, try running the exact queries you posted in your code in mysql and see if the data is what you expect. Also, try adding more echo statements like what are $people_id and $people_name. And echo the second sql statement that creates $query_report, make sure that is what you expect it to be.

Finally, make sure that you are entering the nested while loop by printing something that will always show up or adding quotes to your existing echo statement:

echo "'" . $report_narr . "'";

This way you know you at least entered the loop and the query returned results. Otherwise, your database may not be arranged the way you think it is.

Chris · Accepted Answer · 2011-03-11 06:34:12Z

1

From what I understand you can't guarantee the order mysql statements execute outside of transactions. I would use a join instead, it will be quicker than nested loops with mysql queries inside of them.

$query = "
    SELECT * FROM `people`
    JOIN `report`
    ON `report`.`report_entity` = `people`.`people_name`
    ORDER BY `people`.`people_name` ASC
";

$result = mysql_query($query);

while($row = mysql_fetch_array($result)) {
    echo $row['report_narr'];
}

answered Mar 11, 2011 at 6:34

Chris

58.9k34 gold badges158 silver badges198 bronze badges

2 Comments

thebarless Over a year ago

Thanks all for the responses. I can't do a join (unless I have something new to learn about joins) because I have hundreds of reports per person; my thought is the easiest way is to fetch the people from the people table and while going through the first while loop, fetch the reports for that person. Is there a better way to do this?

Chris Over a year ago

If you have thousands of reports it's even more important that you don't do this with loops. If you have a properly structured table with indexed columns, etc, joins are fined, and more efficient than php loops tied to queries.

thebarless · Accepted Answer · 2011-03-22 00:34:08Z

0

Well, I have no explanation for this - I started from scratch and it worked. Thanks all for your help!

answered Mar 22, 2011 at 0:34

thebarless

4721 gold badge12 silver badges35 bronze badges

Collectives™ on Stack Overflow

Nested while loop skips first result

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related