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";
// 創(chuàng)建連接
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測(cè)連接
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";
// 創(chuàng)建連接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 檢測(cè)連接
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);
    // 設(shè)置 PDO 錯(cuò)誤模式，用于拋出異常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";
    // 使用 exec() ，沒有結(jié)果返回 
    $conn->exec($sql);
    echo "新記錄插入成功";
}
catch(PDOException $e)
{
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>