MySQL Insert Data

MySQL table uses INSERT INTO SQL statement to insert data.

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

Syntax

The following is a general method for inserting data into MySQL data tables INSERT INTO SQL syntax:

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

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

to insert data through the command prompt window

In the following, we will use SQL INSERT INTO statement into MySQL data table w3codebox_tbl to insert data

Online example

In the following example, we will insert data into w3codebox_tbl table inserted three rows:

root@host# mysql　-u root　-p password;
Enter password:*******
mysql> use w3codebox;
Database changed
mysql> INSERT INTO w3codebox_tbl　
　　　　-> (w3codebox_title, w3codebox_author, submission_date)
　　　　-> VALUES
　　　　-> ('Learn PHP', 'Basic Tutorial Website', NOW());
Query OK　1　rows affected　1　warnings (0.01　sec)
mysql> INSERT INTO w3codebox_tbl
　　　　-> (w3codebox_title, w3codebox_author, submission_date)
　　　　-> VALUES
　　　　-> ('Learn MySQL', 'Basic Tutorial Website', NOW());
Query OK　1　rows affected　1　warnings (0.01　sec)
mysql> INSERT INTO w3codebox_tbl
　　　　-> (w3codebox_title, w3codebox_author, submission_date)
　　　　-> VALUES
　　　　-> ('JAVA tutorial', 'oldtoolbag.com"2016-05-06;
Query OK　1　rows affected (0.00 sec)
mysql>

Note:using arrow marks -> is not part of an SQL statement, it simply indicates a new line. If an SQL statement is too long, we can create a new line by pressing the Enter key to write the SQL statement, and the command terminator of the SQL statement is a semicolon ;.

In the above example, we did not provide the w3codebox_id data because we have set this field to AUTO_INCREMENT (auto-increment) attribute when creating the table. Therefore, this field will automatically increment without the need for us to set it. In the example, NOW() is a MySQL function that returns the date and time.

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

select　*　from w3codebox_tbl;

Output Result:

Insert data using PHP script

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

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

Syntax

mysqli_query(connection, query, resultmode);

Online example

In the following example, the program receives three fields of user input and inserts the data into the table:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = "123456";　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn)
{
　　die('Connection failed: ' . mysqli_error($conn));
}
echo 'Connection successful<br　/">';
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8");
　
$w3codebox_title = "Learn Python"
$w3codebox_author = "oldtoolbag.com"
$submission_date = "2019-06-08";
　
$sql = "INSERT INTO w3codebox_tbl ".
　　　　　　　　"($w3codebox_title, $w3codebox_author, submission_date) ".
　　　　　　　　"VALUES ".
　　　　　　　　"('$w3codebox_title, '$w3codebox_author, '$submission_date')";
　
　
　
mysqli_select_db($conn, 'w3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval )
{
　　die('Unable to insert data: ' . mysqli_error($conn));
}
echo "Data insertion successful\n";
mysqli_close($conn);
?>

select　*　from w3codebox_tbl;