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

-- 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: <code>coding</code>
--

-- --------------------------------------------------------

--
-- Table structure for table <code>login</code>
--

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

--
-- Dumping data for table <code>login</code>
--

INSERT INTO <code>login</code> (<code>id</code>, <code>user</code>, <code>pass</code>) VALUES
(1, 'vivek@gmail.com', 'pass1234'),
(2, 'codingcyber@gmail.com', 'pass1234');

-- --------------------------------------------------------

--
-- Table structure for table <code>profile</code>
--

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

--
-- Dumping data for table <code>profile</code>
--

INSERT INTO <code>profile</code> (<code>id</code>, <code>first_name</code>, <code>last_name</code>, <code>login_id</code>) VALUES
(1, 'VIVEK', 'KUMAR', 1),
(2, 'CODING', 'CYBER', 2);

PHP Code

Create two PHP files,

  1. index.php
  2. register.php

code for index.php

<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>

code for register.php

&lt;?php
mysql_connect('localhost', 'root', '');
mysql_select_db('coding');
$sql = &quot;INSERT INTO <code>login</code> (user, pass) VALUES ('{$_POST['user']}', '{$_POST['pass']}')&quot;;
$result = mysql_query($sql) or die(mysql_error());
$rid = mysql_insert_id();
if($result){
	$query = &quot;INSERT INTO <code>profile</code> (first_name, last_name, login_id) VALUES ('{$_POST['first_name']}', '{$_POST['last_name']}', '$rid')&quot;;
	mysql_query($query);
}
?&gt;

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:

x

Join My Facebook Group to Receive Coding Tutorial for Free

email-subsribe