PHP has excellent MySQL support, having integrated the API since PHP version 2! In this course we will be using the Improved MySQL extension for PHP, mysqli, which allows for an object oriented approach using prepared statements, all is a relatively simple interface.
The code to connect to the database is rather straight forward:
$db = new mysqli('hostname',
'username',
'password',
'database_name');
To disconnect, when all queries are completed, usually at the end of
your PHP script.
$db->close();
Please understand that this method is not what is normally used, please review the next section!
Since your username and password (for the database) is needed for the connection, standard convention is to create a small .php script which handles the connection, and is readable by the webserver. We could just embed the $db line above, but a better way is to extend the class, the example below is in a file called mysql.inc.php:
<?php
// mysql.inc.php - This file will be used to establish the database connection.
class myConnectDB extends mysqli{
public function __construct($hostname="localhost",
$user="m23xxxx",
$password="MYPASSWORD",
$dbname="m23xxxx"){
parent::__construct($hostname, $user, $password, $dbname);
}
}
?>
and then connect to the database via:
require_once('mysql.inc.php'); # MySQL Connection Library
$db = new myConnectDB(); # Connect to MySQL
We can check to see if we connected successfully via
if (mysqli_connect_errno()) {
echo "<h5>ERROR: " . mysqli_connect_errno() . ": " . mysqli_connect_error() . " </h5><br>";
}
There are a few basic steps needed when performing SQL queries via PHP and MySQL, in the examples below simple queries will be presented on the left, while dynamic queries are shown on the right.
$course=$_GET["course"];
$query = "SELECT alpha, Course
FROM grades
HERE Course='".$course."'";
$course=$_GET["course"];
$query = "SELECT alpha, Course
FROM grades
WHERE Course=?";
$stmt = $db->stmt_init();
$stmt->prepare($query);
Character | Description |
---|---|
i | Corresponding variable has type integer |
d | Corresponding variable has type double |
s | Corresponding variable has type string |
b | Corresponding variable is a blob and will be sent in packets |
// This step not required for simple query!
$stmt->bind_param('s', $course);
// Multiple variables? List data types in order up front!
$stmt->bind_param('ssi', $course, $name, $alpha);
$success = $stmt->execute();
if (!$success || $db->affected_rows == 0) {
echo "<h5>ERROR: " . $db->error . " for query *$query*</h5><hr>";
}
$stmt->store_result(); // Needed if we want to be able to retrieve specific data
// or it we want to run new queries while walking through
// the results of this one.
$num_rows = $stmt->num_rows; // Retrieve the number of rows returned by the query
$insert_id = $stmt->insert_id; // Retrieve the auto_increment value
Another optional capability is to retrieve the column names and store them into an array:
$meta = $stmt->result_metadata();
$columns = array();
while ($field = $meta->fetch_field()) {
$columns[] = $field->name; // Append the column name to the array
}
$stmt->bind_result($alpha, $course);
while ($stmt->fetch()) {
echo "$alpha $course <br>";
}
$stmt->close();