How to insert data in PDo

Question

I use PDO and i can't insert some data:

use this code:

$sql  = 'INSERT INTO `releases` (id, artists, release, label, catalog, date, tracklist, type, status, vote, votes_count) ';
$sql .= 'VALUES (:id, :artists, :release, :label, :catalog, :date, :tracklist, :type, :status, :vote, :votes_count)';

$query = $this->db->prepare($sql);

$query->bindParam(':id', 0, PDO::PARAM_INT);
$query->bindParam(':artists', implode('|||', $data['artists']), PDO::PARAM_STR);
$query->bindParam(':release', $data['release'], PDO::PARAM_STR);
$query->bindParam(':label', $data['label'], PDO::PARAM_STR);
$query->bindParam(':catalog', $data['catalog'], PDO::PARAM_STR);
$query->bindParam(':date', $data['date'], PDO::PARAM_STR);
$query->bindParam(':tracklist', $data['tracklist'], PDO::PARAM_STR);
$query->bindParam(':type', $data['type'], PDO::PARAM_STR);
$query->bindParam(':status', $data['status'], PDO::PARAM_INT);
$query->bindParam(':vote', 0, PDO::PARAM_INT);
$query->bindParam(':votes_count', 0, PDO::PARAM_INT);

$query->execute();

but data don't inserted to database. all names checked and valid. id as AUTO_INCREMENT field.

if i use this code: $this->db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); $sql = 'INSERT INTO releases (artists, release, label, catalog, date, tracklist, type, status, vote, votes_count) '; $sql .= 'VALUES (:artists, :release, :label, :catalog, :date, :tracklist, :type, :status, :vote, :votes_count)';

$query = $this->db->prepare($sql);

$array = array(':artists'   => implode('|||', $data['artists']),
               ':release'   => $data['release'],
               ':label'     => $data['label'],
               ':catalog'   => $data['catalog'],
               ':date'      => $data['date'],
               ':tracklist' => $data['tracklist'],
               ':type'      => $data['type'],
               ':status'    => $data['status'],
               ':vote'      => 0,
               ':votes_count' => 0);

$query->execute($array);

i receive error:
Warning: PDOStatement::execute() [pdostatement.execute]: 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 'release, label, catalog, date, tracklist, type, status, vote, votes_count) VALUE' at line 1 in C:\Program Files\Wamp\www\forthcoming\application\controllers\release.php on line 89

Answer 1

bindParam uses reference semantics. I don't think you can use it on a constant, like you're doing with your PDO::PARAM_INT lines.

I never use bindParam, and I recommend that you use bindValue instead. Or better yet - Pass an associative array to execute.

Answer 2

Hello,

if ID is an auto_increment, why specify it in your insert query. This is not needed and may be the reason you don't see your inserted line.

Answer 3

date and type are reserved words in mySQL.

Surround the field names with backticks:

INSERT INTO `releases` (`id`, `artists`, `release`, `label`, `catalog`,
`date`, `tracklist`, `type`, `status`, `vote`, `votes_count`) ';

or rename them.