INSERT语句不适用于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.

Answer 1

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).