MySQL Insert Data

To insert data into a MySQL table, you use the INSERT INTO SQL statement.

You can insert data into a table either through the mysql> command prompt window or via a PHP script.

Syntax

The following is the generic INSERT INTO SQL syntax for inserting data into a MySQL table:

INSERT INTO table_name (field1, field2, ...fieldN)
                       VALUES
                       (value1, value2, ...valueN);

If the data is of character type, it must be enclosed in single or double quotes, such as: "value".

Inserting Data via Command Prompt

Below, we will use the SQL INSERT INTO statement to insert data into the MySQL table tutorialpro_tbl.

Example

In the following example, we will insert three records into the tutorialpro_tbl table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use tutorialpro;
Database changed
mysql> INSERT INTO tutorialpro_tbl 
    -> (tutorialpro_title, tutorialpro_author, submission_date)
    -> VALUES
    -> ("Learning PHP", "tutorialpro.org", NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO tutorialpro_tbl
    -> (tutorialpro_title, tutorialpro_author, submission_date)
    -> VALUES
    -> ("Learning MySQL", "tutorialpro.org", NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> INSERT INTO tutorialpro_tbl
    -> (tutorialpro_title, tutorialpro_author, submission_date)
    -> VALUES
    -> ("JAVA Tutorial", "tutorialpro.org", '2016-05-06');
Query OK, 1 row affected (0.00 sec)
mysql>

Note: The arrow marker -> is not part of the SQL statement. It merely indicates a new line. If an SQL statement is too long, we can create a new line to write the SQL statement by pressing the Enter key. The command terminator for the SQL statement is the semicolon ;.

In the above example, we did not provide the tutorialpro_id data because this field was set to AUTO_INCREMENT when we created the table. Therefore, this field will auto-increment and does not require us to set it. The NOW() function is a MySQL function that returns the current date and time.

Next, we can view the table data with the following statement:

Reading Table Data:

select * from tutorialpro_tbl;

Output result:

Inserting Data Using PHP Script

You can use the PHP function mysqli_query() to execute the SQL INSERT INTO command to insert data.

This function has two parameters and returns TRUE on success, otherwise FALSE.

Syntax

mysqli_query(connection, query, resultmode);

Example

The following example receives data for three fields from the user and inserts it into the table:

Adding Data

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connection successful<br />';
// Set encoding to prevent Chinese character乱码
mysqli_query($conn , "set names utf8");

$tutorialpro_title = 'Learning Python';
$tutorialpro_author = 'tutorialpro.org';
$submission_date = '2016-03-06';

$sql = "INSERT INTO tutorialpro_tbl ".
        "(tutorialpro_title, tutorialpro_author, submission_date) ".
        "VALUES ".
        "('$tutorialpro_title', '$tutorialpro_author', '$submission_date')";

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('Could not insert data: ' . mysqli_error($conn));
}
echo "Data inserted successfully\n";
mysqli_close($conn);
?>

For inserting Chinese data, the statement mysqli_query($conn , "set names utf8"); is required.

Next, we can view the table data with the following statement:

Reading Table Data:

select * from tutorialpro_tbl;

Output result: