CS479/579 - Web Programming II

The PHP mysqli interface:

The PHP MySQL Improved interface is a object oriented interface to connect to a MySQL or MariaDB server and issue SQL commands to send and receive data.

Connecting to the database:

// Connects to the database, $myconn is the resource handle for the database connection:
$myconn = new mysqli($db_host, $db_user, $db_password, $db);
if ($myconn->connect_error) {
  die ("Failed to connect to database (" . $myconn->connect_errono . "): " .



$db_host localhost or the host name of the MySQL server we want to connec to.
$db_user MySQL username
$db_password MySQL password.
$db Name of the database to use.
$myconn A connection resource object.

Closing the connection:


  • Closes the connection to the database. Probably not necessary for short-lived scripts.

Performing a Query:

$res = $myconn->query($query) or

die ("Error querying database: ($query) " . $myconn->error);


$myconn The connection resource object.
$query The query string to be performed.
$res The result of the query.
$myconn->error Error string of the last mysqli call

query() is only suitable for simple queries that cannot contain user supplied data of any type, all other SQL queries should be done using prepared statements (see below) for best security practices.


  • Frees the result set from the query. << If making a number of queries, this is probably necessary. If you are making just one and exiting the PHP script you may consider omitting it. >>

$rows = $res->num_rows;

  • Number of rows, if any, returned by the query. Not valid for unbuffered results until all rows have been read.

$row = $res->fetch_row();

  • Returns the next row provided by the query as a enumerated array stored in $row. i.e. $row[0] is the first column, $row[1] the second and so on. $row is set to NULL when there are no more rows.


$myconn = new mysqli("localhost", "cs47900", "1234", "cs47900");
$res = $myconn->query("SELECT * FROM people WHERE id < 20");
while($row = $res->fetch_row()) {
  // Do something with $row[int]

$row = $res->fetch_assoc();

  • Fetches a row as an associative array (column names as keys => values), otherwise same as fetch_row();

Escaping special characters in a string for use in a MySQL statement:

$safestr = $myconn->real_escape_string($str);

Prepared MySQLi statements:

Creates a SQL statement template that is sent to the server. In the template some values (called parameters) are left undefined, specified as '?' in the statement. Example:

INSERT INT table (x, y, z) VALUES (?, ?, ?)

Note: The ?'s are only allowed for values or for comparisons in a WHERE clause (but not for both sides of a comparison), they are not allowed for table or column names.

The database compiles the prepared statement and can query optimize it but does not immediately execute it, making it not unlike a function call. Subsequent execution of the prepared statement can be made more than once and only requires the missing values be transferred to the server saving on bandwidth.

Useful as a protection against SQL injections, particularly second level injections, because all parameters will be properly escaped automatically and we no longer need to escape them ourselves.


// After MySQLi connection has been made:

// prepare the statement:
$stmt = $myconn->prepare("INSERT INTO people (fname, lname, email)
  VALUES (?, ?, ?)");
// Bind the parameters:
$stmt->bind_param("sss", $fname, $lname, $email);

$stmt The prepared statement object.
"sss" Specifies that the parameters that follow are all strings. Can be one of:
s = String, i = Integer, d = Double, b = BLOB
$fname, $lname, $email These variables are now "bound" so whatever their values are will be passed to the prepared statement when the execute() method is called. They are like the parameters to our function call.

// (includes code from above)
// Set the parameter values:
$fname = "Steve"; $lname = "Baker"; $email = "Steve.Baker@indstate.edu";
// Execute the function call:

// Set new parameter values:
$fname = "Bob"; $lname = "Smith"; $email = "Bob.Smith@indstate.edu";
// Execute the statement a second time:

// Close the statement, this is recommended:

Getting results from prepared statements:

Normally results are bound to variables in a manner similar to the way inputs are bound.

// Binds variables to the output results:
// Fetch the values (stored in the bound variables above.):


// Gives us $myconn (the data-base connection object):
include_once "config.php";

$uid = null;

if (!($stmt = $myconn->prepare("SELECT username,gecos FROM users WHERE uid=?"))) {
  echo "Prepare failed: (" . $myconn->errno . ") " . $myconn->error;
if (!$stmt->bind_param("i", $uid)) {
  echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;

$username = null; $gecos = null;
$stmt->bind_result($username, $gecos);

for($uid = 1000; $uid < 3000; $uid++) {
  if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
  printf("%d: %-6s (%8s %s)\n", $uid, $username, $gecos);


Result sets from prepared statements:

We can get results in the normal fashion w/o binding to output variables by using the get_result() method, which returns a result set which you then use the normal fetch*() functions on:

$res = $stmt->get_result(); // Returns a result set
$row = $res->fetch_assoc();