mercredi 1 novembre 2017

Inserting data into two tables at the same time to relate movies to genres

I looking for help, Appreciate.

QUESTION 1 : I am beginner to SQL query in PHP, the problem is HOW to auto insert the movie_id, genre_id into movie_genre table WHEN I insert the movie id, name, genre into movie table at the same time?

QUESTION 2 : In PHP what is the correct way to write prepare query?

QUESTION 3 : In PHP how to write the bind_param query?

SQL query and PHP coding at below:

Table movie:

CREATE TABLE movie (
 id INT,
 name VARCHAR,
 genre VARCHAR,
 PRIMARY KEY (id)  
)

Table genre:

CREATE TABLE genre(
 id INT,
 type VARCHAR,
 PRIMARY KEY (id) 
)

Table movie_genre (many-to-many);

CREATE TABLE movie_genre(
 movie_id INT,
 genre_id INT,
 CONSTRAINT FK_movie_id 
 FOREIGN KEY (movie_id) REFERENCES movie (id),
 CONSTRAINT FK_genre_id 
 FOREIGN KEY (genre_id) REFERENCES genre(id)
)

PHP CODE:

 if($stmt = $conn->prepare("START TRANSACTION"."INSERT INTO movie (id,name,genre) VALUES (?, ?, ?)" ."INSERT INTO movie_genre (movie_id, genre_id) VALUES (?, ?)" ."COMMIT")) {

    $stmt->bind_param("iss"."si",$id, $name, $genre .$name, $genre);
    $stmt->execute();                     
    $stmt->close();




Aucun commentaire:

Enregistrer un commentaire