Instructobit
Inserting records into a table is one of the core functions of SQL. To execute SQL statements in PHP, you can make use of the
mysqli
class which can be used to perform actions such as selecting, inserting, updating, deleting and more.
Using mysqli to insert records into a database is not too different from using a select statement, but there are a few changes you will need to make to your script.
This tutorial assumes a basic knowledge of
connecting to databases in PHP using mysqli
as well as
inserting records into a database using SQL
.
Inserting static data into a table using PHP
To insert static (unchanging) data into a MySQL table, you can make use of the query() method. Unlike using the SELECT statement, there will be no records returned after the query has executed so there will be no need to fetch the results.
As an example, say we had a table called "user" in the database "testDB" with the columns "name", "surname" and "username", and we wanted to insert a new user, we could use the following script:
<?php
//declare database credentials
$host = "localhost";
$username = "yourUsername";
$password = "yourPassword";
$dbName = "testDB";
//connect to database
$con = new mysqli( $host, $username, $password, $dbName );
//check connection
if( $con->connect_error ){
die( "Unable to connect: " . $con->connect_error );
}
$sql = "INSERT INTO user( name, surname, username ) VALUES( 'john', 'doe', 'user1' )";
//execute query
if( $con->query( $sql ) ){
echo( "User added to database" );
}
else{
echo( "User could not be added: " . $con->error );
}
$con->close();
?>
This script will insert the new user into the database.
Inserting dynamic data into a table using PHP
To insert dynamic data into a table, you can simply concatenate your SQL statement with variable(s) containing the values you wish to insert. The values can come from various sources such as HTML forms.
Say we again wanted to insert a new user into the "user" table, but this time we will allow the user to enter their own information and submit it through an HTML form using the POST method. The PHP script for this would be as follows:
<?php
//retrieve form inputs
$name = $_POST[ "name" ];
$surname = $_POST[ "surname" ];
$username = $_POST[ "username" ];
//declare database credentials
$host = "localhost";
$username = "yourUsername";
$password = "yourPassword";
$dbName = "testDB";
//connect to database
$con = new mysqli( $host, $username, $password, $dbName );
//check connection
if( $con->connect_error ){
die( "Unable to connect: " . $con->connect_error );
}
//concatenate variables into the string
$sql = "INSERT INTO user( name, surname, username ) VALUES( '" . $name . "', '" . $surname . "', '" . $username . "' )";
//execute query
if( $con->query( $sql ) ){
echo( "User added to database" );
}
else{
echo( "User could not be added: " . $con->error );
}
$con->close();
?>
This script will retrieve the values of the inputs from the submitted HTML form and insert them as a new record into the "user" table.
Warning:
The above example is not secure as it is vulnerable to SQL injection. The above example should not be used for websites in which users other than yourself are allowed to enter data. If you want to allow users on your site to enter data, have a look at
PHP secure database interaction with mysqli
.
