In MySQL, how to copy rows of two tables within the same table respectably using PHP?

Raag Singh Published at Dev

Raag Singh

I have two tables questions and answers and would like to copy the rows of each table into the same table using PHP. The structure of the tables are as follows:

Table: questions

Table: answers

Currently I am using following code but it is not working:

include "db.php";    
$exam_title=$_POST["title_id"];
$exam_id=$_POST["exam"];
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "insert into questions(question, option_type, media_type, media_link, exam_id)
    select question, option_type, media_type, media_link, '$exam_id' from questions where exam_id = ". $exam_id;   

$sql2 = "insert into answers(answer, ques_id, right_ans)
    select a.answer, q.ques_id, a.right_ans from questions q, answers a where q.exam_id = ". $exam_id;

if ($conn->query($sql) === TRUE) {
    echo "done";    
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}    
if ($conn->query($sql2) === TRUE) {
    echo "done2";    
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}    
$conn->close();

The issue is ques_id of the questions table and ans_id of answers table is primary key and is auto increment where ques_id of answers and ques_id of questions have relation.

Axel Amthor

If you are simply copyiing records in both tables which have a 1:1 relationship. and "questions" obviously is the master, you have new questions, all showing to the existing answers (i.e. two questions showing to just one single answer) and "dangling" answers not having any questions.

From my perspective, having a question twice but only one answer is what I call "normalization", but anyway, if you want to copy the entire set, you need (pseudo code)

Get all fields for all questions and their related answers (simple join)
for each row
   insert new question row
   get last insert id
   insert answer, set answer.ques_id to last insert id of question

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-24

Comments

0 comments

From Dev

mysql How to Find Difference between two rows in same table and list the Unmatched Records? mysql finding unmatched rows in a table

From Dev

Related Related

Article