Using bound parameters from two select statements and use them in one insert

Question

I'm trying to figure out how to take bound parameter values from two different select statements and insert them into a new table.

My first select gets an array without issue. My second select gets a count, as well as a number 180 divided by the count, but uses returned values from the first select in it's where clause.

This all works perfect.

Now, based on each of the 2nd select's execution I want to insert the values from each select into one new table.

I'm binding the values from each select individually, but how can I bind the values from both selects to execute my insert on them?

$selectPLC = "
    SELECT 
        sku_id, s.frm as frm, sg.code as code, s.matrl as matrl, s.color as color, cst
    FROM plc;
";

try {
    $PLCcheck = $MysqlConn->prepare($selectPLC);
    $detailRslt = $PLCcheck->execute();

     while ($PLCRow = $PLCcheck->fetch(PDO::FETCH_ASSOC)) {

        print_r($PLCRow); //This prints the first array I need

        $salesValues = [
        ":cst" => $PLCRow["cst"],
        ":frm" => $PLCRow["frm"],
        ":matrl" => $PLCRow["matrl"],
        ":color" => $PLCRow["color"]

        ];

        $checkSales = "
            SELECT
            count(*) as salesCount,
            180/count(*) as countDIV
            FROM orders
                WHERE cstnoc = :cst
                AND frmc = :frm
                AND covr1c = :matrl
                AND colr1c = :color
                AND date(substr(dateField1,1,4)||'-'||substr(dateField1,5,2)||'-'||substr(dateField1,7,2) ) between current_Date - 180 DAY AND current_Date
        ";

        try{
            $salesCheck = $DB2Conn->prepare($checkSales);
            $salesResult = $salesCheck->execute($salesValues);

            while ($salesRow = $salesCheck->fetch(PDO::FETCH_ASSOC)) {

                print_r($salesRow); //This prints the 2nd array I need

                $countValues = [
                    ":salesCount" => $salesRow["salesCount"],
                    ":countDiv" => $salesRow["countDiv"]
                ];

                $insertSales = "

                    INSERT INTO metrics (cst, frm, matrl, color, salesCount, countDIV )
                    VALUES (
                        :cst, //from first array
                        :frm, //from first array
                        :matrl, //from first array
                        :color, //from first array
                        :salesCount, //from 2nd array
                        :countDiv //from 2nd array
                    )
                ";

                $salesInsertPrep = $DB2Conn->prepare($insertSales);
                $salesInsertExec = $salesInsertPrep->execute($countValues);
            }

        }catch(PDOException $ex) {
            echo "QUERY TWO FAILED!: " .$ex->getMessage(); 
        }
    }

}catch(PDOException $ex) {
    echo "QUERY ONE FAILED!: " .$ex->getMessage();
}

score 1 · Accepted Answer · answered Sep 25 '18 at 18:11

When your fetching the second array set of values, rather than add them to a new array, you can add them into the array of the first values, so instead of...

       $countValues = [
            ":salesCount" => $salesRow["salesCount"],
            ":countDiv" => $salesRow["countDiv"]
        ];

use

$salesValues[":salesCount"] = $salesRow["salesCount"];
$salesValues[":countDiv"] = $salesRow["countDiv"];

and then...

$salesInsertExec = $salesInsertPrep->execute($salesValues);

The other thing is that you can prepare your insert once outside the loop and then execute it each time in the loop, so this would look like...

    $insertSales = "
                INSERT INTO metrics (cst, frm, matrl, color, salesCount, countDIV )
                VALUES (
                    :cst, //from first array
                    :frm, //from first array
                    :matrl, //from first array
                    :color, //from first array
                    :salesCount, //from 2nd array
                    :countDiv //from 2nd array
                )
            ";

    $salesInsertPrep = $DB2Conn->prepare($insertSales);
    while ($salesRow = $salesCheck->fetch(PDO::FETCH_ASSOC)) {

        print_r($salesRow); //This prints the 2nd array I need

        $salesValues[":salesCount"] = $salesRow["salesCount"];
        $salesValues[":countDiv"] = $salesRow["countDiv"];
        $salesInsertExec = $salesInsertPrep->execute($salesValues);
   }

OK - can you put the prepare back into the loop to see if that solves this problem. — Nigel Ren, Sep 25 '18 at 18:28
Ok I tried that, but still the same error. And the parameters seem to be in the order of the insert too, if that were to cause an issue — Geoff_S, Sep 25 '18 at 18:30
Can you give the actual error text so I can check what it may be — Nigel Ren, Sep 25 '18 at 18:31
SQLSTATE[HY010]: Function sequence error: 0 [Microsoft][ODBC Driver Manager] — Geoff_S, Sep 25 '18 at 18:34
Have a read of https://stackoverflow.com/questions/27268959/sqlstatehy010-function-sequence-error-0-during-insert, it may help. Mainly around data types and lengths of values. If this does work, move the prepare back outside the loop as it's more efficient. — Nigel Ren, Sep 25 '18 at 18:37

Using bound parameters from two select statements and use them in one insert

1 Answers1