PDO Connect

Connect to MySQL Database Server

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

try {
	$conn = new PDO("mysql:host=$servername;dbname=$myDBPDO", $username, $password);
	// set the PDO error mode to exception
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connected successfully";
}
	catch(PDOException $e)
{
	echo "Connection failed: " . $e->getMessage();
}
?>

PDO Insert into Table

Insert Data into MySQL Database

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";

PDO Last Inserted ID

Get the ID of the last inserted Entry

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
// use exec() because no results are returned
$conn->exec($sql);

$last_id = $conn->lastInsertId();

echo "New record created successfully. Last inserted ID is: " . $last_id;
	

PDO Prepared Statements

Build up PDO Statement and prepare with fixed values for security

// prepare sql and bind parameters
$stmt = $conn->prepare("INSERT INTO MyGuests (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();
	

PDO Delete Entry of Table

$sql = "DELETE FROM MyGuests WHERE id=3";
	

PDO SQL Update

MISSING MORE THAN ONE ELEMENT UPDATE

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

// Prepare statement
$stmt = $conn->prepare($sql);

// execute the query
$stmt->execute();

// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";
	

PDO Select Data Limit Offset

$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";

//You could also use a shorter syntax to achieve the same result:
$sql = "SELECT * FROM Orders LIMIT 15, 10";

//Notice that the numbers are reversed when you use a comma.
	

PDO FETCH BOTH

Get Data of Request

$user = $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_BOTH);
/*
array (
  'id' => '104',
  0 => '104',
  'name' => 'John',
  1 => 'John',
  'sex' => 'male',
  2 => 'male',
)*/
	

PDO FETCH NUM

Get Data of Request

$user = $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_NUM);
/*
array (
  0 => '104',
  1 => 'John',
  2 => 'male',
)*/
	

PDO FETCH ASSOC

Get Data of Request

$user = $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_ASSOC);
/*
array (
  'id' => '104',
  'name' => 'John',
  'sex' => 'male',
)*/
	

PDO FETCH OBJ

Get Data of Request

$user = $pdo->query("SELECT * from users LIMIT 1")->fetch(PDO::FETCH_OBJ);
/*
stdClass::__set_state(array(
   'id' => '104',
   'name' => 'John',
   'sex' => 'male',
))*/