PHP MySQL inserts data
May 11, 2021 PHP
Table of contents
PHP MySQL inserts data
Insert data into MySQL using MySQLi and PDO
After we have created the database and table, we can add data to the table.
Here are some syntax rules:
-
SQL query statements in PHP must use quotation marks
-
String values in SQL query statements must be quoted
-
The value of the value does not require quotation marks
-
NULL values do not require quotation marks
INSERT INTO statements are typically used to add new records to mySQL tables:
VALUES (value1, value2, value3,...)
To learn more about SQL, check out our SQL tutorial.
In the previous chapters we have created tables "MyGuests" with the following fields: "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 a value in the SQL query statement; |
|---|
The following instance adds a new record to the MyGuests table:
Instances (MySQLi - Object Oriented)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);
Detect the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Instance (mysqli - the oriented process)
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create a connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Test connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
if (mysqli_query($conn, $sql)) {
echo "New record created 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 the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]')";
// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
}
catch(PDOException $e)
{
echo $sql . " <br>" . $ e->getMessage();
}
$conn = null;
?>
In fact, you can also insert multiple data into PHP's MySQL database, which is mentioned in the following section.