MySQL insert data

MySQL Insert data

Use the INSERT INTO SQL statement to insert data into the MySQL table.

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

Syntax

The following is the common 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 Quotes or double quotes, such as "value".

Insert data through the command prompt window

Below we will use the SQL INSERT INTO statement to insert data into the MySQL data table php_tbl

Example

In the following example we will insert three pieces of data into the php_tbl table:

root@host# mysql -u root -p password;Enter password:*******mysql> useDEMO;Database changed
mysql> INSERT INTO user 
    -> (user_title, user_author, submission_date)
    -> VALUES    -> ("学习 PHP", "PHP中文网", NOW());Query OK, 1 rows affected, 1 warnings (0.01 sec)
    mysql> INSERT INTO user    -> (user_title, user_author, submission_date)
    -> VALUES    -> ("学习 MySQL", "PHP中文网", NOW());Query OK, 1 rows affected, 1 warnings (0.01 sec)
    mysql> INSERT INTO user    -> (user_title, user_author, submission_date)
    -> VALUES    -> ("JAVA 教程", "PHP中文网", '2016-05-06');Query OK, 1 rows affected (0.00 sec)mysql>

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

In the above example, we did not provide user_id data because we have set this field to the AUTO_INCREMENT (automatic increase) attribute when we created the table. Therefore, this field will automatically increment without us needing 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 through the following statement:

Read the data table:

select * from user;

Use PHP Script to insert data

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

This function has two parameters and returns TRUE when executed successfully, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

Parameter	Description
connection	Required. Specifies the MySQL connection to use.
query	Required, specify query string
resultmode	Optional. a constant. Can be any of the following values: MYSQLI_USE_RESULT (use this if you need to retrieve large amounts of data) MYSQLI_STORE_RESULT (default)

Example

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

<?php
header("Content-Type: text/html;charset=utf-8");
$dbhost = 'localhost'; // mysql server host address
$dbuser = 'root'; // mysql user Name
$dbpass = 'root'; // 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");

$runoob_title = 'Learn Python';
$runoob_author = 'PHP Chinese website';
$submission_date = '2016-03-06' ;

$sql = "INSERT INTO user ".
"(user_title,user_author, submission_date) ".
"VALUES ".
"('$user_title','$user_author' ,'$submission_date')";

mysqli_select_db( $conn, 'DEMO' );
$retval = mysqli_query( $conn, $sql );
if( ! $retval )
{
die('Unable to insert data: ' . mysqli_error($conn));
}
echo "Data insertion successful\n";
mysqli_close($conn );
?>

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

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

select * from user;

未标题-1.jpg

Related course recommendations: Insert data

MySQL insert data

php.cn