INSERT statement does not work with PDO

Question

EDIT: PDO appears to have an issue with the French character in the column name Unité. Is there a solution to this, or do I need to rename the column in the database?

SELECT statements work with PDO, but the INSERT statement does not.

Connection string:

$dbCon = new PDO("mysql:host=".$host.";dbname=".$dbName.";charset=utf8", $username, $password);

Working SELECT statement:

$sql = 'SELECT * FROM availability WHERE event_id=:postID';
$stmt = $dbCon->prepare($sql);
$stmt->execute(array(':postID'=>$postID));
$result = $stmt->fetchAll();

Non-functioning INSERT statement:

$sql = 'INSERT INTO `availability` (`event_id`,`Nom`,`Address`,`Address2`,`Tel1`,`Tel2`,`Tel3`,`Classement`,`Soleil`,`Unité`,`Dispo`,`Ville`,`Type`,`URL`,`Jour1`,`Jour2`,`Jour3`,`Jour4`,`Jour5`,`Jour6`,`Jour7`,`Jour8`,`Jour9`,`Jour10`,`Jour11`,`Jour12`,`Jour13`,`Jour14`,`Jour15`,`visible`) (SELECT :postID,`Nom`,`Address`,`Address2`,`Tel1`,`Tel2`,`Tel3`,`Classement`,`Soleil`,`Unité`,`Dispo`,`Ville`,`Type`,`URL`,`Jour1`,`Jour2`,`Jour3`,`Jour4`,`Jour5`,`Jour6`,`Jour7`,`Jour8`,`Jour9`,`Jour10`,`Jour11`,`Jour12`,`Jour13`,`Jour14`,`Jour15`,`visible` FROM `default_hotels`)';
$stmt = $dbCon->prepare($sql);
$stmt->execute(array(':postID'=>$postID));
$result = $stmt->fetchAll();

Another working SELECT statement:

$sql = 'SELECT post_title FROM wp_posts WHERE id=:postID';
$stmt = $dbCon->prepare($sql);
$stmt->execute(array(':postID'=>$postID));
$records = $stmt->fetchAll();

All of the statements appear in this order in my script. The variables are re-used and not cleared.

The SQL INSERT statement that I provided works when submitted through phpmyadmin but not PDO.

if you wanted... it'd be pretty easy to clean an integer value so that it'd be safe to do variable interpolation... `select $postID, whatever from whatever`... just make sure it contains only numbers. `is_numeric` would test it for you... or you could use regex... `$postID = preg_replace ('/[^0-9]/', '', $postID);` to actually clean it — gloomy.penguin, Jan 26 '15 at 01:45
did you try to get the error? try `print_r($stmt->errorInfo());` — ryvasquez, Jan 26 '15 at 01:51
@RxV It looks like PDO doesn't like the French character in the column `Unité`. Is there a solution to this? — gopher, Jan 26 '15 at 01:59
@RxV same issue `Array ( [0] => 00000 [1] => 1054 [2] => Unknown column 'UnitÃ©' in 'field list' ) ` — gopher, Jan 26 '15 at 02:10
@RxV There are never issues executing these queries through means other than PDO. Also, when the PDO results are returned, the French characters are not recognized; they come back as a question mark in a black diamond. — gopher, Jan 26 '15 at 02:11

score 0 · Accepted Answer · edited May 23 '17 at 11:56

The PDO charset parameter in the connection string started being supported in PHP 5.3.6. Before that it did nothing. So the problem is that the connection to your database is not actually run in utf8, but likely latin1. Hence the encoding of the column name in PHP and in MySQL doesn't match, hence MySQL misunderstands the column name, hence can't find the column you ask for.

See https://stackoverflow.com/a/279279/476 for alternative ways to specify the connection encoding, upgrade your version of PHP, or use ASCII-only for column names (that's a good idea anyway, specifically because it makes compatibility issues like this much less of a problem).

INSERT statement does not work with PDO

1 Answers1