WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

PHP Insert Data Into MySQL database


Insert data into MySQL using MySQLi and PDO

After creating the database and table, we can add data to the table.

Here are some syntax rules:

  • SQL query statements in PHP must use quotes
  • String values must be quoted in SQL query statements
  • No quotes are needed for numerical values
  • Quotation marks are not required for NULL values

The INSERT INTO statement is typically used to add new records to a MySQL table:

 INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...) 

Learn more about SQL, check out our SQL tutorial .

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 Note: If the column is set to AUTO_INCREMENT (such as the "id" column) or TIMESTAMP (Such as the "reg_date" column), we don't need to specify a value in the SQL query; MySQL automatically adds a value to the column.

The following example adds a new record to the "MyGuests" table:

Instance (MySQLi-Object Oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Detect 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(); ?>


Examples (MySQLi - Process-oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Detect 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); ?>


Examples (PDO)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set PDO error mode for throwing 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 returned $conn->exec($sql); echo "New record inserted successfully"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>