zeeshan haider February 2016

how do i build logic for query

i have two tables named student and qualification. I inserted the data in student table. Now i want to insert data in qualification table based on id generated in student table. I tried to write my queries in such a way

$sql = "INSERT INTO student (name,fathername,degreetitle)
VALUES ('$name','$fathername','$degreetitle')";

$sql_combine = "SELECT user_id FROM student WHERE fathername = $fathername";

$sql1 = "INSERT INTO information (user_id,cell,email)
VALUES ('$sql_combine','$cell','$email')";


if(mysqli_query($conn, $sql) && mysqli_query($conn, $sql1) && mysqli_query($conn, $sql_combine))
{

    header("Location: successful_message.php");

}

Why isn't this working?

Answers


john elemans February 2016

You need a new column in your information table for the id field from the student table. As @Franz pointed out you should use last_insert_id() to get the value, then include it in the insert into the information table. That is how you link the two tables.


Franz Gleichmann February 2016

$sql1 ="INSERT INTO student (name,fathername,degreetitle)
VALUES ('$name','$fathername','$degreetitle')";
$sql2 = "SELECT last_insert_id() as id";

mysqli_query($conn, $sql1); //here you insert
$res = mysqli_query($conn, $sql2); //here you fetch the ID you inserted
$id = mysqli_fetch_array($res)['id'];

$sql3 = "INSERT INTO information (user_id,cell,email)
VALUES ('$id','$cell','$email')"; //here you use that said ID in your second query
mysqli_query($conn, $sql3); //aaand you insert

as i said: last_insert_id() to the rescue

Post Status

Asked in February 2016
Viewed 3,428 times
Voted 9
Answered 2 times

Search




Leave an answer