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.

The following are some grammar rules:

1. SQL query statements in PHP must use quotation marks

2. String values ​​in SQL query statements must be quoted

3. Numeric values ​​do not require quotes

4. NULL values ​​do not require quotes

The INSERT INTO statement is usually used to add new records to a MySQL table:

INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)

You can also add new records to the MySQL table in batches:

INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,...),(value1, value2,...),(value1, value2,...) ;

To learn more about SQL, check out our SQL tutorials.

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;
?>