PHP insert array of values

Question

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!

I edited the post to show the value of $sqlField variable, which was a string containing all the column names of the table. — Brent Connor
– Brent Connor, Commented Nov 4, 2014 at 15:25
@BrentConnor I need to know how all the $sqlV are being populated from. In the meantime, try an array method $stmt->execute(array($sqlV[0], $sqlV[1]), etc.); — Funk Forty Niner
– Funk Forty Niner, Commented Nov 4, 2014 at 15:58

David Myers · Accepted Answer · 2014-11-04 16:23:05Z

2

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.

edited Nov 4, 2014 at 16:23

answered Nov 4, 2014 at 16:17

David Myers

4113 silver badges10 bronze badges

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

Comments

Funk Forty Niner · Accepted Answer · 2014-11-04 14:41:59Z

2

This piece of code:

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

remove the quotes

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

answered Nov 4, 2014 at 14:41

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

8 Comments

Brent Connor Over a year ago

I removed the quotes, but its still not making it to the database. if I use print_r on $stmt after execute, it outputs: [ PDOStatement Object ( [queryString] => INSERT INTO inventory (Whse,Product,Description,QtyOnHand,QtyUnavail,BinLoc1,BinLoc2,GLCost,OnhandTotalValue,UnavailTotalValue,GrandTotalValue) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ) ]

Funk Forty Niner Over a year ago

@BrentConnor What errors are you getting? Plus can you var_dump your query?

Brent Connor Over a year ago

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

Funk Forty Niner Over a year ago

@BrentConnor Instead of bindParam try bindValue

Brent Connor Over a year ago

changed to bindValue. No affect.

|

Collectives™ on Stack Overflow

PHP insert array of values

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related