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)

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 . "
" . $conn->error; } $conn->close(); ?>

Instance (MySQLi - Procedure Oriented)

" . mysqli_error($conn); } mysqli_close($conn); ?>

Instance (PDO)

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 . "
" . $e->getMessage(); } $conn = null; ?>