How to avoid Query doubles INSERT into database?

Question

Problem:
I have made a simple form that uses PHP to pass information to my database via a INSERT query. However, every time I run it, it tries to put the information in twice. How can I avoid this?

Explanation:
I first insert the answers, into my answers table, save the AnswerID as a variable. Then do the save with my question table and lastly I use the two saved variables containing the ID's into my question_answers table.

My code:

if (isset($_POST['textinput1']) && !empty($_POST['textinput1'])) { $text1 = mysqli_real_escape_string($conn, $_POST['textinput1']); $text2 = mysqli_real_escape_string($conn, $_POST['textinput2']); $q_text = mysqli_real_escape_string($conn, $_POST['textarea']); $stmt = $conn->prepare("INSERT INTO answers (Answer1Text, Answer2Text) VALUES (?, ?)"); $stmt->bind_param('ss', $text1, $text2); $stmt->execute(); $answerid = $stmt->insert_id; $stmt = $conn->prepare("INSERT INTO question (QuestionText) VALUES (?)"); $stmt->bind_param('s', $q_text); $stmt->execute(); $questionid = $stmt->insert_id; if ($stmt->execute()) { $stmt = $conn->prepare("INSERT INTO question_answers (AnswerID, QuestionID) VALUES (?, ?)"); $stmt->bind_param('ss', $answerid, $questionid); $stmt->execute(); echo "<h2>Dit spørgsmål er nu lagt op på siden!</h2>"; echo "<h3>Tusinde tak for din interesse for SMIL - Skodfri Århus.</h3>"; } else { echo "ERROR: Could not able to execute . " . mysqli_error($conn); } } // close connection mysqli_close($conn); ?>

My tables of importance:
question: QuestionID(PK), QuestionText
answers: AnswerID(PK), Answer1Text, Answer2Text
question_answers: QuestionAnswerID(PK), QuestionID(FK), AnswerID(FK)

Ps. I prefer not to use composite unique constraint as a solution.

Also a side-question, should $stmt->insert_id variables be mysqli_real_escape_string?

You run $stmt->execute() twice. With prepared statements you don't need to be escaping. — chris85
– chris85, Commented Dec 13, 2015 at 18:59
Thank you for your answer, that was indeed the problem. Ah, does that mean this code is safe against SQL injections as it is? — Nenn
– Nenn, Commented Dec 13, 2015 at 19:12

RiggsFolly · Accepted Answer · 2015-12-13 18:58:35Z

Your problem is that you have executed the second query TWICE

if (isset($_POST['textinput1']) && !empty($_POST['textinput1'])) { $text1 = mysqli_real_escape_string($conn, $_POST['textinput1']); $text2 = mysqli_real_escape_string($conn, $_POST['textinput2']); $q_text = mysqli_real_escape_string($conn, $_POST['textarea']); $stmt = $conn->prepare("INSERT INTO answers (Answer1Text, Answer2Text) VALUES (?, ?)"); $stmt->bind_param('ss', $text1, $text2); $stmt->execute(); $answerid = $stmt->insert_id; $stmt = $conn->prepare("INSERT INTO question (QuestionText) VALUES (?)"); $stmt->bind_param('s', $q_text); $stmt->execute(); $questionid = $stmt->insert_id; // THIS IS THE SECOND EXECUTION OF QUERY 2 if ($stmt->execute()) { $stmt = $conn->prepare("INSERT INTO question_answers (AnswerID, QuestionID) VALUES (?, ?)"); $stmt->bind_param('ss', $answerid, $questionid); $stmt->execute(); echo "<h2>Dit spørgsmål er nu lagt op på siden!</h2>"; echo "<h3>Tusinde tak for din interesse for SMIL - Skodfri Århus.</h3>"; } else { echo "ERROR: Could not able to execute . " . mysqli_error($conn); } } // close connection mysqli_close($conn); ?>

Instead try this as the IF test

 //if ($stmt->execute()) { if ( isset($answerid,$questionid) ) {

By defining my queries as '$stmt1' and '$stmt2' and then as safety, having a 'if ( isset($answerid,$questionid) ) {' helped solve the problem. Thanks so much for all the answers.

pvg · Accepted Answer · 2015-12-13 18:58:39Z

if ($stmt->execute()) {

this runs one of your statements a second time. You should assign the return value to a variable if you need it for something later.

Collectives™ on Stack Overflow

How to avoid Query doubles INSERT into database?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related