Get the last insert query id in PHP

Get the last insert query id in PHP

Get the last insert query id in PHP after performing a query on a table, this is useful if you want to insert one form data into two different tables and want to reference the first table id in second table. first we will insert in one table and after that we will get the query id, and insert it in the second table for the purpose of reference.

We can directly use mysql_insert_id function in PHP, but below is an example of how it works and it is useful when developing web appliations.

Here is the simple example, we are registering a user and we want to separate user login details in one table and profile details in another table. First of all we are inserting login details in one table, then we are getting back the inserted id, then adding this id to next query and inserting the data in profile table. By adding login table’s query id, we can combine those two tables when we have to select all the data at once.

For the Database, i have created two tables name as login and profile. In the login table it contains username and password. And in the profile table it contains first_name, last_name and login_id. The login_id is the first inserted query id.

The tables look like these
Get the last insert query id in PHP

And the login table structure and data is
Get the last insert query id in PHP

And the profile table structure and data is
Get the last insert query id in PHP

Code for Get the last insert query id in PHP

SQL Code

[sql] — phpMyAdmin SQL Dump
— version 3.5.1
— http://www.phpmyadmin.net

— Host: localhost
— Generation Time: Mar 15, 2013 at 05:54 PM
— Server version: 5.5.24-log
— PHP Version: 5.4.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


— Database: coding

— ——————————————————–


— Table structure for table login

CREATE TABLE IF NOT EXISTS login (
id int(11) NOT NULL AUTO_INCREMENT,
user varchar(60) NOT NULL,
pass varchar(60) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


— Dumping data for table login

INSERT INTO login (id, user, pass) VALUES
(1, ‘[email protected]’, ‘pass1234’),
(2, ‘[email protected]’, ‘pass1234’);

— ——————————————————–


— Table structure for table profile

CREATE TABLE IF NOT EXISTS profile (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(60) NOT NULL,
last_name varchar(60) NOT NULL,
login_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


— Dumping data for table profile

INSERT INTO profile (id, first_name, last_name, login_id) VALUES
(1, ‘VIVEK’, ‘KUMAR’, 1),
(2, ‘CODING’, ‘CYBER’, 2);
[/sql]

PHP Code

Create two PHP files,

  1. index.php
  2. register.php

code for index.php
[html] <html>
<head>
<title>Get the last insert query id in PHP</title>
</head>
<body>
<form action="register.php" method="POST">
<fieldset>
<legend> Login Details</legend>
<label>User Name : </label><input type="text" name="user" /><br />
<label>Password : </label><input type="password" name="pass" /><br />
</fieldset>

<fieldset>
<legend> Profile Details</legend>
<label>First Name : </label><input type="text" name="first_name" /><br />
<label>Last Name : </label><input type="text" name="last_name" /><br />
<input type="submit" value="submit" />
</fieldset>

</form>
</body>
</html>
[/html]

code for register.php
[php] <?php
mysql_connect(‘localhost’, ‘root’, ”);
mysql_select_db(‘coding’);
$sql = "INSERT INTO login (user, pass) VALUES (‘{$_POST[‘user’]}’, ‘{$_POST[‘pass’]}’)";
$result = mysql_query($sql) or die(mysql_error());
$rid = mysql_insert_id();
if($result){
$query = "INSERT INTO profile (first_name, last_name, login_id) VALUES (‘{$_POST[‘first_name’]}’, ‘{$_POST[‘last_name’]}’, ‘$rid’)";
mysql_query($query);
}
?>
[/php]

We are getting the last insert query id in line 6, the whole example is to show how it is useful when working with web applications.

Vivek Vengala
 

Vivek Vengala is a Online Entrepreneur, Web Developer from Hyderabad India.

Click Here to Leave a Comment Below 0 comments

Leave a Reply: