CODEWITHSUNDEEP
phpsql-serverpdo
Brent Connor
Brent Connor

Reputation: 628

PHP insert array of values

I haven't been able to get my INSERT statement to work. I know the problem is how I'm trying to bind the parameters. INSERT was working before I added parameter binding.

$inventoryQuery = "INSERT INTO inventory (Whse, Product, Description,
QtyOnHand, QtyUnavail, BinLoc1, BinLoc2, GLCost, OnhandTotalValue,
UnavailTotalValue, GrandTotalValue) 
    VALUES ('?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?');";
// execute INSERT statement
$conn = $GLOBALS['conn'];
$stmt = $conn->prepare($inventoryQuery);
$stmt->bindParam(1, $sqlV[0]);
$stmt->bindParam(2, $sqlV[1]);
$stmt->bindParam(3, $sqlV[2]);
$stmt->bindParam(4, $sqlV[3]);
$stmt->bindParam(5, $sqlV[4]);
$stmt->bindParam(6, $sqlV[5]);
$stmt->bindParam(7, $sqlV[6]);
$stmt->bindParam(8, $sqlV[7]);
$stmt->bindParam(9, $sqlV[8]);
$stmt->bindParam(10, $sqlV[9]);
$stmt->bindParam(11, $sqlV[10]);

$stmt->execute();

Output using var_dump($stmt):

object(PDOStatement)[2] public 'queryString' => string 'INSERT INTO inventory (Whse,Product,Description,QtyOnHand,QtyUnavail,BinLoc1,BinLoc2,GLCost,OnhandTotalValue,UnavailTotalValue,GrandTotalValue) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);' (length=192)

I have seen and tried many ways to handle binding parameters. And now my head is swimming. If you could provide a solution and an explanation, I would be so happy.

Solution from all info provided:

    try{
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $inventoryQuery = "INSERT INTO inventory ($sqlField) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    // execute INSERT statement
    $conn = $GLOBALS['conn'];
    $stmt = $conn->prepare($inventoryQuery);
    $stmt->bindValue(1, $sqlV[0]);
    $stmt->bindValue(2, $sqlV[1]);
    $stmt->bindValue(3, $sqlV[2]);
    $stmt->bindValue(4, ((int) ((float) $sqlV[3])));
    $stmt->bindValue(5, ((int) ((float) $sqlV[4])));
    $stmt->bindValue(6, $sqlV[5]);
    $stmt->bindValue(7, $sqlV[6]);
    $stmt->bindValue(8, ((float) $sqlV[7]));
    $stmt->bindValue(9, ((float) $sqlV[8]));
    $stmt->bindValue(10, ((float) $sqlV[9]));
    $stmt->bindValue(11, ((float) $sqlV[10]));

    $stmt->execute();
}
catch(PDOException $ex){
    var_dump($ex);
}

Thanks to everyone that helped!

Upvotes: 1

Views: 164

Answers (2)

David Myers
David Myers

Reputation: 411

I'm going to go with incorrect column types in this one. Try surrounding your execution in a try-catch block and make sure SQL isn't throwing any errors. If it is, it will likely show you exactly what you need to do.

Here's an example:

try
{
    $stmt->execute();
}
catch (PDOException $ex)
{
    // log this object or just the error messages
    log($ex->getMessage()); // Assuming you have some sort of global logger set up.
}

P.s. You can remove the semi colon in your query string. Being at the end shouldn't hurt anything, but if you were to add anything after it, it would terminate your statement early.

Upvotes: 2

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

This piece of code:

('?', '?', '?', '?', '?', '?', '?', '?', '?', '?', '?')

remove the quotes

(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Upvotes: 2

Related Questions