PHP Primer
This primer should contain all the code you need to access a MySQL database and build a table from the data, create an update form and submit it, and a few other tricks like creating a droplist in a form with the value from the current record pre-selected.
Please note: The commas in these code samples are converted automatically to curved 'smartquotes', in which the starting and ending quotes are different. When using these code samples in a text editor, be sure to change the quotes to uncurved straight quotes, otherwise the code will not run.
<?php
echo "Hello World!";
?>
About PHP
PHP (PHP Hypertext Pre-Processor – I know, the acronym doesn't completely make sense) is a server-side scripting language. It is open source, free software. In many ways it is comparable to other server-side scripting languages like ASP. PHP was originally developed in the mid 1990s as a personal project, but quickly evolved into a powerful scripting tool.
It differs from static web pages, usually identified by .htm or .html extensions in a browser's address bar, in that server-side scripts asssemble web pages at the moment the browser requests them. This enables a website to provide dynamic content, such as information drawn from a database in response to a specfic request from the visitor.
Static pages, by comparison, are not easily changed, and are passed from the server to the browser without modification.
Keep in mind that PHP isn't something that you will interact with directly like most computer programs you use. It is installed invisibly on a web server and used by the web server itself to interpret .php files.
Core Elements of a PHP Page
PHP code is created in one or more blocks, which may or may not be mixed in with HTML code.
Each block is opened and closed with the PHP tags <?php and ?>. Previous versions of PHP could be opened with just <?, but that can cause problems with current versions of PHP so save yourself some grief and always use <?php to open your PHP code blocks.
< ?php
echo “We’ll start with some PHP codeâ€;
?>
Now we have some HTML code
< ?php
echo “and now we’re back to PHP codeâ€;
?>
Here's a demonstration of a very simple PHP page.
<html>
<head>
<title>My Page Title</title>
</head>
<body>
<?php
echo "We'll start with some PHP code";
?>
<p>Now we have some HTML code</p>
<?php
echo "and now we're back to PHP code";
?>
</body>
</html>
Using Include Files
It's useful to put some code, like standard document opening and closing in an external file to be called by any file you need it for. Then you can change those elements for all pages by editing the include file.
Think of an include as a cut and paste job. Imagine that the server will paste the contents of the include file in the place you put the include() command.
I include the database select command in mine, because I generally only access one database in a php project.
I use a double extension ".inc.php" so I can tell what is an extension and I keep my folders organized by storing my includes in their own folder.
Always use the .php extension for includes so the server will never render the code visibly on a visitor's browser.
Here's an example of an include file used to insert a menu.
include("includes/menu.inc.php");
The most useful application of includes may be to create standardized page open and page close includes, or a database connect string, which we'll look at below.
You remember that the basic structure of an HTML page is more or less what follows. Of course this wouldn't validate and you're going to have more in there but for the purposes of illustration we'll work with this.
<html>
<head>
<title></title>
</head>
<body>
<p>Page content goes here.</p>
</body>
</html>
We'll start by creating two include files that will be common to all our pages. The first will be everything from the top down to the <body> tag and the second will be everything from the </body> tag down to the end.
standard-page-head.inc.php
<html>
<head>
<title></title>
</head>
<body>
standard-page-foot.inc.php
</body>
</html>
So now let's do something with it!
<?php
include("includes/standard-page-head.inc.php");
?>
<p>Page content goes here.</p>
<?php
include("includes/standard-page-foot.inc.php");
?>
This this us exactly the same basic HTML page we had before, but the structure of the page has been moved into external include files.
However, this simple example has serious limitations, as useful as it is. For instance, you won't want all your pages to have the same name.
So let's rework our page head include a bit;
standard-page-head.inc.php
<html>
<head>
<title><?php echo "$pagetitle"; ?></title>
</head>
<body>
With that we've added PHP code to inject the value of a PHP variable called $pagetitle.
Now we'll add a line to create and populate that variable prior to calling the include;
<?php
$pagetitle="My Page";
include("includes/standard-page-head.inc.php");
?>
<p>Page content goes here.</p>
<?php
include("includes/standard-page-foot.inc.php");
?>
The variable will be available when the include is loaded, and since I've added code to insert it in the <title></title> tags the page will be named with whatever we put in our $pagetitle variable.
Using a Query String
Often you're going to want one PHP file to do different things; I usually use the same file to present a data editing form and to update the submitted form data in the database.
You pass parameters through a query string in the URL to tell the page what you want it to do.
The Query String is that part of the URL which follows the filename, starting which a question mark. For example the query string file.php?city=ottawa&name=smith would pass two parameters, City and Name, with data accompanying each. Most often the query string will be used to tell the PHP script how to interact with a database.
If you use this technique, you need to test the query string at or near the top of the PHP file to see what it is supposed to do.
If you pass the query string edit.php?mode=update the following code can act on it.
Notice how I convert the query string to lower case for comparisons to reduce potential errors.
switch (strtolower($_REQUEST['mode'])) {
case "edit":
editForm(); // display a form to edit data
break:
case "add";
addForm(); // display a form to add new data
break:
default;
// the default block is executed if no other block is applicable
echo "This page cannot be directly accessed.";
break:
}
Database Connect String
Once you've created your PHP file, you're likely to want to connect to a mysql database server and select a database to work with. Do this early in your page so the database is available anyhere in the page.
Note how the or die() code combined with the mysql_error() function gives us useful feedback if for some reason a connect or query fails.
$dbconnect=mysql_connect ("server", "db_user", "password")
or die ('Cannot connect to database: ' . mysql_error());
mysql_select_db ("db_name") or die('Could not select database: '.mysql_error());
Get a Dataset
So now we've created a basic structure for our PHP file, connected to our database server and selected a database to work with. Now let's pull some data.
SQL is another tutorial, so I won't get too far into that here except to say that Structured Query Language (SQL) is a widely used language used to extract data from databases, as well as many other database maintenance tasks. As a quick example, SELECT fname,lname,phone FROM members WHERE city = "Ottawa" is the SQL code used used to query a database table called 'members' for the first and last names and phone numbers of people who live in Ottawa.
Here is an example of the PHP code used to read a set of data from a database and store it in a variable called $dataset.
$dataset = mysql_query("SELECT * FROM mytable") or die("ERROR: ".mysql_error());
Loop through a dataset
Now we're going to use a while loop to go through the dataset, reading one record at a time and acting on it.
A common use for a dataset, or recordset, is to build a table, like a membership directory. So you'll often capture a recordset, open an HTML table, loop through your recordset creating a row for each table, then close the HTML table.
Remember the table is opened and closed outside the loop, the rows are built inside the loop, one table row per dataset row.
echo "<table>";
while($row=mysql_fetch_array($dataset)) {
echo "<tr><td>Member name:</td><td>".$row['last_name'].", ".$row['first_name']."</td><td>".$row['phone']."</td></tr>";
} // end while loop
echo "</table>";
That works well, but as you can see the values from the row can't be placed within the quoted string, and the code can be hard to read, debug and work with.
So here's another method of doing the same thing that uses a foreach loop inside the while loop to cycle through attributes in the row, creating a variable for each. Variables are easier to work with - for instance they can be embedded in an echoed string - so for the processing cost of running the foreach loop we get code that's much easier to work with and less prone to errors.
What the foreach loop does is equivalent to doing this for each attribute in the row:
$attribute=$row['attribute'];
Unless you have a huge recordset I think the processing cost of this second approach is well worth it.
echo "<table>";
while($row=mysql_fetch_array($dataset)) {
foreach($row as $var => $value){
$$var = $value;
}
echo "<tr><td>Member name:</td><td>$last_name, $first_name</td><td>$phone</td></tr>";
} // end while loop
?>
echo "</table>";
Taking Stock
So what have we accomplished?
At this point, we know how to
- Build the structure of a PHP page
- Mix PHP with HTML
- Use includes and pass values into the includes
- Connect to a database
- Pull a dataset from the database
- Loop through a datset
- Extract the attribute values from each row in a dataset
- Use PHP to build a table in HTML to display the data to a user
What we haven't learned how to do is get data into our database, so let's have a look at that.
A Form to Edit an Existing Record
As I mentioned, I like to use the same PHP file to edit and submit data.
For such a multi-purpose page to work, however, code has to be laid out in functions or subroutines.
You'll remember that earlier I talked about using the query string for this purpose. By testing the query string at the top of our PHP file, we redirect the flow to the appropriate subroutine. The SWITCH statement below checks to see what parameter we've passed to the page through the query string in the URL to indicate what we're trying to do. Script execution is passed to the appropriate subroutine.
In this page, if no suitable parameter has been provided in the query execution passes to the default block of the switch statement where the user is advised that the page is cannot be directly accessed.
switch (strtolower($_REQUEST['mode']) == "update") {
case "edit":
editForm();
break:
case "add";
addForm();
break:
default;
echo "This page cannot be directly accessed.";
break:
}
So let's build the edit form.
To start with, we need to find out what record we want to edit, so we look back to the query string to the get the id of the record we're going to edit. For this purpose, as elsewhere in this document, we're going to assume that the primary key of our table is an integer value called 'id'. (Keep in mind that I'm going to assume here that the id parameter has been passed properly; in a real application you'd need to test this.)
So what we need to do now is query the database for the record we want to edit.
Let's look back to something we did earlier; querying the database for a record.
$dataset = mysql_query("SELECT * FROM mytable") or die("ERROR: ".mysql_error());
We only want one record this time, so we need to modify the SQL query to limit it. For clarity I've created a variable to capture the parameter from the query string. (Don't get query strings mixed up here; the query string passed to the page through the URL has nothing to due with our SQL query.)
So if we have the query string edit.php?mode=edit&id=237 we're going to pull the record with id value 237 from our database into a dataset.
$id = $_REQUEST['id'];
$dataset = mysql_query("SELECT * FROM mytable WHERE id = '$id'") or die("ERROR: ".mysql_error());
Let's take a look back at something else we did earlier. We'll combine while and foreach loops create a variable for each attribute in the record. (If you haven't read the Database Primer, keep in mind that an attribute is one element of information within a data record, such as the phone number.) Since this dataset only has one row, it won't take long to run!
while($row=mysql_fetch_array($dataset)) {
foreach($row as $var => $value){
$$var = $value;
}
}
Now that we've captured our data and fed it into variables where we can use it easily, let's build it into a form to make it available to the user for editing. We're going to assume that the only attributes in our table, and thus in our record, are the first and last names and a phone number.
There are a few things you'll need to keep in mind here;
- We're working primarily with HTML code to build a form, so at this point we will have ended our PHP code block which puts us into HTML code.
- Note the "<?php $_SERVER['PHP_SELF'];?>" PHP code in the action attribute of this form. This tells the page to load itself again. When this form is submitted the page will reload itself with a query string of ?mode=update and post the form data to the updateRecord() function. That happens because of the Query String test we implemented above.
- There's a hidden input field in this form. This doesn't show in the form, but is needed to pass the primary key value to the update function so the right record is updated. You'll see more of that in the code below when we build the SQL statement to update the database. Remember that id is the primary key value in the database table.
- We fed all the attributes into variables with a foreach loop above. You can see how the current values from the record are being plugged into the value='' attributes of the form elements (i.e. value="<?php echo "$first_name"; ?>") so the user can see and edit them in the form.
<form name="form1" method="post" action="<?php $_SERVER['PHP_SELF'];?>?mode=update">
<input type="hidden" name="id" value="<?php echo "$id"; ?>">
<input type="text" name="first_name" value="<?php echo "$first_name"; ?>">
<input type="text" name="last_name" value="<?php echo "$last_name"; ?>">
<input type="text" name="phone" value="<?php echo "$phone"; ?>">
</form>
Once we've done this, the user is presented with a form with the current record filled in, and is able to edit the data at will. Once the user submits the form, the data will be submitted to the database.
Our next task is to write the code to accept that data and write it into the database.
Writing Updates to the Database
This part gets a little more involved, so we'll go through this function in parts.
First we open the function.
function updateRecord() {
Now select the database, unless it's already been selected in an include file.
mysql_select_db ("database_name") or die('Could not select database');
We generate SQL code to update the record with the submitted data.
Some POSTed data we don't put in the database, for instance the Submit button value and the primary key which is never updated. So I use an exclusions array to list those values so they can be excluded from the SQL Query. An array is a type of variable that can hold more than one value.
With that done, we start to build our query string.
$exclusions = array ("id","Submit");
$sql = "UPDATE members SET ";
We loop through the POSTed values, adding each to the SQL string if it is not in the exclusions array. After that we use the PHP substr() function trim the trailing comma and space left by the final iteration of the foreach loop. $_POST refers to the collection of form data that was passed to the update function when the user submitted the form.
foreach ($_POST as $attrib => $value) {
if (!in_array($attrib, $exclusions)) {
$sql .= "$attrib='$value', ";
}
}
$sql = substr($sql, 0, -2);
We update the timestamp of the record (I said above there were only three attributes, first and last name and the phone number. Let's imagine we added a 'timestamp' attribute to the table so we can track changes - a useful attribute to have.)
The final element of the query string specifies the record to be updated, identifying it with the primary key id value which was POSTed through a hidden input field in the form.
You'll notice that there are backslash \ characters that may seem out of place. When we quote a string like this, we use quotes to mark the start and end of what we are quoting. If we need to use a quote mark inside the string, we 'escape' it by preceeding it with \ so that PHP will know we're using a quote mark, not ending the quoted string.
$sql .= " lastupdate =\"".date('Y-m-d H:i:s',time());
$sql .= " WHERE id=\"".$_POST['id']."\"";
Now that we've created the query string to update the database, it's a relatively simple matter to execute the query and update the record in the database, but we can to a little bit more here.
By embedding the command in an if statement we can do one of two things when the query has executed against the database.
If the query succeeds, we display a message to the user with a link to view their updated data.
If the query fails, we display an appropriate error message.
It's not strictly necessary to do these things, but if there is an error we want our application to respond gracefully.
if(mysql_query($sql) or die("Error writing changes to database: ".mysql_error()))
{
?>
<p>Thank you for taking the time to update your profile.</p>
<div align="center" style="border:1px solid silver;padding:5px">
<a href='profile.php?<?php echo $_POST['id'];?>'>
View your updated data.</a></div>
<?php
} else {
echo "Sorry, we encountered an error updating your information: ".mysql_error();
}
} //------ end function ------//
Now let's review the update function in one box;
function updateRecord() { mysql_select_db ("database_name") or die('Could not select database');
$exclusions = array ("id","Submit");
$sql = "UPDATE members SET "; foreach ($_POST as $attrib => $value) {
if (!in_array($attrib, $exclusions)) {
$sql .= "$attrib='$value', ";
}
}
$sql = substr($sql, 0, -2); $sql .= " lastupdate =\"".date('Y-m-d H:i:s',time());
$sql .= " WHERE id=\"".$_POST['id']."\"";
if(mysql_query($sql) or die("<p>Error writing changes to database: ".mysql_error()))
{
?>
<p>Thank you for taking the time to update your profile.</p>
<div align="center" style="border:1px solid silver;padding:5px">
<a href='profile.php?<?php echo $_POST['id'];?>'>
View your updated data.</a>
</div>
<?php
} else {
echo "Sorry, we encountered an error updating your information: ".mysql_error();
}
} //------ end function ------//
Providing an edit link in a list of records
You'll often want to provide an editing capability for administrators. Generally you do this by generating a list of all records or a subset of records.
Of course you need to carefully control who you allow to use an edit link. You do this by restricting access to the page to authorized users, or if you want to allow allow everyone to see the list, you would test to see if the user is a qualified administrator and add the edit link if they were authorized to edit.
The code below assumes you have tested user rights in a logon script and created a boolean variable like $current_user_is_admin = 1; for authorized administrators.
Make sure your edit page also tests for authorization, in case an unauthorized user follows a bookmark to the edit page.
if ($current_user_is_admin) {
echo "<a href='edit.php?id=<?php echo '$id'; ?>'>[edit]</a>";
}
Building a SELECT list in an edit form with the current value selected
When you let a user update an existing record, you need to have the current values in the form for the user to start with.
This code assumes that $thisrecord_prov contains the province attribute from the current record in the dataset.
First we create an array of possible values. A good way to do this might be to read a lookup table from the database into an array.
Next we open our select tag.
Now we use a foreach loop to built the select list row by row.
Notice that we test each value in the array against the current value of the prov attribute retrieved from the database record we are editing. If it matches, we set the SELECTED attribute for that row of the SELECT list so that it will be selected when the user sees it in the edit form.
Finally we close the select tag and echo it so that it appears in the form.
$provlist = array ("BC", "AB", "SK", "MB", "ON", "QC", "NB", "NS", "NL", "PE");
$select = "<select name=\"prov\">\n";
foreach ($provlist as $prov) {
$select .= "\t<option value=\"".$prov."\"";
if (strtolower($thisrecord_prov) == strtolower($prov)) {
$select .= " selected>".$prov."\n";
} else {
$select .= ">".$prov."\n";
}
}
$select .= "</select>";
echo $select;
Pulling a single value from the database
Sometimes you just want to pull a single value from the database without a whole lot of work. Here's how to do it in a single line;
// mysql_result() function is used to pull a single value from the database
mysql_result(mysql_query('SELECT price FROM stuff_we_sell WHERE product = "widget" '), 0);