PHP MySQL Inserting Records Reference

First up there are three examples for inserting records into a MySQL database, one for each of the three following techniques:

• Using mysqli in a procedural manner.
• Using mysqli in an object-oriented fashion.
• Using PDO.

After that there is an example of the same three approaches, but with the addition of using prepared statements for improved security and efficiency.

MySQLi Procedural

<?php

$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 mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

?>

MySQLi Object-oriented

<?php

$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 mytable (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();

?>

PDO

<?php

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydb";

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 mytable (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";
    // 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;

?>

MySQLi Procedural with Prepared Statements

<?php

// Create connection
$conn = mysqli_connect($servername, $username, $password, $db);

// Check connection
if($conn === false){
	die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Prepare insert statement
$sql = "INSERT INTO mytable (firstname, lastname, email)
VALUES (?, ?, ?)";

$stmt = mysqli_prepare($conn, $sql);

// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sss", $firstname, $lastname, $email);		

// set parameters and execute
$firstname = "Ron";
$lastname = "Peters";
$email = "ronpeters@gmail.com";
mysqli_stmt_execute($stmt);

echo "New records created successfully";


// Close statement
mysqli_stmt_close($stmt);

// Close connection
mysqli_close($conn);

?>

MySQLi Object-oriented with Prepared Statements

<?php

$servername = "localhost";
$username = "root";
$password = "";
$db = "mydb";

// Create connection
$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
	die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO mytable (firstname, lastname, email) 
VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "Tom";
$lastname = "Jones";
$email = "tomjones@gmil.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();

?>

PDO with Prepared Statements

<?php 

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";

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);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO mytable (firstname, lastname, email)
    VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    // insert a row
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();

    // insert another row
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();

    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
$conn = null;

?>