Get last id mysqli php

How to Retrieve the Last Inserted ID in PHP and MySQL

In this article, we will guide you through the steps of retrieving the last inserted ID in PHP and MySQL. When inserting data into a database, it is often necessary to retrieve the ID of the last inserted record. This is useful for a variety of purposes, such as linking related records in the database.

Prerequisites

Before we start, there are a few prerequisites that you should have in place:

• A web server with PHP installed
• A MySQL database
• Access to the PHPMyAdmin interface or similar database management tool

Establishing a Connection

The first step in retrieving the last inserted ID is to establish a connection to the database. This is done by using the mysqli_connect() function, which requires the following parameters:

• The name of the database server
• The username used to access the database
• The password for the database user
• The name of the database to connect to

Here is an example of how to establish a connection to a database:

 $server = "localhost"; $username = "root"; $password = "password"; $database = "database_name"; $conn = mysqli_connect($server, $username, $password, $database); if (!$conn) < die("Connection failed: " . mysqli_connect_error()); > echo "Connected successfully"; ?>

Writing the SQL Statement

Once a connection to the database has been established, the next step is to write the SQL statement that will be used to insert the data into the database.

Here is an example of a SQL statement that inserts data into a database table:

INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3');

Executing the SQL Statement

Once the SQL statement has been written, the next step is to execute it. This is done by using the mysqli_query() function, which requires the following parameters:

Here is an example of how to execute the SQL statement:

 $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('value1', 'value2', 'value3')"; if (mysqli_query($conn, $sql)) < echo "New record created successfully"; > else < echo "Error: " . $sql . "
" . mysqli_error($conn); > ?>

Retrieving the Last Inserted ID

Once the data has been inserted into the database, the next step is to retrieve the ID of the last inserted record. This is done by using the mysqli_insert_id() function, which requires the following parameter:

Here is an example of how to retrieve the last inserted ID:

 $last_id = mysqli_insert_id($conn); echo "Last inserted ID is: " . $last_id; ?>

Closing the Connection

Once the last inserted ID has been retrieved, it is important to close the connection to thedatabase to avoid any security risks. This is done by using the mysqli_close() function, which requires the following parameter:

Here is an example of how to close the connection to the database:

Conclusion

In this article, we have shown you how to retrieve the last inserted ID in PHP and MySQL. By following these steps, you should be able to retrieve the ID of the last inserted record in your database. It is important to note that the exact steps may vary depending on the specific database management system that you are using, so be sure to consult the documentation for your system for more information.

Источник

Последний добавленный идентификатор PHP MySQL

В этом уроке вы узнаете, как получить уникальный идентификатор последней вставленной строки из таблицы базы данных MySQL с помощью PHP.

Как получить идентификатор последней вставленной строки

В уроке о вставка данных в таблицу БД MySQL мы узнали, что MySQL автоматически генерирует уникальный идентификатор для столбца с атрибутом AUTO_INCREMENT каждый раз, когда мы вставляем новую запись или строку в таблицу. Однако бывают ситуации, когда нам нужен автоматически сгенерированный идентификатор, чтобы вставить его во вторую таблицу. В этих ситуациях мы можем использовать функцию PHP mysqli_insert_id() для получения последнего сгенерированного идентификатора.

В следующем примере мы будем использовать ту же таблицу persons, которую мы создали в уроке создание таблиц PHP MySQL, которая имеет четыре столбца id, first_name, last_name и email, где id — столбец первичного ключа с атрибутом AUTO_INCREMENT .

Пример

 // Попытка выполнения запроса вставки $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if(mysqli_query($link, $sql)) < // Получить последний вставленный идентификатор $last_id = mysqli_insert_id($link); echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; >else < echo "Ошибка вставки $sql. " . mysqli_error($link); >// Закрыть соединение mysqli_close($link); ?>

connect_error); > // Попытка выполнения запроса вставки $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; if($mysqli->query($sql) === true)< // Получить последний вставленный идентификатор $last_id = $mysqli->insert_id; echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; > else< echo "Ошибка вставки $sql. " . $mysqli->error; > // Закрыть соединение $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("Ошибка подключения. " . $e->getMessage()); > // Попытка выполнения запроса вставки try< $sql = "INSERT INTO persons (first_name, last_name, email) VALUES ('Ron', 'Weasley', 'ronweasley@mail.com')"; $pdo->exec($sql); $last_id = $pdo->lastInsertId(); echo "Записи успешно вставлены. Последний вставленный идентификатор: " . $last_id; > catch(PDOException $e)< die("Ошибка вставки $sql. " . $e->getMessage()); > // Закрыть соединение unset($pdo); ?>

Источник

PHP MySQL Insert and Return ID

The topic that you’re about to learn is the PHP MySQL insert and return ID method. This topic goes along with its examples with complete discussions and visuals as proof of its function.

In PHP, when we insert data to MySQL (database), we use queries and it returns the ID of the inserted data. To give you a clear explanation of the process, we will have the examples below.

The examples below will give you hints of different ways of inserting data in MySQL. The methods are possible through PHP which then will return the ID of the data.

How to Get the ID of the Last Inserted Row?

In PHP programming, we can insert and update data into MySQL. You just have to make sure that you have a structured database or table in your MySQL that will serve as the data storage. Moreover, you also need to assign the ID column of your table as an AUTO_INCREMENT field.

As soon as you insert or update the data from the MySQL database, you can also get the ID of that information.

In the example below, we will use the table “Sample” present in the MySQL server with its ID column set as AUTO_INCREMENT to insert data through PHP.

Sample Database

CREATE TABLE Sample ( ID INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL, gender VARCHAR(50) NOT NULL, age INT(11) NOT NULL ); 

In the sample database, we create table naming Sample and it has five attributes or columns. The first column which is ID with its data type as an integer ( INT ) and has its characters length of eleven ( 11 ).

The ID attribute of the columns is AUTO_INCREMENT to make sure that there will be no duplicates of ID among the data.

Next, we also have the firstname , lastname , and gender attributes with variable characters ( VARCHAR ) as their data types. Their data are limited to 50 characters and NOT NULL which means that their field or column must not be blank.

Lastly, we have the age attribute which is also an integer ( INT ), has the character length of 50 , and must not be blank.

This time, we have our database available for use, so we will use it with the following examples:

Example Program on How To Get the ID of The Last Inserted Record Using (MySQLi Object Oriented)

For our first example, we will use the MySQLi Object-Oriented method to implement the PHP MySQL insert and return ID application.

connect_error) < die("Connection failed: " . $con->connect_error); > $sql = "INSERT INTO sample (firstname, lastname, gender, age) VALUES ('Jane', 'Jenner', 'Female', '25')"; if ($con->query($sql) === TRUE) < $lastID = $con->insert_id; echo "Data was successfully inserted. Last ID inserted is: " . $lastID; > else < echo "Error: " . $sql . "
" . $con->error; > $con->close(); ?> 

This example will show the exact implementation when using the MySQLi object-oriented to connect the PHP and MySQL server. This method will enable us to insert data into the database (local server).

Example Program on How To Get ID of The Last Inserted Record Using (MySQLi Procedural)

This time, we will have another method of implementing PHP MySQL insert and return ID. The method that we will use for the next example is MySQLi procedural.

The MySQLi procedural has some differences from the above example yet will produce the same process and output as the mysqli object-oriented method.

 $sql = "INSERT INTO sample (firstname, lastname, gender, age) VALUES ('John', 'Jenner', 'Male', '26')"; if (mysqli_query($con, $sql)) < $lastID = mysqli_insert_id($con); echo "Data was successfully inserted. Last ID inserted is: " . $lastID; >else < echo "Error: " . $sql . "
" . mysqli_error($con); > $con->close(); ?> 

So, in this example, we were also trying to insert the data from PHP to the MySQL server and it should return the ID of the inserted data.

As you can see in the output that the connection between PHP and MySQL as well as the insertion of data was successful. This proves that the previous method and the mysqli procedural method have similar output.

Example Program on How To Get ID of The Last Inserted Record Using (PDO)

To insert information or data to MySQL from PHP, we can also use the PDO and return the data’s ID.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO sample (firstname, lastname, gender, age) VALUES ('Jade', 'Jenner', 'Female', '14')"; // use exec() because no results are returned $con->exec($sql); $lastID = $con->lastInsertId(); echo "Data was successfully inserted. Last ID inserted is: " . $lastID; > catch(PDOException $e) < echo $sql . "
" . $e->getMessage(); > $con = null; ?>

This example will test if the PDO method is applicable and works the same with mysqli object-oriented and mysqli procedural methods.

So the output shows that we can also use the PDO method to insert data from PHP to the MySQL server. This means that with either of the three methods, we can still implement the application of PHP and MySQL connection as well as the data insertion.

Related Articles

Conclusion

In conclusion, this topic gives you three alternative methods which you can use to apply the PHP MySQL insert and return ID. This topic enlightens you with the new methods that you can try and is advantageous for web-development skills.

Upon going through this topic, you will be confident enough on building up and developing your web. This is because learning the PHP and MySQL connection is crucial in any aspect of web development.

As you can see, the topic stresses the important methods that you should possess in order to be efficient in the industry of web development. This discussion gives the edge and boosts your skills which preps you for a new chapter of your journey.

If you’d like to add or open concern about this, you can leave a comment below. Good day!

Leave a Comment Cancel reply

You must be logged in to post a comment.

Источник