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:

<?php
// 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 . "): " .
     $myconn->connect_error);
}

?>

Where:

Closing the connection:

$myconn->close();

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

Where:

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.

$res->free();
$res->close();

• 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.

Example:

<?php
$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.

Example:

<?php
// 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);
?>

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

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

// Close the statement, this is recommended:
$stmt->close();
?>

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:
$stmt->bind_result($var1[,$var2,...]);
// Fetch the values (stored in the bound variables above.):
$stmt->fetch();

Example:

<?php
// 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;
  }
  $stmt->fetch();
  printf("%d: %-6s (%8s %s)\n", $uid, $username, $gecos);
}

$stmt->close();
?>

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