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 = "cda150"; // localhost database name $dbServer = "localhost"; ?>
dbvars.php
FileUsing 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);
?>
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
// 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);
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.
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("<p>$selectQuery<br />" . mysqli_error($dbConn) . "</p>");
$row_cnt = $selectResult->num_rows;
printf("<b>Rows:</b> %d<br>\n", $row_cnt);
// Iterate through the result set
while (list($sku, $name, $price) = $selectResult->fetch_row()) {
printf("(%s) %s: \$%s <br/>\n", $sku, $name, $price);
}
$dbConn->close();
?>
SELECT
CodeSELECT 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)
SELECT
mysqli(
OutputThe MySQL INSERT
Statement
You would use the INSERT
statement to add a data record (row) to your table.
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("<p>$insertQuery<br />" . mysqli_error($dbConn) . "</p>");
$dbConn->close();
?>
INSERT
CodeThe MySQL DELETE
Statement
You would use the DELETE
statement to remove a data record (row) from your table.
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("<p>$deleteQuery<br />" . mysqli_error($dbConn) . "</p>");
$dbConn->close();
?>
DELETE
OutputWorking 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
Employee | Department | Location |
---|---|---|
Dylan, Bob | HR | New York, NY |
Cocker, Joe | HR | Paris, FR |
Prine, John | HR | London, UK |
JOIN
OutputThe 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.
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 <br/>", $sku, $name, $price);
}
// Close the statement to recuperate the statement resources
$stmt->close();
// Close the connection to recuperate the statement resources
$dbConn->close();
?>
bind
and prepare
Methods CodeAB223234 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
bind
and prepare
Methods OutputTask - 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
) themonthsTable
. - Generates HTML5/CSS to display the results set as an HTML table.
- This function should use a PHP conditional (
- 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 yourindex.php
,php_mysql_table.php
,vars.php
, andfunctions.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.