Guide to PHP and MySQL (University at Buffalo Version)

Chapter 9: Working With PHP and a MySQL Database

PHP and MySQL

Before you can work with a MySQL database using PHP you need to create a connection to your database. First, create a file called dbvars.php containing variables that store the server name, database name, your username for that database and your password for that database:

<?php
  $dbUsername = "your-localhost-username"; // probably 'root'
  $dbPassword = "your-localhost-mysql-password"; // probably ''
  $dbDatabase = "cda350"; // localhost database name
  $dbServer   = "localhost";
?>

Figure 9-1: PHP dbvars.php File

Using `mysqli()` to Connect to a MySQL Database

After you have your database connections variables defined you need to use the PHP require_once() function to have the variables available for your connection to your MySQL database. You use the mysqli() extension to connect to your database.

<?php
  require_once("dbvars.php");
  // Create a new server connection
  $dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
?>

Figure 9-2: Using mysqli() to Connect to a Database

Using `PDO()` to Connect to a MySQL Database

After you have your database connections variables defined you might also use the PHP Data Objects (PDO()) interface to connect to your database. PDO is handy because it provides drivers which can be used with other databases such as DB2, MS SQL, Oracle, or PostgreSQL

<?php
// Add this next line to your dbvars.php file
$pdo = "mysql:host=$dbServer;dbname=$dbDatabase";
require_once("dbvars.php");
// Create a new server connection
$dbConn = new PDO($pdo, $dbUsername, $dbPassword);

Figure 9-3: Using PDO() to Connect to a Database

The MySQL `SELECT` Statement using `mysqli()`

Yuo would use the SELECT statement to retrieve a data record (or records) from your database. You need to create a variable to store your SQL query. Then you need to execute that query, walk through the results set to display the database data and, finally, close the database connetion.

<?php
require_once("dbvars.php");
// Create a new server connection
$dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
// Create the query
$selectQuery = "SELECT sku, name, price FROM products1 ORDER by name";
// Send the query to MySQL
$selectResult = $dbConn->query($query,MYSQLI_STORE_RESULT)
or die("$selectQuery " . mysqli_error($dbConn) . "");
$row_cnt = $selectResult->num_rows;
printf("Rows: %d \n", $row_cnt);
// Iterate through the result set
while (list($sku, $name, $price) = $selectResult->fetch_row()) {
printf("(%s) %s: \$%s \n", $sku, $name, $price);
}
$dbConn->close();
?>

Figure 9-4: The MySQL SELECT Code

  
  SELECT id, sku, name, price FROM products1 ORDER by name;
  
+----+----------+-----------------------+-------+
| id | sku      | name                  | price |
+----+----------+-----------------------+-------+
|  1 | TY232278 | AquaSmooth Toothpaste |  2.25 |
|  2 | PO988932 | HeadsFree Shampoo     |  3.99 |
|  3 | ZP457321 | Painless Aftershave   |  4.50 |
|  4 | KL334899 | WhiskerWrecker Razors |  4.17 |
+----+----------+-----------------------+-------+
4 rows in set (0.00 sec)

Figure 9-5: The MySQL SELECT mysqli( Output

The MySQL `INSERT` Statement

You would use the INSERT statement to add a data record (row) to your table.

<?php
require_once("dbvars.php");
// Create a new server connection
$dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
// Create the query
$insertQuery = "INSERT INTO products1 (sku, name, price) VALUES ('VS123456', 'Volume Shampoo', 2.25)";
// Send the query to MySQL
$insertResult = $dbConn->query($query,MYSQLI_STORE_RESULT)
or die("$insertQuery " . mysqli_error($dbConn) . "");
$dbConn->close();
?>

Figure 9-6: The MySQL INSERT Code

The MySQL `DELETE` Statement

You would use the DELETE statement to remove a data record (row) from your table.

<?php
require_once("dbvars.php");
// Create a new server connection
$dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
// Create the query
$deleteQuery = "DELETE FROM products1 WHERE sku = 'VS123456'";
// Send the query to MySQL
$deleteResult = $dbConn->query($query,MYSQLI_STORE_RESULT)
or die("$deleteQuery " . mysqli_error($dbConn) . "");
$dbConn->close();
?>

Figure 9-7: Database Connect and DELETE Output

Working With Multiple MySQL Tables

In most applications you will need to work with more than one table.

The MySQL `join` Keyword

You can use the join keyword to relate two or more tables together.

<?php
  require_once("dbvars.php");
  // Create a new server connection
  $dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
  // Create the query
  $selectQuery  = "SELECT firstName, lastName, DeptName, locationName";
  $selectQuery .= "FROM employees";
  $selectQuery .= "JOIN departments ON employees.dept = departments.departmentID";
  $selectQuery .= "JOIN locations ON departments.location = locations.locationID";
  // Send the query to MySQL
  $selectResult = $dbConn->query($selectQuery, MYSQLI_STORE_RESULT)
  or die("<p>$selectQuery<br />" . $dbConn->errorInfo() . "</p>");
  $row_cnt = $selectResult->num_rows;
  printf("<b>Rows: %d</b><br>\n", $row_cnt);
  $table = <<< _END
     <table>
       <thead>
         <tr>
           <th>Employee</th>
           <th>Department</th>
           <th>Location</th>
         </tr>
       </thead>
       <tbody>
_END;
  // Iterate through the result set
  while (list($firstName, $lastName, $deptName, $locName) = $selectResult->fetch_row()) {
    $table .= "<tr class='evenRow'>\n<td>$lastName, $firstName</td>\n";
    $table .= "<td style='text-align: center;'>$deptName</td>\n<td>$locName</td>\n</tr>\n";
  }
  $table .= "</tbody>\n</table>\n";
  echo($table);
  $dbConn->close();
?>
   Figure 9-8: MySQL JOIN Code

Rows: 3

Employee	Department	Location
Dylan, Bob	HR	New York, NY
Cocker, Joe	HR	Paris, FR
Prine, John	HR	London, UK

Figure 9-9: MySQL JOIN Output

The `prepare` and `bind` Methods

You can use the prepare method to setup a query that would use parameters. You then use bind method to provide variable values for those parameters.

<?php
require_once("dbvars.php");
// Create a new server connection
$dbConn = new mysqli($dbServer, $dbUsername, $dbPassword, $dbDatabase);
// Create the query
$selectQuery = "SELECT sku, name, price FROM products1 ORDER BY sku";
// Create a statement object
$stmt = $dbConn->stmt_init();
// Prepare the statement for execution
$stmt->prepare($query);
// Execute the statement
$stmt->execute();
// bind result variables
$stmt->bind_result($sku, $name, $price);
// Do something with the prepared statement
while($stmt->fetch()) {
printf("%s %s $%.02f ", $sku, $name, $price);
}
// Close the statement to recuperate the statement resources
$stmt->close();
// Close the connection to recuperate the statement resources
$dbConn->close();
?>

Figure 9-10: The MySQL bind and prepare Methods Code

AB223234 Lovable Lipstick $1.45
AB223234 Lovable Lipstick $1.45
KL334899 WhiskerWrecker Razors $4.17
MN873213 Minty Mouthwash $1.95
PO988932 HeadsFree Shampoo $3.99
TY232278 AquaSmooth Toothpaste $2.25
ZP457321 Painless Aftershave $4.50

Figure 9-11: The MySQL bind and prepare Methods Output

Task - Create a PHP Table Page From a MySQL Table

For this Assignment you will create a page, php_mysql_table.php on your local web server and the BSC web server. This file should be based on your lfa.php file. This web page should:

Use PHP to create a function that reads from the MySQL monthsTable table and displays an HTML table page.
- This function should use a PHP conditional (if) test to determine whether the row is odd or even and use appropriate CSS to make the even rows a different background/foreground color than the odd rows.
- Create a PHP function that:
  - Connects to your MySQL server.
  - Requests (SELECT) all records in (FROM) the monthsTable.
  - Generates HTML5/CSS to display the results set as an HTML table.
Use appropriate PHP comments throughout your code.
Appropriate HTML5, CSS-styled tags that identify this as your web page including your name and a brief paragraph describing how you approached solving this problem.
You should use appropriately identified and styled <div> tags for the various areas of your page layout.
Modify your JavaScript in your index.html file that uses an array of the Assignments for this course to display an ordered list of these assignments to convert the entry for this assignment to a web link that allows me download and save your index.php, php_mysql_table.php, vars.php, and functions.php files so I can download, save, and grade your work.

You *MUST* use the W3C Unicorn Validator to validate your HTML5/CSS3 code.

You will modify the PHP function for this item in your index.php page to add a link to your php_mysql_table.php that allows me to view your work.

Let's Get Started with a PHP/MySQL Table Page!

Guide to PHP and MySQL (University at Buffalo Version)

Chapter 9: Working With PHP and a MySQL Database

PHP and MySQL

Using mysqli() to Connect to a MySQL Database

Using PDO() to Connect to a MySQL Database

The MySQL SELECT Statement using mysqli()

The MySQL INSERT Statement

The MySQL DELETE Statement