Insert fails while loop in pdo (pdo/php 5.6)

Question

I've wrote a script in PHP for an university big data project.

The following code should support following function:

1. Read amount of data from a table
2. read a subset of the same table und extend it with 2 columns which calculate 2 values.
3. generate f.e. 1000 inserts and commit them to an other table
4. do this for the whole dataset of point 1

My problem:

0-1000: my solution inserts in the first try 1000 datalines 1000-2000: in this step it inserts 2000 datalines, so after the 2nd step it seems to insert 0-2000 datalines of the origin dataset. In sum 3000 datalines.

I'm a little bit out of ideas... I don't see my failure.

<?php
$user = 'root';
$pass = '';
$kill = 0;
echo 'Connecting to DB:';
try {
    $db1 = new PDO('mysql:host=localhost;dbname=itt2_joined;charset=utf8',$user,$pass);
    $db1->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    echo "OK \nGOING2COPY!\n";
}
catch(PDOException $ex) {
    echo "  Failed - Process stopped!\n";
    $kill = 1;
}
if(!$kill) {
    $stmt = 'SELECT
                `id_transaction`
              FROM
                `database_joined_full`
              WHERE
                `relative_delivery_date` > 0
              AND
                `relative_received_date` > 0
              AND
                `relative_issued_date` > 0';
    $setSize = $db1->query($stmt)->rowCount();
    echo "No. Of Datasets: " . $setSize . "\n";
    $stepSize = 1000;
    echo "Start Copy $stepSize Datasets per Query! \n";
    $copy = 1;
    $lower = 0;
    $upper = 0;
    echo "[";
    while($copy) {

        $lower = $upper;
        $upper += $stepSize;
        $upper = ($upper > $setSize ? $setSize : $upper);

        $data = $db1->query("SELECT
                `id_transaction`,
                `id_order_line`,
                `relative_transaction_date`,
                `relative_requested_date`,
                `new_requested_qty`, `new_requested_price`,
                `relative_suggested_date`,
                `new_suggested_qty`,
                `delivery_qty`,
                `relative_delivery_date`,
                `received_qty`,
                `relative_received_date`,
                `id_order_header`,
                `delivery_counter`,
                `relative_issued_date`,
                `relative_xcurrent_date`,
                `issued_quantity`,
                `current_quantity`,
                `product_code`,
                `destination`,
                `relative_issuing_date`,
                `delivery_location`,
                `issuer`,
                `id_supplier`,
                `currency`,
                `shipment`,
                `issued_price4unit`,
                `current_price4unit`,
                `total_delivered`
              FROM
                `database_joined_full`
              WHERE
                `relative_delivery_date` > 0
              AND
                `relative_received_date` > 0
              AND
                `relative_issued_date` > 0
              LIMIT  $lower OFFSET $stepSize")->fetchAll();
        $db1->beginTransaction();
        foreach($data as $row) {
           $sql = "";
            $sql = "INSERT INTO `refactored_data` (`id_transaction`, `id_order_line`, `relative_transaction_date`,
                                `relative_requested_date`, `new_requested_qty`, `new_requested_price`, `relative_suggested_date`,
                                `new_suggested_qty`, `delivery_qty`, `relative_delivery_date`, `received_qty`,
                                `relative_received_date`, `id_order_header`, `delivery_counter`, `relative_issued_date`,
                                `relative_xcurrent_date`, `issued_quantity`, `current_quantity`, `product_code`, `destination`,
                                `relative_issuing_date`, `delivery_location`, `issuer`, `id_supplier`, `currency`, `shipment`,
                                `issued_price4unit`, `current_price4unit`, `total_delivered`, `shipment_delay`, `sending_delayed`) VALUES ";
            $sql .= "(";
            for($i = 0; $i<sizeof($row)/2; $i++) {
                $sql .= "'".$row[$i] . "', ";
            }

            $sql .= $row['relative_received_date'] - $row['relative_delivery_date'] . ',';
            $sql .= ($row['relative_received_date'] <= $row['relative_issued_date'] ? 1 : 0) .')';
            $db1->prepare($sql)->execute();

        }
        $db1->commit();


        // Statusbar
        //echo "\n$lower - $upper\n";
        $perc = ceil(($upper/$setSize) *40);
        $bar = "\r[". str_repeat("=", $perc - 1). ">";
        $bar .= str_repeat(" ", 40 - $perc) . "] - " . number_format($upper / $setSize * 100, 3) ."% ";
        echo "\033$bar"; // Note the \033. Put the cursor at the beginning of the line

        //While_Exit
        $copy = ($upper == $setSize ? 0 : 1);
        //$copy = 0;

    }
    echo "\nall done, BYE! \n";
}
?>

Answer 1

Oh thank you! I haven't seen the failure.

Now the code works, nearly completely, my last problem is the following:

in the first Select query i get from '->rowCount();' the number of rows of the select query.

But with my insertion copys a bit more data. But in the second Select i have the same Where clause likewise the first Select query.

I get from the CLI-Print : No. of Rows: 655056 and it writes in the Database 658597

so how do i get the 3541 rows?

or is maybe a failure inside the Insert?

Greetings

Answer 2

You're using the stepsize as the offset each time:

LIMIT  $lower OFFSET $stepSize

Should be:

LIMIT $stepSize OFFSET $lower