CODEWITHSUNDEEP
phpsqlauto-increment
Samuel
Samuel

Reputation: 225

How to store the auto increment value into a php variable?

I have written my sql below and it works. As I've set my particulars_id to autoincrement, i have to use Last_Insert_ID() in order for the database to use the next id using auto increment. However, i would like to store that value into a php variable. Is that possible? 

 $addquery = "INSERT INTO Particulars (Particulars_ID, Name, Identification_Number, Number, Nationality, Status, Remarks)
                    VALUES(LAST_INSERT_ID(),'$_POST[newname]', '$_POST[newic]','$_POST[newnumber]','$_POST[newnationality]','$_POST[newstatus]','$_POST[newremarks]')";

Upvotes: 1

Views: 3788

Answers (1)

dimlucas
dimlucas

Reputation: 5141

When you insert a row into a table that has an AUTO_INCREMENT field that field will be incremented automatically as the name implies. You don't need to tell MySQL to increment it, you don't have to provide a value for the auto-increment field at all.

So to begin with, remove the call to LAST_INSERT_ID() from your query:

$addquery = "INSERT INTO Particulars (Name, Identification_Number, Number, Nationality, Status, Remarks)
                '$_POST[newname]', '$_POST[newic]','$_POST[newnumber]','$_POST[newnationality]','$_POST[newstatus]','$_POST[newremarks]')";

Notice how I completely removed the Particulars_ID from the query.

Second, this is not directly related to your question but your query is vulnerable to SQL Injection. When accepting user input you should avoid concatenating it to your query, instead use Prepared Statements and modify your query like this:

$addquery = "INSERT INTO Particulars (Name, Identification_Number, Number, Nationality, Status, Remarks)
VALUES(?,?,?,?,?,?)";

You can then prepare a statement and bind the values from $_POST. This essentially sanitizes user input. Read more about prepared statements here

START OF EDIT

An example of binding the real values to the ? placeholders using PDO:

//First prepare the statemt
$db->prepare("INSERT INTO Particulars (Name, Identification_Number, Number,Nationality, Status, Remarks)
              VALUES(?,?,?,?,?,?)";
//Start binding values to placeholders
$db->bindValue(1, $_POST['name']);
$db->bindValue(2, $_POST['Identification_Number'];
$db->bindValue(3, $_POST['Number'];
//Bind the rest of the values in the same way

END OF EDIT

About the id of the last inserted row you will need to run a separate query to get it. So after you run the above query and if insertion is successful you can run a query like this:

SELECT LAST_INSERT_ID() AS id FROM Pariculars

Upvotes: 1

Related Questions