Mysqli 简明教程
MySQLi - Insert Query
要将数据插入 MySQL 表格,您需要使用 SQL INSERT INTO 命令。您可以使用 mysql> 提示符或使用任何像 PHP 这样的脚本将数据插入 MySQL 表格。
To insert data into a MySQL table, you would need to use the SQL INSERT INTO command. You can insert data into the MySQL table by using the mysql> prompt or by using any script like PHP.
Syntax
以下是 INSERT INTO 命令用于将数据插入 MySQL 表格的通用 SQL 语法 -
Here is a generic SQL syntax of INSERT INTO command to insert data into the MySQL table −
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
要插入字符串数据类型,需要将所有值放入双引号或单引号中。例如 "value" 。
To insert string data types, it is required to keep all the values into double or single quotes. For example "value".
Inserting Data from the Command Prompt
要从命令提示符插入数据,我们将使用 SQL INSERT INTO 命令将数据插入 MySQL 表格 tutorials_tbl。
To insert data from the command prompt, we will use SQL INSERT INTO command to insert data into MySQL table tutorials_tbl.
Example
以下示例将在 tutorials_tbl 表格中创建 3 条记录 -
The following example will create 3 records into tutorials_tbl table −
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> INSERT INTO tutorials_tbl
→(tutorial_title, tutorial_author, submission_date)
→VALUES
→("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
→(tutorial_title, tutorial_author, submission_date)
→VALUES
→("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tutorials_tbl
→(tutorial_title, tutorial_author, submission_date)
→VALUES
→("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>
NOTE - 请注意所有箭头符号 (→) 不是 SQL 命令的一部分。它们表示一个新行,并且在命令的每行末尾不加分号时,它们会由 MySQL 提示符自动创建。
NOTE − Please note that all the arrow signs (→) are not a part of the SQL command. They are indicating a new line and they are created automatically by the MySQL prompt while pressing the enter key without giving a semicolon at the end of each line of the command.
在上述示例中,我们没有提供 tutorial_id,因为在创建表格时,我们为该字段给出了 AUTO_INCREMENT 选项。因此,MySQL 会自动插入这些 ID。此处, NOW() 是一个 MySQL 函数,它会返回当前日期和时间。
In the above example, we have not provided a tutorial_id because at the time of table creation, we had given AUTO_INCREMENT option for this field. So MySQL takes care of inserting these IDs automatically. Here, NOW() is a MySQL function, which returns the current date and time.
Inserting Data Using a PHP Script
PHP 使用 mysqli query() 或 mysql_query() 函数向 MySQL 表中插入记录。此函数接受两个参数,在成功时返回 TRUE,在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to insert a record into a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli→query($sql,$resultmode)
Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to insert record into a table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
此示例将从用户那里获取三个参数,并将它们插入 MySQL 表 − −
This example will take three parameters from the user and will insert them into the MySQL table − −
将以下示例复制粘贴为 mysql_example.php:
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['add'])) {
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli→connect_errno ) {
printf("Connect failed: %s<br />", $mysqli→connect_error);
exit();
}
printf('Connected successfully.<br />');
if(! get_magic_quotes_gpc() ) {
$tutorial_title = addslashes ($_POST['tutorial_title']);
$tutorial_author = addslashes ($_POST['tutorial_author']);
} else {
$tutorial_title = $_POST['tutorial_title'];
$tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO tutorials_tbl ".
"(tutorial_title,tutorial_author, submission_date) "."VALUES ".
"('$tutorial_title','$tutorial_author','$submission_date')";
if ($mysqli→query($sql)) {
printf("Record inserted successfully.<br />");
}
if ($mysqli→errno) {
printf("Could not insert record into table: %s<br />", $mysqli→error);
}
$mysqli→close();
} else {
?>
<form method = "post" action = "<?php $_PHP_SELF ?>">
<table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
<tr>
<td width = "250">Tutorial Title</td>
<td><input name = "tutorial_title" type = "text" id = "tutorial_title"></td>
</tr>
<tr>
<td width = "250">Tutorial Author</td>
<td><input name = "tutorial_author" type = "text" id = "tutorial_author"></td>
</tr>
<tr>
<td width = "250">Submission Date [ yyyy-mm-dd ]</td>
<td><input name = "submission_date" type = "text" id = "submission_date"></td>
</tr>
<tr>
<td width = "250"> </td>
<td></td>
</tr>
<tr>
<td width = "250"> </td>
<td><input name = "add" type = "submit" id = "add" value = "Add Tutorial"></td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
Output
访问部署在 apache Web 服务器上的 mysql_example.php,输入详细信息,并在提交表单时验证输出。
Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.
Record inserted successfully.
在执行数据插入时,最好使用函数 get_magic_quotes_gpc() 来检查当前 magic quote 配置是否已设置。如果此函数返回 false,则使用函数 addslashes() 在引号前添加反斜杠。
While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.
您可以进行许多验证,以检查输入的数据是否正确,并可以采取适当的操作。
You can put many validations around to check if the entered data is correct or not and can take the appropriate action.