References used for these notes include:

1. http://www.w3schools.com

Introduction

You can access databases using 2 different PHP packages:

1. MySQLi extension (the "i" stands for improved)
2. PDO (PHP Data Objects)

Prior to PHP 5, PHP used the MySQL extension package (note the lack of the "i"), but this package was deprecated in 2012. You may still see commands such as "mysql_query(...)" rather than "mysqli_query(...)". Such commands are using the old extension package. If you are maintaining such code, you should probably consider converting the commands to the newer mysqli package.

The MySQLi package has both an object-oriented and a procedural API. In this course we will only consider the procedural API, except for SQL prepared statements. The PDO package allows PHP to connect to databases other than MySQL, but the syntax is more complicated. In this course we will only consider the MySQLi package.

Establishing Connections to the MySQL Server

• sql_link mysqli_connect(servername, username, password, databaseName): establishes a connection within php to a mysql server.
  1. Returns a MySql link identifier on success, or false on failure: A link identifier can be subsequently used as a parameter to many mysql functions if you establish links to multiple sql servers. However in general you are only going to establish a single connection and php's mysql functions assume that they should execute their statements on the last link established if no link is provided. This "cheat" sheet does not show links as a parameter to any functions

  2. Example: $conn = mysqli_connect("dbs.cs.utk.edu", "cs594bvz", "cs594bvz", "cs465_fa14_bvz");

• bool mysqli_close($conn): closes the connection to the mysql server

Executing Queries

• resource mysqli_query($conn, string query)
  1. For this course, query should be one of SELECT, INSERT, UPDATE, or DELETE, or CREATE TABLE

  2. The resource can be passed to a variety of other mysql functions to extract information

  3. Returns false if the query fails for any reason

Prepared Sql Statements

• Prepared sql statements are query templates that take parameters and which may be repeatedly executed with high frequency. In this course I want you to use prepared statements for your assignments. Here is a procedural example taken from the PHP manual:

  $city = "Amersfoort";
  
  /* create a prepared statement */
  if ($stmt = mysqli_prepare($link, "SELECT District FROM City WHERE Name=?")) {
  
      /* bind parameters for markers */
      mysqli_stmt_bind_param($stmt, "s", $city);
  
      /* execute query */
      mysqli_stmt_execute($stmt);
  
      /* bind result variables */
      mysqli_stmt_bind_result($stmt, $district);
  
      /* fetch value */
      mysqli_stmt_fetch($stmt);
  
      printf("%s is in district %s\n", $city, $district);
  
      /* close statement */
      mysqli_stmt_close($stmt);
  }
  
  /* close connection */
  mysqli_close($link);
        

  Things to note:
  • The ?'s in the prepare statement are the parameters to the query
  • The prepare statement compiles and optimizes the query
  • The bind_param statement binds the ?'s to variables. The arguments are passed by reference. The second argument is a string representing the types of the parameters. Valid types are:
    1. s: string
    2. i: integer
    3. d: double
    4. b: blob
  • bind_result binds variables to each of the columns returned by the query.
  • fetch returns the next row of the result
• Advantages of prepared statements include:
  1. Parses and optimizes once, rather than each time the query is executed.
  2. Minimizes network traffic because only parameters, rather than the entire query string, must be sent with each request
  3. Helps prevent SQL injection attacks because parameter values, are transmitted separately and it is clear where parameters begin and end in the query. The backend automatically escapes any quoted characters and hence the programmer does not need to escape the characters.
• See Prepared Sql Statements for an object-oriented way to do prepared statements.

Extracting Information from Prepared Statements

1. mysqli_stmt_num_rows($stmt): returns the number of rows returned by a prepared statement that executes a SELECT statement. $stmt is the resource returned by the mysqli_prepare statement.
2. mysqli_stmt_affected_rows($stmt): returns the number of rows affected by an UPDATE, INSERT, or DELETE prepared statement. $stmt is the resource returned by the mysqli_prepare statement.

Extracting Information from Non-Prepared Statements

• array mysqli_fetch_array(resource result): returns the next unread row from the result as an array that permits both numeric and associative indices or false if the rows have been exhausted.
  1. The associative indices correspond to the columns' names.
  2. The numeric indices correspond to the columns' numeric position in the returned table.
  3. If two columns have the same name (e.g., a join has been performed on two tables and the SELECT statement has extracted columns from each table with the same name), then the last column will be the one returned. You can access the "masked" columns by their numeric positions in the returned table.
  4. Example code: $result = mysqli_query("SELECT * FROM person"); while($row = mysqli_fetch_array($result)) { echo $row['FirstName'] . " " . $row['LastName']; echo "<br /> \n"; echo $row[0] . " " . $row[1] . "\n"; } Note that I can access the columns via either their column names or their numeric position in the table.

  5. mysqli_fetch_row() can be used to return an array that contains just numeric indices but it is typically more readable to reference columns by their names and mysqli_fetch_array is not significantly slower than mysqli_fetch_row

• int mysqli_num_rows(resource result): returns the number of rows retrieved by a SELECT query

• int mysqli_affected_rows($conn): returns the number of rows affected by the last INSERT, UPDATE, or DELETE query