MySQL insert data

The MySQL table uses the INSERT INTO SQL statement to insert data.

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

grammar

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

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

If the data is character type, you must use single quotation marks or double quotation marks, such as "value".

Insert data through the command prompt window

Next, we will use the SQL INSERT INTO statement to add runoob to the MySQL data table_ TBL insert data

example

In the following example, we will apply runoob_ Insert three pieces of data into TBL table:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("study PHP", "course", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("study MySQL", "course", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA course", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

Note: the arrow mark - > is not a part of the SQL statement. It only represents a new line. If an SQL statement is too long, we can create a new line by pressing enter to write the SQL statement. The command end character of the SQL statement is a semicolon;.

In the above example, we do not provide runoob_id, because this field has been set to auto when we created the table_ The increment attribute. Therefore, this field will be incremented automatically without our setting. In the instance, NOW() is a MySQL function that returns date and time.

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

Read data table:

select * from runoob_tbl;

Output result:

Match lineup

Insert data using PHP script

You can use mysqli in PHP_ Query() function to execute SQL INSERT INTO command to insert data.

This function has two parameters. It returns TRUE when the execution is successful, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

example

In the following example, the program receives three field data entered by the user and inserts them into the data table:

Add data

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';            // mysql user name
$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 the code to prevent Chinese random code
mysqli_query($conn , "set names utf8");
 
$runoob_title = 'study Python';
$runoob_author = 'RUNOOB.COM';
$submission_date = '2016-03-06';
 
$sql = "INSERT INTO runoob_tbl ".
        "(runoob_title,runoob_author, submission_date) ".
        "VALUES ".
        "('$runoob_title','$runoob_author','$submission_date')";
 
 
 
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
  die('Unable to insert data: ' . mysqli_error($conn));
}
echo "Data insertion succeeded\n";
mysqli_close($conn);
?>

For data insertion with Chinese, you need to add mysqli_query($conn , "set names utf8"); Statement.

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

Read data table:

select * from runoob_tbl;

Output result: