PDO not inserting - no error

Question

I do not understand why these lines do not work:

  $host = 'localhost';
  $dbname='mydbname';
  $login='mylogin';
  $pwd='mypwd';
  $datex = date('Y/m/d H:i:s');
  $nomx = 'jrmy';
  $numx = '007';
  try {
    $bdd = new PDO('mysql:host='.$host.';dbname='.$dbname, $login, $pwd);
  }
  catch(Exception $e) {
    die('Erreur : '.$e->getMessage());
  }
  $bdd->exec('INSERT INTO dossiers(date, nom, numero, disp) VALUES(\''.$datex.'\', \''.$nomx.'\', \''.$numx.'\', \'Y\')');
  $id = $bdd->lastInsertId();
  $bdd= null;
  echo 'id: '.$id;

disclosure: the script worked on my apache server, and since I installed my script on a IIS server it stopped working...

AND the query function works perfectly... query OK, insert NO

EDIT : thank you all! really!

Concatenating the values into the query completely defeats the purpose of prepared statements. — Austin Brunkhorst, Jan 23 '13 at 16:56
@AustinBrunkhorst He is not using a prepared statement, but rather a straight query execution. — Mike Brant, Jan 23 '13 at 16:56
"This diesel gas worked fine in my tractor, but after I put it in my car the engine won't start?" turn up [error_reporting](http://ca2.php.net/manual/en/function.error-reporting.php) and see if you're getting an error about a missing extension. Also, wrap your `exec` in an `if` statement to see if it was actually successful or not. — Sammitch, Jan 23 '13 at 16:57
It's not a good practice to use `date` as a column name in MySQL. — Lion, Jan 23 '13 at 16:58
You are doing nothing to try to check you query attempt for errors. I would suggest starting there. Also you might want to make you life easier in writing your query by using double quotes around the query string so you don't have to escape all the single quotes. You should also consdier using prepared statements as suggested by @AustinBrunkhorst. — Mike Brant, Jan 23 '13 at 16:58
you shouldn't be generating a date string in PHP like that anyways. you could just use `now()` in mysql and let it do all that itself. your date format string is incorrect anyways. mysql uses `-` as separators, not `/`. — Marc B, Jan 23 '13 at 17:05

score 33 · Accepted Answer · answered Jan 23 '13 at 16:57

33

You need to tell PDO to raise an error

$bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Please note that the way you're handling exceptions is quite useless. It would be better to remove try-catch blocks from the code

answered Jan 23 '13 at 16:57

Your Common Sense

156,878
40
214
345

my problem was: no default value for 1 column in my database - it was not a problem with my previous config and it raises an error now – jrm Jan 23 '13 at 17:04
2

I'm really starting to hate PDO – jim smith Sep 11 '14 at 20:18
3

@jimsmith I concur, its funny howmany things were got wrong with PDO when it was designed from the ground up to replace the main mysql_ functions. Not raising any error by default is an extraordinary decision. – Toby Allen Nov 15 '14 at 09:59
problem was 'database no selected' – TheTechGuy Nov 26 '17 at 17:22

Ibu · Answer 2 · 2022-06-21T03:08:51.723

4

You are not making full use of PDO. Use parameters to insert your data

Example:

$bdd->exec('INSERT INTO dossiers(date, nom, numero, disp) VALUES(?, ?,?, ?)');
$bdd->bindParam(1, $datex);  
$bdd->bindParam(2, $nomx);  
$bdd->bindParam(3, $numx);
$bdd->bindParam(4, 'Y');

And use this to raise errors:

$bdd->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

edited Jun 21 '22 at 03:08

answered Jan 23 '13 at 16:58

Ibu

42,752
13
76
103

`$bdd->` not `$bdd>` – syahid246 Jun 20 '22 at 10:19

score 0 · Answer 3 · answered Jan 23 '13 at 17:02

If the code was working correctly in Apache and stopped working in IIS, then there is a possibility that the environment change is causing it, and nothing in the code. Have you checked if PDO extensions in windows are enabled?

These are the main four PDO dlls for windows:

extension=php_pdo.dll
extension=php_pdo_mysql.dll
extension=php_pdo_mssql.dll
extension=php_pdo_odbc.dll

Of these I think the first two will need to be enabled in your php.ini to make PDO extension work

score 0 · Answer 4 · answered Oct 15 '14 at 22:30

I had a similar problem and it turned out to be because I didn't have auto-commit enabled for PDO.. using an explicit transaction helped me out;

self::$_pdo->beginTransaction();
$sql = "INSERT INTO redirects (source, target, http_status_code, aspect_id) VALUES(?, ?, ?, ?)";
$query = self::$_pdo->prepare($sql);
$query->execute(array($_POST['source'], $_POST['target'], $_POST['http_status_code'], $_POST['aspect_id']));
self::$_pdo->commit();

(yes there a million ways of doing the above in a better manner, but i'm just demonstrating the transaction. Here, read this: http://php.net/manual/en/pdo.transactions.php)

PDO not inserting - no error

4 Answers4