PHP MySQL Insert Data
After a database and a table have been created, you can start adding data to them. In SQL, we use the INSERT INTO statement to add new records.
1. Syntax Rules
- The SQL query must be quoted in PHP.
- String values inside the SQL query must be quoted (e.g.,
'John').
- Numeric values should not be quoted.
- The
NULL value should not be quoted.
2. Insert Data using MySQLi (Object-Oriented)
In this example, we insert a new record into the "MyGuests" table we created earlier.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);
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 created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
3. Insert Data using PDO
With PDO, we use exec() for insert statements because they do not return a result set.
<?php
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com')";
$conn->exec($sql);
echo "New record created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
4. Important Note on ID Columns
If a column is set to AUTO_INCREMENT (like our id column), you do not need to include it in the SQL query. MySQL will automatically generate the next number for you.
Security Alert: The examples above use static data. In a real application, you should never insert data directly from a user (like $_POST variables) into a query. This makes you vulnerable to SQL Injection. You should use Prepared Statements instead.