PHP/MYSQL - Inserting multiple rows using mysql_insert_id?

BACKGROUND:

I am uploading basic client information to a database from a client form. When the client clicks "Finished" the information entered such as (Name, City, Country etc.) are stored in the client_info table.

As an alternative the client can also upload image(s) to the server. The information of these images (image_path, image_name) are then stored in another, separate table called client_images, which has a Foreign Key linked with client_info.

client_info table:

CREATE TABLE IF NOT EXISTS `client_info` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_name` varchar(100) NOT NULL,
  `client_city` varchar(100) NOT NULL,
  `client_country` varchar(10) NOT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10446 ;

client_images table:

CREATE TABLE IF NOT EXISTS `client_images` (
  `image_id` int(11) NOT NULL AUTO_INCREMENT,
  `image_path` varchar(100) NOT NULL,
  `image_name` varchar(100) NOT NULL,
  `client_id` int(11) NOT NULL,
  PRIMARY KEY (`image_id`),
  KEY `client_id` (`client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=415;

---

PROBLEM:

I have managed to successfully upload one row to the client_images table. Adding more than one row results in the following error:

Cannot add or update a child row: a foreign key constraint fails (`db`.`client_images`, CONSTRAINT `client_images_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `client_info` (`client_id`) ON DELETE CASCADE ON UPDATE CASCADE) : 1452

However, when I test it through the console adding multiple rows and an already existing client_id it works perfectly well.

Example:

mysql> INSERT INTO client_images (image_path, image_name, client_id) VALUES ('pathname1', 'name1', 10241);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO client_images (image_path, image_name, client_id) VALUES ('pathname2', 'name2', 10241);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO client_images (image_path, image_name, client_id) VALUES ('pathname3', 'name3', 开发者_C百科10241);
Query OK, 1 row affected (0.00 sec)

I need to point out that I have it lined up like this:

File: (create_article.php)

insert_client_info($client_name, $client_city, $client_country);    

include('uploads.php');

?>

<?php mysql_close($connection); ?>

Inside uploads.php I have a foreach loop that counts how many files that are going to be uploaded and in the end of the loop I currently have a function insert_images($image_path, $image_name);

FUNCTION (insert_images):

function insert_images($directory_path, $image_name) {  

    global $connection;
    $client_id=mysql_insert_id();

    $query = "INSERT INTO client_images (
        image_path, image_name, client_id
    ) VALUES (
        '{$directory_path}', '{$image_name}', '{$client_id}'
    )";
$result = mysql_query($query, $connection);

if ($result) {
    // Success!
    echo "<p></p>";
    echo "Successfully uploaded the file.";
    echo "<p></p>";

} else {
    // Display error message.
    echo "<p>" . mysql_error() ." : ".mysql_errno(). "</p>";
}
return $result;
}

Reading other similar problems I understand that the Achilles heel is probably mysql_insert_id() and that somehow it uses another connection different from the first one in the foreach loop, thus resulting in getting the before mentioned MYSQL error.

Thanks / EDZ

---

this part:

$query = "INSERT INTO client_images (
        image_path, image_name, client_id
    ) VALUES (
        '{$directory_path}', '{$image_name}', '{$client_id}'
    )";

Would be:

$query = "INSERT INTO client_images (
        image_path, image_name, client_id
    ) VALUES (
        '$directory_path', '$image_name', $client_id
    )";

You must escape the variables with mysql_real_escape_string() to prevent sql injection

Also $client_id is numeric so does not need to quote '$client_id'

---

Adding more than one row results in the following error:

The last insert id is the one generated by the most recent INSERT in the current MySQL session that generated an auto-incrementing id.

When you insert the first image, the most recent INSERT is the one for client_info.

But once you get to the second image, the most recent INSERT is that of the first image, so the value returned by mysql_insert_id() is different. Likewise every subsequent INSERT changes the value returned by mysql_insert_id().

You should set the variable $client_id before your foreach loop starts to insert images, and pass that variable to each call to insert_images(). In uploads.php:

$client_id=mysql_insert_id();
foreach (...) {
    insert_images($client_id, ...);
}

Pass $client_id as a parameter of your insert_images() function, instead of setting $client_id in the function.

function insert_images($client_id, $directory_path, $image_name) {  
    ...
}

---

P.S.: @tttony has a good point when he reminds you to be careful about SQL injection.

See also my presentation SQL Injection Myths and Fallacies, or the chapter on SQL injection in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.