While in a while display only one row?

Question

I have two SQL table, one contains "manual inserted datas" and the other one "automatic inserted data" by script.

In order to test if the script works well, the manual table and the auto table are the same.

So, I would like to "compare" both database, and then in another script, highlight the difference.

// $currentdate_today_midnight is a timestamp

$sql_getLive = "SELECT * FROM worksheets WHERE entry_date > $currentdate_today_midnight";
$req_getLive = $cnx->query($sql_getLive);
$req_getLive->setFetchMode(PDO::FETCH_OBJ);

// countAll counts all rows for a table and a condition 
$countLive = countAll("worksheets", "entry_date > $currentdate_today_midnight");

$sql_getCriter = "SELECT * FROM criter_live WHERE entry_date > $currentdate_today_midnight";
$req_getCriter = $cnx->query($sql_getCriter);
$req_getCriter->setFetchMode(PDO::FETCH_OBJ);

$countCriter = countAll("criter_live", "entry_date > $currentdate_today_midnight");

if($countLive == 0){
    /* If there is no live (manual datas) inserted */
    echo "EMPTY";
    die();
}

while ($check_criter = $req_getCriter->fetch()) {

    while ($check_live = $req_getLive->fetch()) {

        if ($check_live->train_id == $check_criter->train_id) {

            /* check_live = worksheets */
            /* check_criter = criter_live */

            echo $check_live->train_id . "|" . $check_criter->entry_date . "|" . $check_live->entry_date . "|". $check_criter->left_date . "|". $check_live->entry_date . "|". $check_criter->train_type . "|". $check_live->train_type . "|". $check_criter->entry_number . "|". $check_live->entry_number . "|". $check_criter->left_number . "|". $check_live->left_number. "#";

        }
    }
}

So, I've tried to make "a while in a while" but it doesn't work, I get only one "echo"... instead of 17 (returned thanks to the countAll function).

Did I made a mistake? Or is there any other solution?

Thank you!

Answer 1

perhaps you could try to find the differences directly in the sql like this:

select * from `worksheets` where `entry_date` > $currentdate_today_midnight
    and `train_id` not in (
        select `train_id` from `criter_live` where `entry_date` > $currentdate_today_midnight
    )

A slightly modified version, testing entry_date

select * from `worksheets` where `entry_date` > $currentdate_today_midnight
    and `entry_date` not in (
      select `entry_date` from `criter_live` where `entry_date` > $currentdate_today_midnight
    )