Nested while loop skips first result

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?

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.

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'];
}

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