PHP to mySQL - duplicating entry when finding insert ID

Question

I'm having an issue with my PHP form submission to MySQL - it's a multi_query insert that is supposed to feed two tables. Everything was working fine, but I recently set up a foreign key relationship and something in the process of getting the mysqli_insert_id for the entry to the parent table causes the first entry to be entered twice in that table - to use the table names from my code below, the master table entry is being inserted twice, and the local table entry is inserted once with the $master_id of the first master entry. I really only have a functional understanding of PHP - can someone explain why this is happening and how I can fix it so that the entry is inserted only once into each table?

$conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
       } 

$date = $_POST['date'];
$time = $_POST['time'];
$latitude = $_POST['latitude'];
$longitude = $_POST['longitude'];
$accuracy = $_POST['accuracy'];
$species = $_POST['species'];

$sql = "INSERT INTO master (date, time, latitude, longitude, accuracy, species, source)
VALUES ('$date', '$time', '$latitude', '$longitude', '$accuracy', '$species', 'source A');";

mysqli_query($conn, $sql);
$master_id = mysqli_insert_id($conn);

$sql .= "INSERT INTO local (Master_ID, date, time, latitude, longitude, accuracy, species)
VALUES ('$master_id', '$date', '$time', '$latitude', '$longitude', '$accuracy', '$species');";

if ($conn->multi_query($sql) === TRUE) {
   $conn->close();
   header("Location:http://d-bird.org/thank%20you.html");
   } 
else {
   echo "Error: " . $sql . "<br>" . $conn->error;
   }

You are concatenating your SQL queries in the same variable, ergo the master table gets two inserts instead of one. The first one happens before the concatenation in mysqli_query() and the second happens after the concatenation in multi_query(). Separate the queries and insert data into each table by itself. — Crackertastic
– Crackertastic, Commented Jan 8, 2016 at 21:14
Why don't you use the SQL LAST_INSERT_ID() function instead of doing it in PHP? — Barmar
– Barmar, Commented Jan 8, 2016 at 22:29

wmehanna · Accepted Answer · 2016-01-08 21:09:29Z

2

$sql .= "INSERT INTO local (Master_ID, date, time, latitude, longitude, accuracy, species)
VALUES ('$master_id', '$date', '$time', '$latitude', '$longitude', '$accuracy', '$species');";

should be

$sql = "INSERT INTO local (Master_ID, date, time, latitude, longitude, accuracy, species)
VALUES ('$master_id', '$date', '$time', '$latitude', '$longitude', '$accuracy', '$species');";

without the dot concatenation for $sql

answered Jan 8, 2016 at 21:09

wmehanna

3943 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thst · Accepted Answer · 2016-01-08 21:09:08Z

1

You are adding the second insert to the first.

changing

$sql .= "INSERT INTO local (Master_ID, date, time, ...

to

$sql = "INSERT INTO local (Master_ID, date, time, ...

should fix your issue.

answered Jan 8, 2016 at 21:09

thst

4,6021 gold badge29 silver badges40 bronze badges

1 Comment

skwidbreth Over a year ago

Thank you! Yes, that was my mistake.

Collectives™ on Stack Overflow

PHP to mySQL - duplicating entry when finding insert ID

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related