How to insert data into MySQL database table? php insert data statement instance to MySQL

MySQL databaseHow to insert data into the table? php toMySQLInsert data statement example

MySQL insert data

MySQL table use 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 to insert data.

grammar

The following are common 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, you must use single quotes or double quotes, such as: "value".

Insert data via command prompt window

Below we will use SQL INSERT INTO The statement inserts data into the MySQL data table chenweiliang_tbl

Examples

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

root@host# mysql -u root -p password;
Enter password:*******
mysql> use chenweiliang;
Database changed
mysql> INSERT INTO chenweiliang_tbl 
    -> (chenweiliang_title, chenweiliang_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO chenweiliang_tbl
    -> (chenweiliang_title, chenweiliang_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO chenweiliang_tbl
    -> (chenweiliang_title, chenweiliang_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "chenweiliang.com", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>

note: Mark with arrows -> It is not part of the SQL statement, it just represents a new line. If a SQL statement is too long, we can create a new line to write the SQL statement by using the Enter key. The command terminator of the SQL statement is a semicolon ;.

In the above example, we did not provide the data of chenweiliang_id, because this field has been set as the AUTO_INCREMENT attribute when we created the table.Therefore, the field will be automatically incremented without us needing to set it.In the example NOW() is a MySQL function that returns a date and time.

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

Insert data using PHP script

You can use PHP's mysqli_query() function to do SQL INSERT INTOcommand to insert data.

The function has two parameters and returns TRUE if the execution is successful, otherwise it returns FALSE.

grammar

mysqli_query(connection,query,resultmode);

Examples

The program in the following example receives three fields of data entered by the user and inserts them into the data table:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功
';
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
$chenweiliang_title = '学习 Python';
$chenweiliang_author = 'chenweiliang.com';
$submission_date = '2016-03-06';
 
$sql = "INSERT INTO chenweiliang_tbl ".
 "(chenweiliang_title,chenweiliang_author, submission_date) ".
 "VALUES ".
 "('$chenweiliang_title','$chenweiliang_author','$submission_date')";
 
 
 
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
 die('无法插入数据: ' . mysqli_error($conn));
}
echo "数据插入成功\n";
mysqli_close($conn);
?>

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