Using array variable in MySQL PDO query

Question

I'm trying to fix a mysql update within a php script using PDO.

I have an array, $order_ids, that contains the correct records and I then use a variable called $placeholders to implode those values for my select query, $detailstatcheck.

All of this works but the update I added at the bottom is not working, when I run the script it prints that 0 records are updated.

I just want to say "update record if order_id is in $order_ids or $placeholders"

$order_ids = [];
while ($row = $ordStat->fetch(PDO::FETCH_ASSOC)) {
    $order_ids[] = $row['order_id'];
}

if (count($order_ids) > 0) {

$placeholders = implode(',', array_fill(0, count($order_ids), '?'));
//This query works
$detailStatCheck = "
    SELECT 
         invnoc as INVOICE,
         fstatc as STATUS,
         cstnoc AS DEALER,
         framec AS FRAME,
         covr1c AS COVER,
         colr1c AS COLOR ,
         extd2d AS SHIPDATE,
         orqtyc AS QUANTITY
    FROM GPORPCFL
    WHERE invnoc IN ($placeholders)
";

try {
    $detailCheck = $DB2conn->prepare($detailStatCheck);
    $detailRslt = $detailCheck->execute($order_ids);
    $count2 = $detailCheck->fetch();
    print_r($order_ids);
    print_r($count2);
} catch(PDOException $ex) {
    echo "QUERY FAILED!: " .$ex->getMessage();
}

//this update doesn't work
$updateShipped = "
    UPDATE order_status
    set date_updated = current_date()
    where order_id in ($placeholders) //Not sure if syntax is correct here
";


try{
            $updateStatus = $MysqlConn->prepare($updateShipped);
            $statUpdateRslt = $updateStatus->execute();
            $count = $updateStatus->rowcount();
            }
            catch(PDOException $ex)
            {
                echo "QUERY FAILED!: " .$ex->getMessage();
            }
        echo "Records Updated: " . $count . "\n";

UPDATE:

Structure of order_status table

order_id  |  order_status  |  is_placement  |  date_updated
-----------------------------------------------------------
12345           S                  1            2018-03-09

order_id = int(11)

order_status = varchar

is_placement = bit

date_updated = DATE

I'm getting this now QUERY FAILED!: SQLSTATE[HY093]: Invalid parameter number: no parameters were boundRecords Updated: 0 — Geoff_S
– Geoff_S, Commented Mar 9, 2018 at 5:00
Please show the structure of your order_status table with dummy entry. — Nikhil G
– Nikhil G, Commented Mar 9, 2018 at 5:04

voter · Accepted Answer · 2018-03-09 08:43:04Z

2

You are missing $order_ids parameter while executing query: $statUpdateRslt = $updateStatus->execute($order_ids);

answered Mar 9, 2018 at 8:43

voter

3581 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geoff_S Over a year ago

Yes, that was it! Thanks

Collectives™ on Stack Overflow

Using array variable in MySQL PDO query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related