PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

<?php
$servername = "localhost"; $database = "databasename"; $username = "username"; $password = "password";
try {
	$conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connected successfully";
}
catch(PDOException $e) {echo "Connection failed: " . $e->getMessage();}
?>

Notice that PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

$conn = null;

Example (MySQLi Object-oriented)

$servername = "localhost";
$username = "username";
$password = "password";
$conn = new mysqli($servername, $username, $password); //Create connection
if($conn->connect_error){ die("Connection failed: ".$conn->connect_error);} //Check connection
// Create database myDB
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {echo "Database created successfully";
} else { echo "Error creating database: " . $conn->error;}
$conn->close();

Example (PDO)

$servername = "localhost";
$username = "username";
$password = "password";
try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE myDBPDO";
$conn->exec($sql); // use exec() because no results are returned
echo "Database created successfully";
}
catch(PDOException $e) {echo $sql . "<br>" . $e->getMessage();}	
$conn = null;

try { ......
	$sql = "CREATE TABLE MyGuests (
		id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
		firstname VARCHAR(30) NOT NULL,
		lastname VARCHAR(30) NOT NULL,
		email VARCHAR(50),
		reg_date TIMESTAMP
		)";
	$conn->exec($sql);
	echo "Table MyGuests created successfully";
 }

Here are some syntax rules to follow:

• The SQL query must be quoted in PHP
• String values inside the SQL query must be quoted
• Numeric values must not be quoted
• The word NULL must not be quoted

Note: If a column is AUTO_INCREMENT (like the "id" column) or TIMESTAMP (like the "reg_date" column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

try { ......
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
$conn->exec($sql);
echo "New record created successfully";
}

If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.

try { ......
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')";
$conn->exec($sql);
$last_id = $conn->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $last_id;
}

Multiple SQL statements must be executed with the mysqli_multi_query() function.

The PDO way is a little bit different:

try { ......
$conn->beginTransaction();  // begin the transaction
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Mary', 'Moe', 'mary@example.com')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Julie', 'Dey', 'julie@example.com')");
$conn->commit();  // commit the transaction
echo "New records created successfully";
}

Prepared Statements and Bound Parameters

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

Prepared statements basically work like this:

1. Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
2. The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
3. Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

Compared to executing SQL statements directly, prepared statements have two main advantages:

• Prepared statements reduces parsing time as the preparation on the query is done only once (although the statement is executed multiple times)
• Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query
• Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

The following example uses prepared statements and bound parameters in PDO:

try { ......
  /* 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 rows */
$firstname = "John"; $lastname = "Doe"; $email = "john@e.com"; $stmt->execute();
$firstname = "Mary"; $lastname = "Moe"; $email = "mary@e.com"; $stmt->execute();
$firstname = "Julie"; $lastname = "Dooley"; $email = "julie@e.com"; $stmt->execute();
echo "New records created successfully";
}
方法2：
"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"
$stmt->bind_param("sss", $firstname, $lastname, $email);
//参数解释：i - integer  d - double  s - string  b - BLOB

Select Data With PDO (+ Prepared Statements)

The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:

echo "<table style='border: solid 1px black;'>";
echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";
class TableRows extends RecursiveIteratorIterator {
	function __construct($it) { parent::__construct($it, self::LEAVES_ONLY); }
	function current() {
		return "<td style='width:150px;border:1px solid black;'>" . 
		parent::current() . "</td>"; }
	function beginChildren() { echo "<tr>";}
	function endChildren() { echo "</tr>" . "\n";} 
}
try { ......
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); 
$stmt->execute();	
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);  // set the resulting array to associative 
foreach (new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v){ echo $v;}
} ......
echo "</table>";

try { ......
$sql = "DELETE FROM MyGuests WHERE id=3";
$conn->exec($sql);
echo "Record deleted successfully";
} ......

try { ......
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
$stmt = $conn->prepare($sql);  // Prepare statement
$stmt->execute();
echo $stmt->rowCount() . " records UPDATED successfully";
} ......

The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.

$sql = "SELECT * FROM Orders LIMIT 30";
$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";  // select records 16-25 (inclusive)
$sql = "SELECT * FROM Orders LIMIT 15, 10";  // numbers are reversed when you use a comma.