mysql execute() (PDO) does not INSERT anything in table

Question

I am facing a weird problem. I'm using a piece of code that's workin 100% throughout my website, but in a particular place does not work. Here's the code :

$stmt_insert_query = "INSERT INTO mya_events(event_id, artist_id, event_title, date, event_text, event_start, event_duration, genre, soundcloud_preview, event_type, country, ext, clicks, active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                                $stmt_insert = $db->prepare($stmt_insert_query);
                                $stmt_insert->bindValue(1, $next_avail, PDO::PARAM_INT);
                                $stmt_insert->bindValue(2, $_id, PDO::PARAM_INT);
                                $stmt_insert->bindValue(3, $_POST['event_title'], PDO::PARAM_STR);
                                $stmt_insert->bindValue(4, $event_date, PDO::PARAM_STR);
                                $stmt_insert->bindValue(5, $_POST['event_text'], PDO::PARAM_STR);
                                $stmt_insert->bindValue(6, $_POST['event_start'], PDO::PARAM_STR);
                                $stmt_insert->bindValue(7, $_POST['event_duration'], PDO::PARAM_STR);
                                $stmt_insert->bindValue(8, 'electronica', PDO::PARAM_STR);
                                $stmt_insert->bindValue(9, '', PDO::PARAM_STR);
                                $stmt_insert->bindValue(10, 'dj_event', PDO::PARAM_STR);
                                $stmt_insert->bindValue(11, 'US', PDO::PARAM_STR);
                                $stmt_insert->bindValue(12, '', PDO::PARAM_STR);
                                $stmt_insert->bindValue(13, 0, PDO::PARAM_INT);
                                $stmt_insert->bindValue(14, 'y', PDO::PARAM_STR);
                                $stmt_insert->execute();

After this piece of code i'm displaying a text which displays. Also i checked for errors with print_r($db->errorInfo()); but no error is displaying. Also tried try - catch but nothing.

I entered manually from phpMyAdmin a row and it works.

Where am I wrong ? I checked the code 10 times and it's perfect.

update

i found an error in the log of the server exactly where i do ->execute

[11-Oct-2012 14:48:15] PHP 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 right syntax to use near ''event_id', 'artist_id', 'event_title', 'date', 'event_text', 'event_start', 'ev' at line 1' in /home/cubeworx/public_html/electronicdancemusic.net/MYArtist/admin/list_events.php:527 Stack trace:

0 /home/cubeworx/public_html/electronicdancemusic.net/MYArtist/admin/list_events.php(527): PDOStatement->execute()

1 {main}

thrown in /home/cubeworx/public_html/electronicdancemusic.net/MYArtist/admin/list_events.php on line 527

Check out [How to squeeze error message out of PDO?](http://stackoverflow.com/q/3726505) — Pekka, Oct 11 '12 at 11:13
yes, i don't get any errors, and the code is safe and sound. — Adrian Tanase, Oct 11 '12 at 11:17
I'm pretty sure you *are* getting errors, but not seeing them. I can see one problem in your code, the use of `date` without backticks which is a reserved word in mySQL (and needs backticks). Hence the link to the question - getting an error message from PDO can be cumbersome — Pekka, Oct 11 '12 at 11:19
@AdrianTanase: Just to be sure, you have tried setting `$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );`, right? Don't forget that you might be working on a different DB, too. best to change `INSERT INTO mya_events` to `INSERT INTO dbName.mya_events`. Lastly: try setting your ini to `E_ALL | E_STRICT` to avoid warnings and errors being supressed — Elias Van Ootegem, Oct 11 '12 at 11:19
You have a column named `date` and that is a mysql reserved word. In your query, put backticks around that column name or simply change it to something that isn't a reserved word. — N.B., Oct 11 '12 at 11:30
again, with old style mysql_* it works perfectly, but now as I discovered the error (and updated the main question) I see that the sql_query ends abbruptly in the middle of one of the fields....i have much longer queries on the website that work perfectly...and the mysql_max_packet_size cannot be activated on my current shared hostin, i need to upgrade...maybe this would be it ? but i have much longer queries that work fine... — Adrian Tanase, Oct 11 '12 at 11:53
**finally** discovered what it was....i was using a $_POST var that was not defined (not existing)....my bad...old style mysql_* commands were saving anyways in the table but PDO was throwing sticks at stones at me with critical errors because he couldn't find the var :)) thanks @EliasVanOotegem for your setAttribute thing, with your help i could debug the thing finally :)) a beer on me! :)) — Adrian Tanase, Oct 11 '12 at 12:14

Elias Van Ootegem · Accepted Answer · 2014-03-31T11:48:42.930

As a response to the OP's final comment:
A beer would be nice, but since technology hasn't gotten to the stage where it's possible to transfer liquids over any protocol (yet) an up-vote will do ;). Since this solved your problem, and it can save you hours of debugging in the future: a couple of tips (when using PDO):

try setting your ini to E_ALL | E_STRICT to avoid warnings and errors being suppressed
Always use $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); to force PDO to throw PDOExceptions when a query fails.
When query-ing, use dbName.tblName as much as possible, to avoid accidentally working on the wrong DB
Always check if a variable/array key exists before using it
Use prepared statements, preferably with named placeholders to ensure your code is more readable for yourself and others

score 2 · Answer 2 · answered Oct 11 '12 at 11:18

2

Good practise is to use `` signs for table names, columns and such so it will never interfere with the database engine itself.

INSERT INTO mya_events(event_id, artist_id, event_title, ...

will become

INSERT INTO `mya_events`(`event_id`, `artist_id`, `event_title`,

Always quote your values with '', also numeric values. You send a query by its text anyway.

I think it is useful that you show the query that seems to fail. You could add a check after the insertion, if the row is not in database then print it somewhere in a log, and show us that particular query.

answered Oct 11 '12 at 11:18

Mike de Klerk

11,906
8
54
76

The quoting can't be the issue here, IMO: it's a PDO prepared statement and he passes the types for each param, so they should be quoted correctly. – Elias Van Ootegem Oct 11 '12 at 11:23
IMO, it's *not* considered good practise to use backticks. Instead, I'd argue it's good practise to not use reserved keywords. – Berry Langerak Oct 11 '12 at 11:39
"Good" practice or not, it still doesn't fix the issue, it just makes it a more-quoted fail. Either way, after all, there's a [flowchart for good code](http://www.xkcd.com/844/) too (yes, that's a joke). Also, are you familiar with PDO in particular much, Mike? I'm not sure where you'd like that "final" query to come from after an insert attempt, as it is not possible ([as laid out in detail in this answer](http://stackoverflow.com/a/2411208/1728884)) to get that form of the _query_ that's run on the DB. I've built a few applications with PDO and never been able to log a "final" query either. – jimcavoli Oct 11 '12 at 12:02
1

@jim the quotes actually probably fix the problem, which is probably the column badly named `date`. – Pekka Oct 11 '12 at 12:04
@Pekka Well, hey, looks like it's time for me to go back to my code! Either way, this difficulty in debugging is one of the reasons I've abandoned PHP in general and PDO when possible professionally. Upvotes for you guys haha – jimcavoli Oct 11 '12 at 12:05
@jim yeah, I hear ya. I don't understand why they made PDO so mute by default. – Pekka Oct 11 '12 at 12:06
yes i totally think they will do display more *debug* info in future updates of PDO...i have finally found my problem thanks to @EliasVanOotegem :)) with the debug increased i managed to get an error from the log....the thing is old style mysql_* commands worked fine even if a variable was *not defined* (not empty!, but undefined, nonexistent)...but PDO does not tolerate this...so i was using a $_POST[''] variable that was not existing :)) so PDO said it was not good...thanks all you guys, you made my day brighter – Adrian Tanase Oct 11 '12 at 12:11
@Berry Langerak: What if you do not know all the reserved keywords by hard? Using backticks makes it more fail safe. – Mike de Klerk Oct 11 '12 at 13:06

score 1 · Answer 3 · answered Oct 11 '12 at 11:32

The problem has been identified above- but where your error lies is that you may be checking database errors, but you're not checking for statement errors

$smnt__insert->error_info() will have what you need.

The easiest way to trap this is to wrap the lot (prepare, bind and execute) into a try/catch block, with PDO set in exception mode error reporting. The exception thrown may come from the database or the statement, and gives you all the details. Easy way to catch both without needing to specifically check both.

score 0 · Answer 4 · answered Oct 11 '12 at 11:24

Use $stmt_insert->bindParam instead of $stmt_insert->bindValue. Here is some example code:

<?php
    /* Execute a prepared statement by binding PHP variables */
    $calories = 150;
    $colour = 'red';
    $sth = $dbh->prepare('SELECT name, colour, calories
        FROM fruit
        WHERE calories < ? AND colour = ?');
    $sth->bindParam(1, $calories, PDO::PARAM_INT);
    $sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
    $sth->execute();
    ?>

mysql execute() (PDO) does not INSERT anything in table

update

0 /home/cubeworx/public_html/electronicdancemusic.net/MYArtist/admin/list_events.php(527): PDOStatement->execute()

1 {main}

4 Answers4