INSERT INTO SQL statement to INSERT data INTO MySQL table.

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

grammar

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

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

If data is in character type, single or double quotation marks (for example, “value”) must be used.

Insert data through a command prompt window

INSERT INTO runoob_tbl (runoob_tbl)

The instance

In the following example, we will insert three pieces of data into the runoob_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 -> (" PHP", NOW()); Query OK, 1 rows affected, 1 warnings (0.01sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, Submission_date) -> VALUES -> (" MySQL", NOW()); Query OK, 1 rows affected, 1 warnings (0.01sec) mysql> INSERT INTO runoob_tbl -> (runoob_title, runoob_author, Submission_date) -> VALUES -> ("JAVA tutorial ", "RUNOOB.COM", '2016-05-06'); Query OK, 1 rows affected (0.00 SEC) mysql>Copy the code

Note: Using the arrow mark -> is not part of the SQL statement. It simply represents a new line. If an SQL statement is too long, we can use the return key to create a new line to write the SQL statement. .

In the above example, we did not provide runoob_id because it was set to AUTO_INCREMENT when we created the table. So, the field is automatically incremented without us having to set it. In the example, NOW() is a MySQL function that returns the date and time.

We can then view the table data with the following statement:

Read data table:

select * from runoob_tbl;

Output result:

 

Insert data using PHP scripts

You can use PHP’s mysqli_query() function to execute SQL INSERT INTO to INSERT data.

This function takes two arguments and returns TRUE on success and FALSE otherwise.

grammar

mysqli_query(connection,query,resultmode);
Copy the code
parameter describe
connection A necessity. Specify the MySQL connection to use.
query Required, specifies the 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 (the default)

The instance

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

Add data

<? php $dbhost = ‘localhost’; $dbuser = ‘root’; $dbpass = ‘123456’; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(‘ connection failed: ‘. Mysqli_error ($conn)); } echo ‘connect successfully <br />’ Mysqli_query ($conn, “set names utf8”); $runoob_title = ‘learn 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(‘ cannot insert data: ‘.mysqli_error ($conn)); } echo “insert data successfully \n”; mysqli_close($conn); ? >

Mysqli_query ($conn, “set names utf8”); Statements.

We can then view the table data with the following statement:

Read data table:

select * from runoob_tbl;

Output result: