Query error MySQL with PDO

Question

I have a problem in executing a query with MySQL and PHP. I connect to the database with PDO and when I run the query it returns the following errors:

errorCode = 42000 
errorInfo = array ([0] => 00000 [1] => [2] =>)

After the modifications suggested, the code I use is this

require "connPDO.php";
ini_set('display_errors',1);
error_reporting(E_ALL);
$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $PDO->prepare("INSERT INTO 'titacc' ('file', 'RGS', 'RGP', 'RGD', 'RGDG',          'RGOG', 'DenOrg', 'CodFiscOrg', 'TipoOrg', 'IntrTipoTass', 'IntrIncid', 'DenLocale', 'CodLocale', 'DataEv', 'OraEv',
'TipoGen', 'IncidGen', 'OrdPostoCodOrd', 'OrdPostoCapienza', 'TitoloEv', 'ProduttoreCinema', 'Autore', 'Esecutore', 'Nazionalita', 'Distributore', 'TATipoTit', 'TAQta',
'TACorrLordo', 'TAPrevendita', 'TAIVACorr', 'TAIVAPrev', 'TAImpPrest') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?");

$stmt->execute(array(
$file,
$RGS,
$RGP,
$RGD,
$RGDG,
$RGOG,
$DenOrg,
$CodFiscOrg,
$TipoOrg,
$IntrTipoTass,
$IntrIncid,
$DenLocale,
$CodLocale,
$DataEv,
$OraEv,
$TipoGen,
$IncidGen,
$OrdPostoCodOrd,
$OrdPostoCapienza,
$TitoloEv,
$ProduttoreCinema,
$Autore,
$Esecutore,
$Nazionalita,
$Distributore,
$TATipoTit,
$TAQta,
$TACorrLordo,
$TAPrevendita,
$TAIVACorr,
$TAIVAPrev,
$TAImpPrest
));
if (! $stmt->exe1cute() )
{
echo "<b>Errore nella query!</b>"; echo"<br/>";
print_r($stmt->errorCode()); echo"<br/>";
print_r($stmt->errorInfo()); echo"<br/>";
print_r($PDO->errorInfo()); echo"<br/>";
die();
} else {
echo "<b>Inserimento avvenuto con successo</b>";
}
$PDO = null;

I read other posts and attempted to fix this, but the error remains. If the query is executed from the console, I have no error.

I don't know where your problem is yet, but when using an extension like PDO you should also make use of prepared statements ([bindParam()](http://php.net/manual/de/pdostatement.bindparam.php) or [bindValue()](http://www.php.net/manual/de/pdostatement.bindvalue.php), alternatively you can replace them directly in the `execute()`-function). This makes your code significantly easier to understand and to maintain, and is safe against SQL injections. — padarom, Apr 08 '14 at 11:45
Do `echo $query` and see what the output is, you have an error in your syntax. Also, why would you use pdo like that? — dkasipovic, Apr 08 '14 at 11:47
@Kasipovic: I tried it with echo and do not receive any error... — Filippo, Apr 08 '14 at 12:17
@Padarom:Thanks for the info. I have read and applied, but the error is always the same — Filippo, Apr 11 '14 at 11:53

score 3 · Answer 1 · answered Apr 08 '14 at 11:45

3

Use prepared statements.

$st = $pdo->prepare("INSERT INTO `table`(`col1`, `col2`) VALUES (:col1value, :col2value)");

Then on execute give argument

$st->execute(array(
    'col1value'=>$value1,
    'col2value'=>$value2
));

http://www.php.net/manual/en/pdostatement.execute.php#109030

answered Apr 08 '14 at 11:45

cfv1000

453
3
10

I modified as suggested, but the error is always the same – Filippo Apr 11 '14 at 11:51

Your Common Sense · Answer 2 · 2014-04-11T14:59:47.040

1

Change your code to

ini_set('display_errors',1);
error_reporting(E_ALL);

$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $PDO->prepare("INSERT INTO tbl VALUES (?, ?, ... ?");
$stmt->execute(array(
    $col1,
    $col2,
    ...
    $col31
));

and post here the real query with real code you run.

edited Apr 11 '14 at 14:59

answered Apr 11 '14 at 14:24

Your Common Sense

156,878
40
214
345

after making the suggested changes, I get the following error: Fatal Error: Uncaught Exception 'PDOException' with message '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 ryght syntax to use near "at line 6' in file.php Stack trace: #0 file.php(126):PDOStatement->execute(Array)#1 {main} thrown in file.php on line 126 – Filippo Apr 11 '14 at 15:02
at line 126 I have the following code: )); – Filippo Apr 11 '14 at 15:03
you have to show line 6 of your **query** – Your Common Sense Apr 11 '14 at 15:09
I modified the request with the actual query – Filippo Apr 11 '14 at 15:26
that's what you had to do in the first place. you are using wrong quotes. remove quotes from table and field names – Your Common Sense Apr 11 '14 at 15:30
I removed the quotes around the table name and field names, but the error remains ... – Filippo Apr 11 '14 at 15:42
I applied the quotes on tables and fields by following the suggestions given on this post [link](http://stackoverflow.com/questions/10556941/php-pdo-syntax-error-or-access-violation-1064-on-insert) – Filippo Apr 11 '14 at 15:44
there is no line 6 in the query yuo posted here. you have to post EXACT CODE YOU RUN and get error with – Your Common Sense Apr 11 '14 at 15:45
solved ... there was a syntax error .. I inserted exe1cute instead of execute the line: if (! $ stmt-> exe1cute ()). Thank you all for your suggestions and availability. – Filippo Apr 11 '14 at 15:59

Query error MySQL with PDO

2 Answers2