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, Jan 08 '16 at 21:14
Why don't you use the SQL `LAST_INSERT_ID()` function instead of doing it in PHP? — Barmar, Jan 08 '16 at 22:29

score 2 · Accepted Answer · answered Jan 08 '16 at 21:09

$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

score 1 · Answer 2 · answered Jan 08 '16 at 21:09

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 08 '16 at 21:09

thst

4,592
1
26
40

Thank you! Yes, that was my mistake. – skwidbreth Jan 08 '16 at 21:13

PHP to mySQL - duplicating entry when finding insert ID

2 Answers2