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;
}
prepare()andbindValue()on all of those?$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.query()is when you want to "query" and not perform an insert or update, or anything that would modify a table. @jrlainfiesta