PHP MySQL inserting data
Use MySQLi and PDO MySQL to insert data
After creating databases and tables, we can add data to the table.
Here are some syntax rules:
- PHP SQL query statements must use quotes
- String value in the SQL query statement must be in quotes
- Values do not need quotes
- NULL value does not need quotes
INSERT INTO statement is usually used to add a new record to MySQL table:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
VALUES (value1, value2, value3, ...)
Learn more about SQL knowledge, please see our SQL tutorial .
In the previous sections, we have created a table "MyGuests", Field has: "id", "firstname", "lastname", "email" and "reg_date". Now, let's start to the table populated with data.
Note: If the column is set AUTO_INCREMENT (eg "id" column) or TIMESTAMP (such as "reg_date" column) ,, We do not need to specify a value in the SQL query language; MySQL will automatically add the column value. |
The following examples are to "MyGuests" table add a new record:
Examples (MySQLi - Object Oriented)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
if ($ conn-> query ($ sql) === TRUE) {
echo "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" $ conn-> error;..
}
$ Conn-> close ();
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password, $ dbname);
// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
if ($ conn-> query ($ sql) === TRUE) {
echo "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" $ conn-> error;..
}
$ Conn-> close ();
?>
Examples (MySQLi - process-oriented)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = mysqli_connect ($ servername, $ username, $ password, $ dbname);
// Test connection
if (! $ conn) {
die ( "Connection failed:" mysqli_connect_error ().);
}
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
if (mysqli_query ($ conn, $ sql)) {
echo "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" mysqli_error ($ conn);..
}
mysqli_close ($ conn);
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDB";
// Create connection
$ Conn = mysqli_connect ($ servername, $ username, $ password, $ dbname);
// Test connection
if (! $ conn) {
die ( "Connection failed:" mysqli_connect_error ().);
}
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
if (mysqli_query ($ conn, $ sql)) {
echo "The new record is inserted successfully";
} Else {
. Echo "Error:" $ sql "<br>" mysqli_error ($ conn);..
}
mysqli_close ($ conn);
?>
Examples of (PDO)
<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDBPDO";
try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = $ dbname", $ username, $ password);
// Set the PDO error mode for an exception
$ Conn-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
// Use exec (), no results are returned
$ Conn-> exec ($ sql);
echo "The new record is inserted successfully";
}
catch (PDOException $ e)
{
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";
$ Dbname = "myDBPDO";
try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = $ dbname", $ username, $ password);
// Set the PDO error mode for an exception
$ Conn-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);
$ Sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ( 'John', 'Doe', '[email protected]') ";
// Use exec (), no results are returned
$ Conn-> exec ($ sql);
echo "The new record is inserted successfully";
}
catch (PDOException $ e)
{
.. Echo $ sql "<br>" $ e-> getMessage ();
}
$ Conn = null;
?>