Cooolranjan Cooolranjan - 6 months ago 34
Javascript Question

Link one row of table to multiple rows in another table in mysql

I have this form

Form image -
From Image
After submitting the form personal details will be inserted into personal table and book details will be into book table. I would like to link one personal table row to multiple rows in book table (since it is inserting with same query) through unique id, so i can identify these books are related to this person in later time.

This is my php and mysql script

$mysqli = new mysqli($host,$user,$password,$database);

$users_firstname = $_POST['firstname'];
$users_middlename = $_POST['middlename'];
$users_lastname = $_POST['lastname'];
$users_gender= $_POST['gender'];
$users_location= $_POST['location'];
$users_email= $_POST['email'];
$users_mobile= $_POST['mobile'];

$query = "INSERT INTO personaldetails(FirstName ,MiddleName,LastName,
Gender,Location,Email,Mobile) VALUES ('$users_firstname',
'$users_middlename', '$users_lastname', '$users_gender','$users_location','$users_email','$users_mobile');";

foreach($_POST['booktitle'] as $key => $bookTitle) {
$bookTitle = mysqli_real_escape_string($mysqli, $bookTitle);
$bookGenre = mysqli_real_escape_string($mysqli, $_POST['bookgenre'][$key]);
$bookWriter = mysqli_real_escape_string($mysqli, $_POST['bookwriter'][$key]);
$bookDescription = mysqli_real_escape_string($mysqli, $_POST['bookdescription'][$key]);


$query .= "INSERT INTO bookdetails(BookTitle ,BookGenre,BookWriter,
BookDescription) VALUES('$bookTitle',
'$bookGenre', '$bookWriter', '$bookDescription');";


}



$result = mysqli_multi_query($mysqli, $query);

Answer

One posssible solution is by using mysqli_insert_id.

But you will also have to create an extra column in book table for storing user's id to relate a book with a user, let it be user_id(which will store the id of newly created user that we will get by mysqli_insert_id() )

and you will also have to execute the queries seperately to get the newly inserted user's Id.

So the code will be like -

$mysqli = new mysqli($host,$user,$password,$database);

    $users_firstname = $_POST['firstname'];
    $users_middlename = $_POST['middlename'];
    $users_lastname = $_POST['lastname'];
    $users_gender= $_POST['gender'];
    $users_location= $_POST['location'];
    $users_email= $_POST['email'];
    $users_mobile= $_POST['mobile'];

    $user_query = "INSERT INTO personaldetails(FirstName ,MiddleName,LastName,
    Gender,Location,Email,Mobile) VALUES ('$users_firstname',
    '$users_middlename', '$users_lastname', '$users_gender','$users_location','$users_email','$users_mobile');";

//execute the user query
$result = mysqli_query($mysqli, $user_query);
//get the user id of newly inserted user
$user_id = mysqli_insert_id($mysqli);

    foreach($_POST['booktitle'] as $key => $bookTitle) {
        $bookTitle = mysqli_real_escape_string($mysqli, $bookTitle);
        $bookGenre = mysqli_real_escape_string($mysqli, $_POST['bookgenre'][$key]);
        $bookWriter = mysqli_real_escape_string($mysqli, $_POST['bookwriter'][$key]);
        $bookDescription = mysqli_real_escape_string($mysqli, $_POST['bookdescription'][$key]);

//use the user id here to relate it with the book
    $book_query = "INSERT INTO bookdetails(BookTitle ,BookGenre,BookWriter,
                BookDescription, user_id) VALUES('$bookTitle',
             '$bookGenre', '$bookWriter', '$bookDescription', '$user_id');";
//execute the query for book
$result = mysqli_query($mysqli, $book_query);

}