Latest web development tutorials

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, ...)

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 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', 'john@example.com') ";

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', 'john@example.com') ";

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', 'john@example.com') ";
// 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;
?>