PHP MySQL Insert Data
Using MySQLi and PDO to insert data into MySQL
After creating the database and table, we can add data to the table.
The following are some syntax rules:
- SQL query statements in PHP must use quotes
- String values in SQL query statements must be quoted
- Numeric values do not need quotes
- NULL values do not need quotes
The INSERT INTO statement is generally 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, please check ourSQL Tutorial。
In the previous chapters, we created the table "MyGuests" with fields: "id", "firstname", "lastname", "email", and "reg_date". Now, let's start filling the table with data.
 |
Note:If a 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 a value in the SQL query statement; MySQL will automatically add a value for that column. |
|---|
The following example adds a new record to the "MyGuests" table:
Example (MySQLi - Object-oriented)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
//Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
//Check connection
if ($conn->connect_error) {
die("Connection failed:" . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
Example (MySQLi - Procedural)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
//Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
//Check connection
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 "New record inserted successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
Example (PDO)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
//Set PDO error mode to throw exceptions
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
//Use exec(), no results are returned
$conn->exec($sql);
echo "New record inserted successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;