CODEWITHSUNDEEP
phppdowhile-loop
FabriceDouceur
FabriceDouceur

Reputation: 91

While in a while display only one row?

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!

Upvotes: 3

Views: 70

Answers (4)

Puneet Singh
Puneet Singh

Reputation: 3543

The answer by akasummer will work fine if the sequence of train_id is same in both the tables. If the sequence is not same some rows may be missed.

and if there is no difference in the sequence of train_id in both the tables there is no need of if conditioned in akasummer's answer. 

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

The easy way to do it my inner join in mysql fetching data from both table on the basis of train_id and there entry dates. As shown below(There can be some silly mistake in syntax, but logic is understandable)

SELECT 
   W.*, 
   CL.entry_date AS cl_entry_date, 
   CL.left_date AS cl_left_date, 
   CL.train_type AS cl_train_type, 
   CL.entry_number AS cl_entry_number, 
   CL.left_number AS cl_left_number  
FROM 
   worksheets AS W, 
   criter_live AS CL 
WHERE 
   W.train_id = CL.train_id 
AND 
  W.entry_date > $currentdate_today_midnight 
AND 
  CL.entry_date > $currentdate_today_midnight

In one result you will get columns for both the table and then you can check it with a simple plain while loop.

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33813

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
    )

Upvotes: 3

akasummer
akasummer

Reputation: 362

EDIT: I am assuming you have the same data in both tables.

Remove second while loop:

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

    $check_live = $req_getLive->fetch();

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

        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. "#";

    }
}

Basically, in the first iteration of your outer loop you've been fetching 1 row from $req_getCriter and comparing it to all other rows from $req_getLive. Second iteration wouldn't work, since all rows from $req_getLive were fetched.

Upvotes: 1

jeroen
jeroen

Reputation: 91734

After the first iteration of your outer loop, you will have fetched all items in the inner loop from the database result set so it will never run again.

You could of course fetch all items from both queries in arrays and use foreach loops or reset the database cursor for your inner loop, but you could probably do this in one database query as well.

Upvotes: 0

Related Questions