Description
The chapter PHP Insert Data Into MySQL gives the description of how to insert the data into Database. Once the database and the tables are created, the values can be inserted.
Data is added to MySQL tables with the help of
The records are inserted as shown in below syntax:
[sql]INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)[/sql]
Below a simple example to insert a record into Student table.
SQL INSERT statement via PHP function mysql_query.
Some rules to be followed while inserting the values into database.
Note:
- The query of SQL must be enclosed inside PHP quotes.
- If any string values persists in the SQL query, it should be given in quotes.
- Quotes should not be given to numeric values.
- NULL should not be in quotes.
Note:
There is no need to specify the value for columns with AUTO_INCREMENT (eg: "id" column) or TIMESTAMP (eg: "birth_date" column), MySQL automatically adds the value. Examples
(MySQLi Object-oriented)
[php]
<!DOCTYPE html>
<html>
<body>
<?php
$HostName = "localhost";
$UserName = "root";
$Password = "password";
$dbname = "accounts";
// Create connection
$connection = new mysqli($HostName, $UserName, $Password, $dbname);
// Check connection
if ($connection->connect_error)
{
die("Connection failed: " . $connection->connect_error);
}
//inserting record
$sql = "INSERT INTO users2 (Username, Password, First, Last)
VALUES ('Adam', 'password', 'Adam', 'Gilchrist')";
if ($connection->query($sql) === TRUE)
{
echo "New record created successfully";
}
else
{
echo "Error: " . $sql . " <br> " . $connection->error;
}
$connection->close();
?>
</body>
</html>
[/php]
The output looks like below in phpmyadmin.
MySQLi Procedural
[php]
<html>
<body>
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "Student";
// Create connection
$connection = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
//inserting record
$sql = "INSERT INTO users2 (Username, Password, First, Last)
VALUES ('Adam', 'password', 'Adam', 'Gilchrist')";
if (mysqli_query($connection, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}
mysqli_close($connection);
?>
</body>
</html>
[/php]
Key Points
PHP Insert Data Into MySQL illustrates the following important points.
- Data can be inserted into MySQL tables by executing SQL INSERT statement.
- AUTO_INCREMENT or TIMESTAMP automatically updates value using MySQL.