Jcode Jcode - 5 months ago 9
SQL Question

Pulling ID from Table as soon as its assigned

I have some PHP code which inserts data into a table collected from an html form, it assigns a unique ID automatically when the query is submitted successfully.

Currently I generate a reference, but this is a poor solution as it will end up having duplicate entries long term... How can I pull the unique ID and post it into the echo statement, will I have to run another query to get it? or can it be done in this one block of code?

if($fnameErr === null) {
$ref = rand(1, 1000);
$query = "INSERT INTO Taxi(fname, lname, contact, unit, snumber, sname, suburb, dsuburb, dt, ref, status) VALUES('{$fname}','{$lname}','{$contact}','{$unit}','{$snumber}','{$sname}','{$suburb}','{$dsuburb}','{$dt}','{$ref}','{$status}')";
if (mysqli_query($conn, $query)) {

$date = date_create($dt);
echo "Thank you! Your booking reference number is {$ref} you will be picked up in front of your provided address at " . date_format($date, 'g:ia \o\n l jS F Y') . ".";

}else{
echo "<br>Failed<br>";
echo mysqli_error($conn);
}
}
else {
echo "<br>Unable to add your booking.<br>";
}

Answer

For the taxi table, you need to create a unique id as the primary key of this table:

ALTER TABLE `taxi` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);

When you insert some entries, you don't need to make a new id by yourself, but it will be done automatically.

So after the insert query, you can get the new id by:

mysqli_query($conn,"INSERT INTO Taxi (fname, lname...) VALUES (...");

echo "New record has id: " . mysqli_insert_id($conn); 

And you have the id of the last entry.