CODEWITHSUNDEEP
phpmysql
BRBT
BRBT

Reputation: 1487

Get auto increment ID after insert to use on another insert

I am using an insert query to add information from a PHP form into a table as well as upload an image. The form information and the image directory path are to be stored in separate tables. Here is what I am doing but it just does not seem to work. 

//insert data from my form into DB, id is auto incremented so it's not in the insert.
$query = "INSERT INTO infotable (number, name, address, city, province, postal_code VALUES ('$facilityNumber', '$facilityName', '$facilityAddress', '$facilityCity', '$facilityProvince', '$facilityPostalCode' )";
mysqli_query($dbc, $query);

//query used to get the id of the facility we had just entered
$getFacilityID = "SELECT id FROM infotable WHERE number = '$facilityNumber' AND name = '$facilityName' "
    . "AND address = '$facilityAddress' AND city = '$facilityCity'";

$queryData = mysqli_query($dbc, $getFacilityID);
$row = mysqli_fetch_assoc($queryData);

//attempt to echo out the value of the ID (this is always empty)
echo $row['id'];

//insert image into image table + id from infotable ... I haven't even got to test this yet.
$imageQuery = "INSERT INTO photo (id, photo, photo_desc)"
            . "VALUES ($row['id'], $facilityPhoto, $facilityPhotoDesc)";

mysqli_query($dbc, $imageQuery);

Upvotes: 0

Views: 159

Answers (2)

NaijaProgrammer
NaijaProgrammer

Reputation: 2957

To get the auto-increment id, just use the built-in mysqli_insert_id function:

$getFacilityID = mysqli_insert_id($dbc);

Your code could then be re-written as below:

//insert data from my form into DB, id is auto incremented so it's not in the insert.
$query = "INSERT INTO infotable (number, name, address, city, province, postal_code VALUES ('$facilityNumber', '$facilityName', '$facilityAddress', '$facilityCity', '$facilityProvince', '$facilityPostalCode' )";
mysqli_query($dbc, $query);

//query used to get the id of the facility we had just entered
$getFacilityID = mysqli_insert_id($dbc);

//attempt to echo out the value of the ID (this is always empty)
echo $getFacilityID;

//insert image into image table + id from infotable ... I haven't even got to test this yet.
$imageQuery = "INSERT INTO photo (id, photo, photo_desc)"
        . "VALUES ($getFacilityID, $facilityPhoto, $facilityPhotoDesc)";

mysqli_query($dbc, $imageQuery);

Upvotes: 1

DiegoCoderPlus
DiegoCoderPlus

Reputation: 770

You need mysqli_insert_id to solve your problem.

See the procedural example: http://www.w3schools.com/php/php_mysql_insert_lastid.asp

Upvotes: 1

Related Questions