1

I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and metadata_image_id, how can I do it when I add the records for the first time?

With the code below, I can only add individual records without a connection.

    $stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
    $stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
    $stmt->execute();
    $stmt->close();


    $stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
    $stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
    $stmt->execute();
    $stmt->close();

I am using MySQL and InnoDB.

My database structure:

Table : book
book_id (PK)
book_original_name
book_tr_name
book_publication_date
book_synopsis

Table : metadata_images
metadata_image_id (PK)
metadata_image_value
type
size


Junction Table : book_main_cover
book_id (FK from book)
book_metadata_id (FK from metadata_images)
2

If I guessed correct you are using PHP PDO, so what your are looking for is this function. http://us3.php.net/manual/en/pdo.lastinsertid.php. It will return the id off the last insert tuple into the database which you can save in a variable to be used in the insert for the junction table.

$stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
$stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
$stmt->execute();
$book_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
$stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
$stmt->execute();
$cover_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO book_main_cover VALUES(?,?)");
$stmt->bind_param($book_id, $cover_id);
$stmt->execute();
$stmt->close();
4
  • I am using mysqli(oop). Actually I only need the SQL query, it's the part I lack of :) – salep Dec 23 '14 at 2:17
  • Well just a simple INSERT INTO book_main_cover VALUES ($book_id, $metadata_image_id) both variables can be obtained via php.net/manual/en/function.mysql-insert-id.php – Benjamin Dec 23 '14 at 2:39
  • I do that with the code I provided, but I was searching a way to do it for my junction table. For instance, I am adding a book named "Metamorphosis" and its id is 6. I add its cover at the same time, and the cover's id is 1. I want to add these values into my junction table via an SQL query. Does your answer apply for this situation, too? Here is an example to make it clearer : i.imgur.com/PLQkBXg.png – salep Dec 23 '14 at 9:10
  • Yes. I updated my answer acordingly – Benjamin Dec 24 '14 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.