PHP: PDO throws SQL Syntax Error with bound params

Question

I've read many of the other questions related to this issue, but I simply can't get to find my mistake. Here's my statement:

INSERT INTO ventas (`bodegaid`, `clienteid`, `usuarioid`, `tipodepagoid`, `fecha`, `numfact`, `serie`)
VALUES (:bodegaid, :clienteid, :usuarioid, :tipodepagoid, :fecha, :numfact, :serie)

Then I get the following error

SQLSTATE[42000]: Syntax error or access violation: 1064
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax
to use near ':bodegaid, :clienteid, :usuarioid, :tipodepagoid,
:fecha, :numfact, :serie)' at line 1

From what I've read, this can happen when you use a reserved word as the column but that's not my case (unless I missed one but I don't think so). The table and all the columns are correct.

Are there any other reasons that could cause this error? I'm almost sure this is not about a syntax error. I've been trying to fix this for hours and I think it's probably about a silly thing I'm missing out.

Here's my PHP function, I get the $new_item from POST parameters. I have also checked that they are correctly being sent from the form:

function doVenta(){
    $app = \Slim\Slim::getInstance();
    $new_item = $app->request->post();
    $sql = "INSERT INTO ventas (`bodegaid`, `clienteid`, `usuarioid`, `tipodepagoid`, `fecha`, `numfact`, `serie`)
            VALUES (:bodegaid, :clienteid, :usuarioid, :tipodepagoid, :fecha, :numfact, :serie)";

    try {
        $db = getConnection();
        $db->beginTransaction();
        $stmt = $db->query($sql); 

        $columns = getColumns('ventas');

        foreach($new_item as $col => $val){
            $paramN = ":".$col;
            $stmt->bindValue($paramN, $val);
        }
        $stmt->execute();
        $db->lastInsertId();

        $db->commit();
    } catch(PDOException $e){
        echo errorMsg($e->getMessage());
    }

    //echo json_encode($new_item);
}

And this is the connection method, I'm using PDO:

function getConnection() {
    $dbhost="localhost";
    $dbuser="root";
    $dbpass="root";
    $dbname="inventarios";

    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);  
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
}

@JayBlanchard Fine is a "reserved" word; for the ladies ;-) — Funk Forty Niner
– Funk Forty Niner, Commented Jan 30, 2015 at 16:08
@AbraCadaver $stmt = $db->query($sql); <= nope. You were right. Should have used "prepare" instead of "query", just like you said. Question would have most likely been solved 23 mins ago if OP had posted full code from the get-go. — Funk Forty Niner
– Funk Forty Niner, Commented Jan 30, 2015 at 16:12
query() is when you want to "query" and not perform an insert or update, or anything that would modify a table. @jrlainfiesta — Funk Forty Niner
– Funk Forty Niner, Commented Jan 30, 2015 at 16:20

Sonny · Accepted Answer · 2015-01-30 16:09:29Z

3

This line:

$stmt = $db->query($sql);

Should be:

$stmt = $db->prepare($sql);

answered Jan 30, 2015 at 16:09

Sonny

8,3667 gold badges66 silver badges139 bronze badges

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

Collectives™ on Stack Overflow

PHP: PDO throws SQL Syntax Error with bound params

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related