Accessing MySQL with PHP
Overview
III.1.1 Introduction
PHP can be used with MySQL to query and edit databases from a web site. This section will cover using PHP to query and edit a table in an example database. Specifically using PHP scripts for four operations will be covered: The four operations are:
- retrieving data from the MySQL database via queries;
- adding rows to a table in the database;
- deleting rows from a table in the database;
- and editing existing rows in the table.
III.1.2 Prerequisites
Apache, PHP and MySQL must be installed on the server. The installation of these software packages is covered in "Setting up a Local Linux Server with MySQL Support". Furthermore, this section uses MySQL commands from section "I. Basic MySQL", and assumes that the example database "arbordb" and the table "trees" have already been created in the format described in that section. A basic understanding, at least at the level covered in section "II. PHP and JavaScript", of the scripting language PHP is also expected.
III.1.3 Chapters
III.2.1 Handshake Code
Before any operations on the MySQL database can be performed by a PHP script,
the script must connect to the database.
The following simple PHP program, which can be saved as: "/var/www/test_connect.php" in the "/var/www" directory,
logs on to MySQL as a the database user, "arbprimary":
<!doctype html>
<html>
<body>
<?php
$servername = "localhost";
$username= "arbprimary";
$password = "the_password";
$dbname = "arbordb";
$mysqli = new mysqli($servername, $username, $password, $dbname);
if($mysqli->connect_errno){
printf("ERROR: Connection Failed: %s: ", $mysqli->connect_error);
exit();
} else {
echo "Successfully connected to MySQL";
}
$mysqli->close();
?>
</body>
</html>
The variables defined at the beginning of test_connect.php are the log in information for the database.
"the_password" must be replaced with whatever password was set for the database user
"arbprimary" from "Section I.".
The line:
logs into the database and creates a new "mysqli" object.
The next line:
checks the state of the object. If the database has been accessed successfully this line returns true.
The final line: "$mysqli->close()", closes the connection.
III.2.2 Handshake Execution
To test the ability of PHP scripts to connect to the database, run the code created in section III.2.1. To prepare to run the script, start MySQL:
Apache should already be running, since it typically starts at boot. If this feature has been disabled then start Apache as described in "Setting up a Local Linux Server with MySQL Support".
To run the script, open an Internet browser and enter
"http://localhost/test_connect.php" in the browser's address bar.
If the handshake was successful then, “Successfully connected to MySQL” should be displayed.
Reading the Database
III.3.1 A Simple Query
The following program which should be saved as "/var/www/test_query.php",
sends a simple query to the MySQL database and displays the result:
The first lines in this code create a "mysqi" object with access to the database.
The lines:
set up a MySQL query which searches for all rows with a name ending in "spruce"
and returns the values in the "species","name" and "height" columns from these rows.
Note that the string variable that determines the name searched for, "$nameStr",
is inserted into the query string surrounded by the single comment chars, "'"'s.
The line:
sends the query to MySQL and returns the result of the query in the "$result" object.
The next line tests this object to see if the query was successful.
The line:
Extracts from the result the number of rows in the table that satified the query. If the query's search criteria was not satisfied then the number of rows returned is 0. This happens in this example if "$nameStr" does not match any values in the "name" column in the table.
The line:
retrieves the (next) row, or record, from the result of the query.
Each time "fetch_array(MYSQLI_ASSOC)" is called it retrieves another row.
When the program is run it will print the rows from the tree table in the database that have the string, "spruce" at the end of their name field.
Editing the Database
III.4.1 Intro
There are three basic database manipulations of interest. Directly, these operations act on a table in a database. These three operations are, adding a line to; removing a line from; and editing a line in a table in the database. This subsection will cover the command syntax for these operations when executed by a PHP script.
III.4.2 Adding a new Line
The following program, which can be saved as: "test_write.php",
will write a new line to the table, "trees" in the database, "arbdb":
There are a series of commands in the script, "test_write.php".
After the database is opened the lines:
define two string variables and a numeric variable.
These variables are then inserted into string variable, "$queryStr":
Note that the string variables are inserted into "$queryStr" enclosed in single quotes, "'"'s,
while the numeric variable is just inserted as is.
The line:
inserts the new line with values for "species", "name",
and "height" into in the table "trees" in the database, "arbordb".
When the program is run it will, if successful, insert a new line into the database and print the message:
"New row added" in the browser.
III.4.3 Removing a Line
The following program which should be saved under, "test_delete.php",
deletes a line from the table "trees" in the database:
This script is composed of a series of commands.
The lines:
sets up a command string, "$queryStr",
that will tell MySQL to delete all lines from the table "trees" which have the name:
"testdata".
The line:
executes the command in “$queryStr” deleting the specified lines.
When this program is run it will print the message: "Row deleted, or not found", in the browser and the row(s) with the specified name will be deleted from the database.
III.4.4 Editing a Line
The following program, which can be saved as "test_edit.php",
resets values in a line in the table, "trees":
Again, there are a series of commands issued by this script.
After connecting to the database the program begins by defining several variables:
The first two variables are strings the rest are numeric.
The line:
defines a MySQL query(command) which resets the values of the "name", "height30", and "height" fields for lines in the table trees that pass the specified condition.
In this instance, the command causes the line for the "serbian spruce" in the table trees to change its name and height values.
Note that the string variables are enclosed in single quotes, "'"'s when inserted into the query-command string.
The line:
sends the command in the "$queryStr" to the database.
If this program runs successfully it will print the following message in the browser:
"Row updated, or already up to date";
If the tree named "serbian spruce" exists in the table, its "name", "height" and "height30" fields will be updated.
III.5.1 Executing the Scripts
To begin start MySQL:
Then run the test PHP program by opening a web browser and entering:
"http://localhost/tut0/test_programname.php" in the browser's address bar.
Here "test_programname.php" is either "test_connect.php", "test_query.php",
"test_write.php", "test_delete.php", or "test_edit.php".
It is recommended that the script, "test_write.php" be run before the script "test_delete.php",
since "test_write.php" creates the line that "test_delete.php" deletes.
III.5.2 Viewing the Results
The results of the operations on the database can be viewed with the MySQL monitor. To open the monitor:
Once in the monitor, the table "trees" can be viewed with the commands: