CODEWITHSUNDEEP
phpmysqlsqlpdo
James Dawson
James Dawson

Reputation: 5409

PDO not inserting more than one row in table

I'm having trouble inserting image data into my database. I have a table called images. When dumped with PHPMyAdmin it looks like this:

CREATE TABLE IF NOT EXISTS `images` (
  `id` int(11) NOT NULL,
  `orig_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `hash` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
  `filename` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `uploaded` datetime NOT NULL,
  `views` int(11) NOT NULL DEFAULT '0',
  `album_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `server_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `server_id` (`server_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This is the code I'm using to insert rows:

// Database connection
$db = new PDO('mysql:host=localhost;dbname=testdb', 'root', '');                  

// some code...

$st = $db->prepare('INSERT INTO `images` (orig_name, hash, filename, uploaded, server_id)
                    VALUES (?, ?, ?, ?, (SELECT `id` FROM `servers` WHERE `name` = ?))');
$st->execute(array($origName, $fileHash, $filename, date('c'), $server));

// more code...

// Database cleanup
$st = null;
$db = null;

The script returns no errors, and works flawlessly for the first row inserted. If the script runs again, it fails to insert any more rows in the images table. I see no reason why it'd behave like this, the data going into each field is unique each time (except for the server_id field).

Upvotes: 1

Views: 466

Answers (1)

andrewsi
andrewsi

Reputation: 10732

Your id field isn't set to auto_increment.

The first record that you post will be added, with a NULL as id; the second record won't be added because there's already a record with NULL as the primary key, so it'll fail - you don't have any error checking in the code, so it won't be printing out the errors it's getting back.

Upvotes: 1

Related Questions