PHP MySQL insert data
Insert data into MySQL using MySQLi and PDO
After creating the database and table, we can add data to the table.
Related video tutorial recommendations: "mysql tutorial"http://www.php.cn/course/list/51.html
The following are some grammar rules:
SQL query statements in PHP must use quotation marks
In SQL query statements String values must be quoted
Numeric values do not need quotes
NULL values do not need quotes
INSERT INTO statement is typically used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)To learn more about SQL, check out our SQL tutorial.
In the previous chapters we have created the table "MyGuests", the table fields are: "id", "firstname", "lastname", "email" and "reg_date". Now, let's start filling the table with data.
 | Note: If the column is set to AUTO_INCREMENT (such as the "id" column) or TIMESTAMP (such as the "reg_date" column), we do not need to specify the value in the SQL query statement; MySQL will automatically add a value to the column. |
|---|
The following example adds a new record to the "MyGuests" table:
Example (MySQLi - Object Oriented)
<?php
$servername = "localhost";
$username = "username";
$password =
"password";
$dbname =
"myDB";
// 创建连接
$conn =
new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
$sql = "INSERT INTO
MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "新记录插入成功";
} else {
echo
"Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>Instance (MySQLi - Procedure Oriented)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// 创建连接
$conn = mysqli_connect($servername, $username, $password,
$dbname);
// 检测连接
if (!$conn) {
die("Connection
failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO
MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
echo "新记录插入成功";
} else {
echo "Error: "
. $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>Instance (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname",
$username, $password);
// 设置 PDO 错误模式，用于抛出异常
$conn->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO
MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
// 使用 exec() ，没有结果返回
$conn->exec($sql);
echo "新记录插入成功";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
