Practical PHP and MySQL Web Site Databases: A Simplified Approach (2013)

Chapter 4. Display Membership Tables for the Administrator

In this chapter, we aim to present a user-friendly set of pages for an administrator who has moderate computer skills. We are assuming the administrator is not capable of using phpMyAdmin, but that he knows enough to be able to log in, access easy-to-use displays, and use simple editing facilities. We will make the table of members interactive so that the administrator can search, edit, delete, and re-order records. Only the administrator is permitted to access these facilities for deleting and amending records. The webmaster will be restricted to using phpMyAdmin to access the records. This chapter will cover the following topics:

· The administration database

· Deleting and editing records

· Paginating the displayed records

· Searching for an individual record

Caution When we talk about tables in this book, try not to confuse tables displayed on the screen with the database tables. These types of tables are two distinct entities. For instance, Table 4-1 is a database table and is not visible to the user. Whereas, Figure 4-2 is a table displayed on the user’s screen.

The Administration Database

The tutorials in this chapter are based on the templates used in previous chapters. We will keep things separate from previous tutorials by using new names for the database and its folder. However, a table can have the same name as earlier tutorials as long as the table lives in a different database. The table will therefore continue to be called users.

Create a new folder and a new database as follows:

1. In the XAMPP htdocs folder, create a new folder called admintable.

2. Download the PHP files from the book’s page at www.apress.com, and place them in the new admintable folder.

3. Now type http://localhost/phpmyadmin in the address field of your browser.

4. Create a new database called admintable.

5. Select the check box next to logindb, and click Check Privileges.

6. Click the Add User button.

7. Enter a new user and password as follows:

1. User: webmaster

2. Password: coffeepot

3. Host: localhost

8. Be sure to record the new user and password details in your notebook.

9. Scroll down, and where it says Global privileges (Check All/Uncheck All), click Check All.

10.Scroll down to the bottom of the form, and click the Add User button (or the Go button on some versions).

To connect to the administration database, you use the following code.

Listing for creating the snippet of code for connecting to the admintable database (mysqli_connect.php)

<?php
// Create a connection to the admintable database and set the encoding
DEFINE ('DB_USER', 'webmaster');
DEFINE ('DB_PASSWORD', 'coffeepot');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'admintable');
// Make the connection:
$dbcon = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
// Set the encoding...
mysqli_set_charset($dbcon, 'utf8');

The Users Table

In phpMyAdmin, make sure you click the new database admintable and then create this users table with seven columns. Give the columns the titles and attributes shown in Table 4-1.

Table 4-1. The Attributes for the Users Table

Use the register-page.php file (included in the downloadable files for Chapter 4), to register the administrator as an ordinary member using the following data:

First name: James

Last name: Smith

E-mail: jsmith@myisp.co.uk

Password: blacksmith

The registered member James Smith will be appointed as the Membership secretary using the pseudonym Jack Smith. We can safely assume that James Smith (alias Jack Smith) would like to view the members’ page occasionally as well as viewing the administration page. He will use his membership e-mail and password to view the members’ page, but he needs a different e-mail and password to access the administration section. Now register the membership secretary a second time with his pseudonym and a new password as follows:

First name: Jack

Last name: Smith

E-mail:jsmith@outcook.com,

Password: dogsbody

Important: Use phpMyAdmin to change Jack Smith’s user level to 1.

Now register these members so that we have something to play with. Use the details in Table 4-2.

Table 4-2. Suggested Details for Members

Name	E-mail	Password
Mike Rosoft	miker@myisp.com	willgates
Olive Branch	obranch@myisp.co.uk	anoilyone
Frank Incense	fincense@myisp.net	perfume
Annie Versary	aversary@myisp.com	happyday
Terry Fide	tfide@myisp.de	scaredstiff
Rose Bush	rbush@myisp.co.uk	redblooms

You will now have a total of eight records.

Note Now that we are using sessions together with the member’s user_level, you will not be able to access any of the administration pages without first logging in as the administrator.

I amended the administrator’s page from Chapter 3 so that it includes some extra facilities.

The Revised Administration Page

In Chapter 3, the admin page only had a small piece of text in the main content. It now has four items describing the extra features that will be available to the administrator. These are shown in Figure 4-1.

Figure 4-1. The revised Admin Page with three extra features

The extra items and amended features are shown in bold type in the admin page Listing 4-1.

Listing 4-1. Creating an Administrator’s Page with Extra Features and a Session (admin-page.php)

Explanation of the Code

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>

This is the security guard in the admin page. He looks up the session details. If either (i) the session does not exist or (ii) the user level does not match the level given in the session data, he sends the user back to the login page.

<?php #2
echo '<h2>Welcome to the Admin Page, ';
if (isset($_SESSION['fname'])){
echo "{$_SESSION['fname']}";
}
echo '</h2>';
?>

This piece of code takes the user’s first name from the session array and inserts it into the welcome message:

<p>■Edit and delete a record.</p> #3
<p>■Use the View Members button to page through all the members.</p>
<p>■Use the Search button to locate a particular member.</p>
<p>■Use the Addresses button to locate a member's address and phone number. </p>

These are the four items in the body of the page. The entity ■ provides a square bullet.

Note If you add extra columns to a displayed table, don’t forget to change its width in the main style sheet.

We display the extended table of data by means of a revised page named view_users.php.

Revising the View Users Page to Include Editing and Deleting

The display table needs two extra columns for links that will enable the administrator to edit and delete records. In Chapter 3, we concatenated (joined) the first and last names so that they appeared in one column. To be able to re-order records, you’ll find that it’s best if the first and last names are displayed in separate columns. Passwords are not included in the display because they are enciphered and would mean nothing to the administrator. The revised display for the administrator will therefore have six columns, as shown in Table 4-3. Remember that this is not the usersdatabase table but the table that will be displayed on the screen for the administrator; the two tables are quite different.

Table 4-3. The Format of the New Table as It Will be Displayed for the Administrator Only

The style sheet downloadable for this chapter (includes.css) is almost the same as the file in Chapter 3, but the table width has been increased from 500 pixels to 700 pixels so that the table can accommodate the three extra columns.

Two of the new columns have links to enable the administrator to interact with the records. Most importantly, we intend the page to be private; therefore, it needs a security guard—in other words, a session.

The appearance of the interactive table is shown in Figure 4-2.

Figure 4-2. The interactive table for an administrator

Note You can view the table using XAMPP and a browser, but the links edit and delete will not work until we create the pages for deleting and editing a record. These pages will be described later in the chapter.

The amendments to create the new View Users page are shown in bold type in the Listing 4-2.

The administrator’s page begins with session code for security:

Listing 4-2. Amending the Table View to Include Two Links (view_users-page.php)

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>An administrator's View-members page</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
</head>
<body>
<div id="container">
<?php include("header-admin.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>These are the registered users</h2>
<p>
<?php
// This script retrieves all the records from the users table
require ('mysqli_connect.php'); // Connect to the database
// Make the query #2
$q = "SELECT lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y')
AS regdat, user_id FROM users ORDER BY registration_date ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran without a problem, display the records
// Table headings #3
echo '<table>
<tr>
<td><b>Edit</b></td>
<td><b>Delete</b></td>
<td><b>Last Name</b></td>
<td><b>First Name</b></td>
<td><b>Email</b></td>
<td><b>Date Registered</b></td>
</tr>';
// Fetch and print all the records #4
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr> #5
<td><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
<td><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>
<td>' . $row['lname'] . '</td> #6
<td>' . $row['fname'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $row['regdat'] . '</td></tr>';
}
echo '</table>'; // Close the table #7
mysqli_free_result ($result); // Free up the resources
} else { // If it failed to run
// Error message
echo '<p class="error">The current users could not be retrieved. We apologize
for any inconvenience.</p>';
// Debugging message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
mysqli_close($dbcon); // Close the database connection
?>
</p>
</div>
<?php include("footer.php"); ?>
</div>
</body>
</html>

Explanation of the Code

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>

This session code returns unauthorized users to the login page.

// Make the query: #2
$q = "SELECT lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y')
AS regdat, user_id FROM users ORDER BY registration_date ASC";
$result = @mysqli_query ($dbcon, $q); // Run the query
if ($result) { // If it ran OK, display the records

The code looks in the users table and selects the five items that will populate the last five columns in the displayed table. The table records are displayed in ascending order of registration date. The order can be reversed by using DESC instead of ASC.

// Table header #3
echo '<table>
<tr>
<td><b>Edit</b></td>
<td><b>Delete</b></td>
<td><b>Last Name</b></td>
<td><b>First Name</b></td>
<td><b>Email</b></td>
<td><b>Date Registered</b></td>
</tr>';

The six headings in the title row of the table are created.

// Fetch and echo all the records #4
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

The code loops through the users table’s data until all the data has been displayed. This is achieved by the while() function.

echo ' #5
<tr>
<td><a href="edit_user.php?id=' . $row['user_id'] . '">Edit</a></td>
<td><a href="delete_user.php?id=' . $row['user_id'] . '">Delete</a></td>

This code displays the first two cells in each row. Each cell contains a link to a PHP page. The links connect to the pages edit_user.php and delete_user.php. As the code loops through the data in the users table, it stores the corresponding user_id for each row. The PHP page and theuser_id are combined using a device that has this pattern:

<a href="delete_user.php?id='the user_id for that row'">Delete</a>

In the preceding pattern, the user_id for the row is passed to the page that will handle the deletion. The whole line of code is a link that, when clicked, will delete the identified row. For clarification, Figure 4-3 shows a fragment of the interactive table and the two links.

Figure 4-3. Showing one record and the two links in the first two cells

<td>' . $row['lname'] . '</td> #6
<td>' . $row['fname'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $row['regdat'] . '</td>
</tr>';
}

In the block of code starting with line #6, the values for the last four cells are extracted from the arrays for each displayed record.

echo '</table>'; // Close the table #7
mysqli_free_result ($result); // Free up the resources

The table’s closing tag is printed to the browser, and memory resources are freed up. The rest of the code deals with error messages and adds the footer to the displayed page.

The next section describes a method of displaying tables one page at a time.

Displaying Pages of Records (Pagination)

The table display created so far is fine for up to 30 records. A greater number of records will cause the table and the administrator to scroll down. Imagine what would happen if the database contained 1,000 records or more. Creating the ability to view the table one screen at a time would provide a solution; however, even this would be inconvenient for a large database. Later in this chapter, we will examine a slicker way of selecting and viewing one particular record in a large database; meanwhile, we will learn how to display a table so that the administrator can see one page at a time. The process is called pagination.

A page length of 20 rows would be a sensible choice; however, for the purposes of this tutorial, it would mean you would have to register a total of around 60 new members. That would give you at least three pages, but I am sure you would prefer to press on with the tutorial. Therefore, we will set the number of rows per displayed page to four so that you will need to register only another eight members. That will give you four pages to play with.

Table 4-4 suggests another 10 members to register.

Table 4-4. Details for Registering Members

You will now have a total of 18 records. When you divide this by four (rows per page), you will have four and a half pages. Following this explanation, in Listing 4-4a for admin_view_users.php, you will see that the number of rows per page has been set by the variable $pagerows as follows:

$pagerows = 4;

Later, you can experiment by giving the variable a value of say 9 or 15 to see the effect.

Figure 4-4 shows the display of four records per page and the links for moving back and forward between the pages. The page also displays the total number of members.

Figure 4-4. The display now shows four records per page and indicates the total membership

The page named admin_view_users.php will demonstrate pagination.

In Figure 4-4, you can see the links Previous and Next that will enable you to move from page to page. Instead of those links, we could have clickable page numbers. However, if you don’t know what is on each page, a page number is not much use. The ability to browse through the pages and also having the total membership displayed will be useful to the administrator. The pagination will also enable the administrator to print each page, and this will be described in Chapter 7. For displaying one particular member’s record, we will use the new Search menu button. This is described later in the chapter.

Listing 4-4a incorporates the code to enable pagination. The amendments and additions are shown in bold type.

Listing 4-4a. Creating a Table Display That Will Paginate (admin_view_users.php)

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Admin view users page for an administrator</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; } #2
</style>
</head>
<body>
<div id="container">
<?php include("header-admin.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>Registered members displayed four at a time</h2>
<p>
<?php
// This code retrieves all the records from the users table.
require ('mysqli_connect.php'); // Connect to the database.
//set the number of rows per display page
$pagerows = 4; #3
// Has the total number of pages already been calculated?
if (isset($_GET['p']) && is_numeric
($_GET['p'])) {//already been calculated
$pages=$_GET['p'];
}else{//use the next block of code to calculate the number of pages #4
//First, check for the total number of records
$q = "SELECT COUNT(user_id) FROM users";
$result = @mysqli_query ($dbcon, $q);
$row = @mysqli_fetch_array ($result, MYSQLI_NUM);
$records = $row[0];
//Now calculate the number of pages
if ($records > $pagerows){ //if the number of records will fill more than one page
//Calculatethe number of pages and round the result up to the nearest integer
$pages = ceil ($records/$pagerows); #5
}else{
$pages = 1;
}
}//page check finished
//Declare which record to start with #6
if (isset($_GET['s']) && is_numeric
($_GET['s'])) {//already been calculated
$start = $_GET['s'];
}else{
$start = 0;
}
// Make the query: #7
$q = "SELECT lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y')
AS regdat, user_id FROM users ORDER BY registration_date ASC LIMIT $start, $pagerows";
$result = @mysqli_query ($dbcon, $q); // Run the query.
$members = mysqli_num_rows($result);
if ($result) { // If it ran OK, display the records.
// Table header.
echo '<table>
<tr><td><b>Edit</b></td>
<td><b>Delete</b></td>
<td><b>Last Name</b></td>
<td><b>First Name</b></td>
<td><b>Email</b></td>
<td><b>Date Registered</b></td>
</tr>';
// Fetch and print the records:
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
echo '<tr>
<td><a href="edit_record.php?id=' . $row['user_id'] . '">Edit</a></td>
<td><a href="delete_record.php?id=' . $row['user_id'] . '">Delete</a></td>
<td>' . $row['lname'] . '</td>
<td>' . $row['fname'] . '</td>
<td>' . $row['email'] . '</td>
<td>' . $row['regdat'] . '</td>
</tr>';
}
echo '</table>'; // Close the table
mysqli_free_result ($result); // Free up the resources
} else { // If it failed to run
// Error message
echo '<p class="error">The current users could not be retrieved. We apologize for
any inconvenience.</p>';
// Debug message
echo '<p>' . mysqli_error($dbcon) . '<br><br />Query: ' . $q . '</p>';
} // End of if ($result)
//Now display the figure for the total number of records/members #8
$q = "SELECT COUNT(user_id) FROM users";
$result = @mysqli_query ($dbcon, $q);
$row = @mysqli_fetch_array ($result, MYSQLI_NUM);
$members = $row[0];
mysqli_close($dbcon); // Close the database connection #9
echo "<p>Total membership: $members</p>";
if ($pages > 1) { #10
echo '<p>';
//What number is the current page?
$current_page = ($start/$pagerows) + 1;
//If the page is not the first page then create a Previous link
if ($current_page != 1) {
echo '<a href="register-view_users.php?s=' . ($start - $pagerows) .
'&p=' . $pages . '">Previous</a> ';
}
//Create a Next link #11
if ($current_page != $pages) {
echo '<a href="register-view_users.php?s=' . ($start + $pagerows) .
'&p=' . $pages .'">Next</a> ';
}
echo '</p>';
}
?>
</div>
<?php include("footer.php"); ?>
</div>
</body>
</html>

Explanation of the Code

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>

The session is our security guard. He will appear in every private page used by the administrator. As in the previous tutorial, the session redirects unauthorized people to the login page.

p { text-align:center; } #2

An internal style is added to center the Previous and Next links and the Total Membership figure.

$pagerows = 4; #3

Set the display to four records per page. (You would set this to about 20 in a real-world page.)

}else{//use the next block of code to calculate the number of pages #4
//First, check for the total number of records
$q = "SELECT COUNT(user_id) FROM users";
$result = @mysqli_query ($dbcon, $q);
$row = @mysqli_fetch_array ($result, MYSQLI_NUM);
$records = $row[0];
//Now calculate the number of pages
if ($records > $pagerows){ //if the number of records will fill more than one page
//Calculate the number of pagesand round the result up to the nearest integer

If the number of pages has not already been calculated by the code, the code calculates it now by counting the number of user_ids in the users table. Then it checks to see if the number of records is greater than the number of records displayed per page.

$pages = ceil ($records/$pagerows); #5
}else{
$pages = 1;
}
}//page check finished

If the number of records is greater than the number of records displayed per page, round up the number of pages to a whole number. The PHP function ceil() rounds up the number of pages. In the tutorial, we have 18 records, resulting in four and a half pages (18 divided by 4). This is rounded up to five pages. The fifth page will display only two records. If the number of records is not greater than the number of rows per display, there will be only one page. The function ceil() means set the ceiling or set to an integer above the actual count; in this case, 4.5 becomes 5.

//Declare which record to start with #6
if (isset($_GET['s']) && is_numeric
($_GET['s'])) {//already been calculated
$start = $_GET['s'];
}else{
$start = 0;
}

This ensures that the variable [‘s’] is an integer. Then the start point for the display is set to zero.

// Make the query #7
$q = "SELECT lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y')
AS regdat, user_id FROM users ORDER BY registration_date ASC LIMIT $start, $pagerows";

The query selects the columns to be displayed and sets the number of rows to be displayed per page, starting with the record number zero. (The variable $start was set to zero.)

//Now display the figure for the total number of records/members #8
$q = "SELECT COUNT(user_id) FROM users";
$result = @mysqli_query ($dbcon, $q);
$row = @mysqli_fetch_array ($result, MYSQLI_NUM);
$members = $row[0];

This code counts the total number of rows in the table and assigns the total to the variable $members.

mysqli_close($dbcon); // Close the database connection. #9
echo "<p>Total membership: $members</p>";

The database is closed, and the total number of members is displayed on the screen.

if ($pages > 1) { #10
echo '<p>';
//What number is the current page?
$current_page = ($start/$pagerows) + 1;
//If the page is not the first page then create a "Previous" link
if ($current_page != 1) {
echo '<a href="register-view_users.php?s=' . ($start - $pagerows) .
'&p=' . $pages . '">Previous</a> ';
}

The link named Previous is created and displayed.

//Create a "Next" link #11
if ($current_page != $pages) {
echo '<a href="register-view_users.php?s=' . ($start + $pagerows) .
'&p=' . $pages . '">Next</a> ';
}
echo '</p>';
}
?>
</div>

The link named Next is created and displayed. Fire up XAMPP, and then log in as the administrator to view the pagination.

We now need to consider how to search for one particular record and then display it.

You may have noticed that the header for the admin_view_users.php page is changed so that the View Members button is a link to the admin_view_users.php page. Also, a new button links to the search page search.php. The changes are shown in bold type in Listing 4-4b.

Listing 4-4b. Creating the Revised Administration Header (header-admin.php)

<div id="header-admin">
<h1>This is the admin header</h1>
<div id="reg-navigation">
<ul>
<li><a href="logout.php">Logout</a></li>
<li><a href="admin_view_users.php">View Members</a></li>
<li><a href="search.php">Search</a></li>
<li><a href="register-password.php">New Password</a></li>
</ul>
</div>
</div>

Planning the Search Criteria

The administrator needs a search facility so that he does not have to scroll through scores of records to edit or delete one. The importance of forward planning cannot be over emphasized; deciding how to organize the search facility requires careful thought.

The administrator can edit and delete records. So what events would require him to use these facilities? For editing, the event would typically be members notifying the administrator of a change of e-mail address, or if a female member got married and wanted to change her last name, a new last name would need to be entered. Deletions would be required if a member resigns or dies.

Some names are very common—for instance, we may have five people named James Smith. We would need to display all five and then use their e-mail addresses to distinguish them so that we don’t delete the wrong James Smith.

The next tutorial describes the creation of a page that displays the results of a search. For this, you will need to register four more members named James Smith. Table 4-5 offers some suggestions.

Table 4-5. Some Identical Common Names

When you have registered them, there will be a total of five members named James Smith and the total number of records in our database will be 22.

A Temporary Page for Displaying Specified Members

We will eventually create a search facility, but first we must create a page that will produce a table of members named James Smith. The target or end result of a search for James Smith will be a table, as shown in Figure 4-5.

Figure 4-5. The five James Smiths have been located and are now displayed

To ensure that we are able to display the table shown in Figure 4-5, we need to create a page that we will call view_found_record.php. Because we have not created a search page, we will enter the names James and Smith directly into the page as a temporary expedient. Listing 4-5 is shown next.

Listing 4-5. Creating a Temporary Page for Displaying the Results of a Search (temp_view_found_record.php)

Note This is a temporary version of the page. It is a helpful stage before producing the final version. It demonstrates an important principle and is a useful exercise for proving that a table will be displayed. The temporary version is included in the downloadable files for this chapter astemp_view_found_record.php.

Explanation of the Code

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}

Our security guard (session) prevents unauthorized people from searching the database.

// This code retrieves particular records from the users table #2
require ('mysqli_connect.php'); // Connect to the database
// Make the query using hard coded names
$q = "SELECT lname, fname, email, DATE_FORMAT(registration_date, '%M %d, %Y') AS regdat,
userid FROM users WHERE lname='Smith'AND fname='James'ORDER BY registration_date ASC ";

This SQL query is almost the same as the one in admin_view_users.php except that the LIMIT on the number of rows has been removed and a new keyword, WHERE, has been introduced. Removing the limit on the number of rows ensures that all the James Smith records will be displayed no matter how many there are. The WHERE clause specifies exactly which records to display. Note that we cheated here and put some actual names in; eventually, the names will be replaced by names sent from the search page. We entered names as a temporary expedient so that we can test the page and ensure that it works as expected. This is a common technique and is known as hard coding. The resulting table display was shown in Figure 4-5.

Note Now that we are using sessions, you will not be able to access any of the administration pages without logging in as the administrator.

We now a have a page that will display a table of specified records. With that working correctly, the next step is to provide a search form so that the administrator can request a table showing any specified members rather than only James Smith.

The Search Form

When the administrator clicks the Search button on the header menu, he will be taken to the search.php page. This page has a form containing two fields where search criteria can be entered. Planning is needed to decide what the search criteria should be. A grieving widow, requesting that her husband’s details be removed, may not know the e-mail address or password he used for his membership registration. The member’s registration date is a poor choice for locating the member because members will inevitably forget the exact date they registered. The administrator needs to know some unique but obvious information in order to display the member’s record. The obvious criteria is the member’s first and last names; therefore, that is what we will use.

Figure 4-6 shows the Search form.

Figure 4-6. The search form

The search form enables the administrator to search for a particular record. If the search is successful, the view_found_record.php page is activated and a record (or records) will be displayed. If several people have the same first and last names, the table will display all of them.

Don’t try entering a search just yet. Currently, we have set (hard coded) the view_found_record.php page to always show James Smith’s records. This temporary page will be changed by means of the search form described in Listing 4-6. To avoid cluttering the code and to demonstrate the basic principle, the search form is extremely simple because it contains no error traps. Errors are reported in a rather primitive manner in the view_found_record.php page.

Listing 4-6. Creating the Search Form (search.php)

Explanation of the Code

<?php #1
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>

The session is our security guard; unauthorized people will be redirected to the login page.

<form action="view_found_record.php" method="post"> #2

<p><label class="label" for="fname">First Name:</label>
<input id="fname" type="text" name="fname" size="30" maxlength="30"
value="<?php if (isset($_POST['fname'])) echo $_POST['fname']; ?>"></p>

<p><label class="label" for="lname">Last Name:</label>
<input id="lname" type="text" name="lname" size="30" maxlength="40"
value="<?php if (isset($_POST['lname'])) echo $_POST['lname']; ?>"></p>

<p><input id="submit" type="submit" name="submit" value="Search"></p>
</form>

The form posts the data to a final version of the form handler named view_found_record.php, which processes the search. The code supplies a sticky form. A sticky form is one that retains the user’s entries when an error is flagged. Users can become very annoyed if they have to fill out the entire form each time they need to correct an error such as failing to fill out one field. The sticky component is the code following the value, for example:

value="<?php if (isset($_POST['fname'])) echo $_POST['fname']; ?>"></p>

We will now make the form handler view_found_record.php more versatile.

The Final Form Handler for Receiving Search Form Input

The code in bold type shows what has been added or modified in the first part of the code. These items enable the file to receive the first name and the last name sent from the search.php page. In Listing 4-6, the first and last names are assigned to variables and these variables are used to search the database so that they can be verified. The variables are then used to display the record(s).

Here’s a partial listing for a snippet of code to eliminate the hard-coding and allow searches for any registered member’s record:

At last, you can test the search facility. Run XAMPP, and type the following into the address field of a browser:

http://localhost/admintable/login.php

Use the administrator’s e-mail address and password to log in. The e-mail is jsmith@outlook.com, and the password is dogsbody. Then click the search button on the header menu. In the search form, try entering Jack Smith to see a single record displayed.

The result should be the record shown in Figure 4-7.

Figure 4-7. Showing a single record selected by using the search page

Try entering James Smith to see a group of records that have the same first and last names.

A sticky form is used so that when a mistake is made by the administrator, he can try again and his original entry will still be present in the search form.

Now we have reached the stage where we can demonstrate how the administrator deletes and edits the records.

Editing Records

When the administrator clicks the Edit link on the record displayed, he is connected to the edit_record.php page. The page displays the fields and the data currently held in the database table. This is illustrated in Figure 4-8.

Figure 4-8. The “Edit a Record” screen showing the existing data

The administrator can amend the details and then click the Edit button to complete the editing process.

The code for edit_record.php is shown in Listing 4-8.

Listing 4-8. Creating the Editing Interface (edit_record.php) Note that the listing starts with a session to deny access to unauthorized people:

<?php
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Edit a record</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; }
input.fl-left { float:left; }
#submit { float:left; }
</style>
</head>
<body>
<div id="container">
<?php include("header-admin.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>Edit a Record</h2>
<?php
// After clicking the Edit link in the found_record.php page, the editing interface appears
// The code looks for a valid user ID, either through GET or POST #1
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // From view_users.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission
$id = $_POST['id'];
} else { // If no valid ID, stop the script
echo '<p class="error">This page has been accessed in error</p>';
include ('footer.php');
exit();
}
require ('mysqli_connect.php');
// Has the form been submitted?
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$errors = array();
// Look for the first name
if (empty($_POST['fname'])) {
$errors[] = 'You forgot to enter the first name.';
} else {
$fn = mysqli_real_escape_string($dbcon, trim($_POST['fname']));
}
// Look for the last name
if (empty($_POST['lname'])) {
$errors[] = 'You forgot to enter the last name.';
} else {
$ln = mysqli_real_escape_string($dbcon, trim($_POST['lname']));
}
// Look for the email address
if (empty($_POST['email'])) {
$errors[] = 'You forgot to enter the email address.';
} else {
$e = mysqli_real_escape_string($dbcon, trim($_POST['email']));
}
if (empty($errors)) { // If everything is OK, make the update query #2
// Check that the email is not already in the users table
$q = "UPDATE users SET fname='$fn', lname='$ln', email='$e' WHERE user_id=$id LIMIT 1";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_affected_rows($dbcon) == 1) { // If it ran OK
// Echo a message if the edit was satisfactory
echo '<h3>The user has been edited.</h3>';
} else { // Echo a message if the query failed
echo '<p class="error">The user could not be edited due to a system error.
We apologize for any inconvenience.</p>'; // Error message.
echo '<p>' . mysqli_error($dbcon) . '<br />Query: ' . $q . '</p>'; // Debugging message.
}
} else { // If the email address is already registered
echo '<p class="error">The email address has already been registered.</p>';
}
} else { // Display the errors.
echo '<p class="error">The following error(s) occurred:<br />';
foreach ($errors as $msg) { // Echo each error.
echo " - $msg<br />\n";
}
echo '</p><p>Please try again.</p>';

} // End of if (empty($errors))section
} // End of the conditionals
// Select the record #3
$q = "SELECT fname, lname, email FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_num_rows($result) == 1) { // Valid user ID, display the form.
// Get the user's information
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Create the form
echo '<form action="edit_record.php" method="post">

<p><label class="label" for="fname">First Name:</label>
<input class="fl-left" id="fname" type="text" name="fname" size="30" maxlength="30"
value="' . $row[0] . '"></p>

<br><p><label class="label" for="lname">Last Name:</label>
<input class="fl-left" type="text" name="lname" size="30" maxlength="40"
value="' . $row[1] . '"></p>

<br><p><label class="label" for="email">Email Address:</label>
<input class="fl-left" type="text" name="email" size="30" maxlength="50"
value="' . $row[2] . '"></p>

<br><p><input id="submit" type="submit" name="submit" value="Edit"></p>
<br><input type="hidden" name="id" value="' . $id . '" /> #4
</form>';
} else { // The record could not be validated
echo '<p class="error">This page has been accessed in error</p>';
}
mysqli_close($dbcon);
include ('footer.php');
?>
</div>
</div>
</body>
</html>

Explanation of the Code

Most of the code has been explained in earlier chapters, but we will now examine some code that is new to this chapter:

// The code looks for a valid user ID, either through GET or POST #1
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { // Sent from view_users.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) {// If form submitted
$id = $_POST['id'];
} else { // If no valid ID, stop the script
echo '<p class="error">This page has been accessed in error.</p>';
include ('footer.php');
exit();
}

We must be sure we are editing the right record, and this is achieved by checking the id of the selected user. If the user’s details were entered incorrectly or the user did not exist in the database table, an error message is shown. Note the is_numeric() function that ensures that the id is a number and therefore genuine. If the id is valid, it is assigned to the variable $id ready for the next step.

if (empty($errors)) { // If everything's OK make the update query #2
// Check that the email is not already in the users table
$q = "UPDATE users SET fname='$fn', lname='$ln', email='$e' WHERE user_id=$id LIMIT 1";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_affected_rows($dbcon) == 1) { // If it ran OK
// Echo a message if the edit was satisfactory
echo '<h3>The user has been edited.</h3>';
} else { // Echo a message if the query failed
echo '<p class="error">The user could not be edited due to a system error.
We apologize for any inconvenience.</p>'; // Message
echo '<p>' . mysqli_error($dbcon) . '<br />Query: ' . $q . '</p>';
// Debugging message
}

If the error array contains no error messages, the query will run. Using the keyword UPDATE, the query updates the record in the users table for the record that has the correct user_id. Using the keyword LIMIT with the number 1 ensures that only one record is updated.

// Select the record #3
$q = "SELECT fname, lname, email FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon, $q);
if (mysqli_num_rows($result) == 1) { // If valid user ID, display the form
// Get the user's information
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Create the form
echo '<form action="edit_record.php" method="post">

<p><label class="label" for="fname">First Name:</label>
<input class="fl-left" id="fname" type="text" name="fname" size="30" maxlength="30"
value="' . $row[0] . '"></p>

<br><p><label class="label" for="lname">Last Name:</label>
<input class="fl-left" type="text" name="lname" size="30" maxlength="40"
value="' . $row[1] . '"></p>

<br><p><label class="label" for="email">Email Address:</label>
<input class="fl-left" type="text" name="email" size="30" maxlength="50"
value="' . $row[2] . '"></p>

<br><p><input id="submit" type="submit" name="submit" value="Edit"></p>

The form containing the selected user’s data is displayed ready for editing.

<br><inputtype="hidden"name="id" value="' . $id . '" /> #4
</form>';
} else { // The record could not be validated
echo '<p class="error">This page has been accessed in error.</p>';
}
mysqli_close($dbcon);
include ('footer.php');
?>

The type hidden (shown in bold in the preceding code) ensures that no field for the user_id is displayed in the form. Any item that is automatically added by the database, such as the user’s id, must be hidden from the user.

Deleting Records

No special precautions were taken in the previous Edit Record tutorial to prevent the administrator from editing the wrong record by mistake; this was because a mistake could be easily corrected. However, if the administrator deletes the wrong record, he cannot undo the delete; therefore, he must be given another chance to check whether he is deleting the correct record. When the administrator clicks the Delete link on a displayed record, he is connected to the delete_record.php page. The Delete a Record screen displays the name of the person the administrator is about to delete. The administrator can then click either the Yes button or the No button, as shown in Figure 4-9.

Figure 4-9. The screen for deleting a record

The page displays the name of the member to be deleted and gives the administrator the chance to change his mind. Before we describe the listing for the delete page, a few more names are suggested in Table 4-6. Register them so that you can try deleting them.

Table 4-6. Some Names You Can Register and Then Try Deleting

In the next step, we will create a page for the administrator so that he can safely delete records. (See Listing 4-9.)

Listing 4-9. Creating a Page for Deleting Records (delete_record.php)

The page is protected from unauthorized people by means of a session.

<?php
session_start();
if (!isset($_SESSION['user_level']) or ($_SESSION['user_level'] != 1))
{
header("Location: login.php");
exit();
}
?>
<!doctype html>
<html lang=en>
<head>
<title>Delete a record</title>
<meta charset=utf-8>
<link rel="stylesheet" type="text/css" href="includes.css">
<style type="text/css">
p { text-align:center; }
form { text-align:center; }
input.fl-left { float:left; }
#submit-yes { float:left; margin-left:220px; }
#submit-no { float:left; margin-left:20px; }
</style>
</head>
<body>
<div id="container">
<?php include("header-admin.php"); ?>
<?php include("nav.php"); ?>
<?php include("info-col.php"); ?>
<div id="content">
<h2>Delete a Record</h2>
<?php
// Check for a valid user ID, through GET or POST
if ( (isset($_GET['id'])) && (is_numeric($_GET['id'])) ) { //Details from view_users.php
$id = $_GET['id'];
} elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission #1
$id = $_POST['id'];
} else { // If no valid ID, stop the script
echo '<p class="error">This page has been accessed in error.</p>';
include ('footer.php');
exit();
}
require ('mysqli_connect.php');
// Has the form been submitted? #2
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if ($_POST['sure'] == 'Yes') { // Delete the record
// Make the query
$q = "DELETE FROM users WHERE user_id=$id LIMIT 1";
$result = @mysqli_query ($dbcon , $q);
if (mysqli_affected_rows($dbcon ) == 1) { // If there was no problem
// Display a message
echo '<h3>The record has been deleted.</h3>';
} else { // If the query failed to run
echo '<p class="error">The record could not be deleted.<br>Probably
because it does not exist or due to a system error.</p>'; // Display error message
echo '<p>' . mysqli_error($dbcon ) . '<br />Query: ' . $q . '</p>';
// Debugging message
}
} else { // Confirmation that the record was not deleted
echo '<h3>The user has NOT been deleted.</h3>';
}
} else { // Display the form
// Retrieve the member's data #3
$q = "SELECT CONCAT(fname, ' ', lname) FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon , $q);
if (mysqli_num_rows($result) == 1) { // Valid user ID, show the form
// Get the member's data
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Display the name of the member being deleted
echo "<h3>Are you sure you want to permanently delete $row[0]?</h3>";
// Display the delete page
...echo '<form action="delete_record.php" method="post"> #4
<input id="submit-yes" type="submit" name="sure" value="Yes">
<input id="submit-no" type="submit" name="sure" value="No">
<input type="hidden" name="id" value="' . $id . '">
</form>';
} else { // Not a valid member’s ID
echo '<p class="error">This page has been accessed in error.</p>';
echo '<p> </p>';
}
} // End of the main conditional section
mysqli_close($dbcon );
echo '<p> </p>';
include ('footer.php');
?>
</div>
</div>
</body>
</html>

Explanation of the Code

elseif ( (isset($_POST['id'])) && (is_numeric($_POST['id'])) ) { // Form submission #1
$id = $_POST['id'];

If the administrator’s id is available and it is numeric, assign it to the variable $id.

// Has the form been submitted? #2
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
if ($_POST['sure'] == 'Yes') { // Delete the record
// Make the query
$q = "DELETE FROM users WHERE user_id=$id LIMIT 1";
$result = @mysqli_query ($dbcon , $q);
if (mysqli_affected_rows($dbcon ) == 1) { // If there was no problem
// Echo a message
echo '<h3>The record has been deleted.</h3>';
}

A check is made to see if the YES button has been clicked. If it has, the SQL code for deleting a record is run; if it is successful, a message is displayed.

// Display the delete page
// Retrieve the member's data #3
$q = "SELECT CONCAT(fname, ' ', lname) FROM users WHERE user_id=$id";
$result = @mysqli_query ($dbcon , $q);
if (mysqli_num_rows($result) == 1) { // Valid user ID, show the form
// Get the member's data
$row = mysqli_fetch_array ($result, MYSQLI_NUM);
// Display the name of the member being deleted
echo "<h3>Are you sure you want to permanently delete $row[0]?</h3>";

This code displays the “deleted” page. The member’s details are retrieved and used to show a message that asks the administrator if he really wants to delete the record.

echo
'<form action="delete_record.php" method="post"> #4
<input id="submit-yes" type="submit" name="sure" value="Yes">
<input id="submit-no" type="submit" name="sure" value="No">
<input type="hidden" name="id" value="' . $id . '">
</form>';

The Yes and No buttons are displayed. They are positioned by means of the internal CSS style in the <head> section of the file. A reasonable space (a margin) is created between the buttons to avoid mistakenly clicking the wrong button. The result of clicking one of these buttons is to send the value Yes or No to the preceding block of code numbered #2.

Note Only files that needed modifying have been described to save space. Many PHP files for this chapter have not been mentioned because they are exactly the same as the files in Chapter 3. The downloaded files for Chapter 4 can be viewed to see the code for all the files.

Summary

In this chapter, you discovered how to create a user-friendly method of allowing an administrator to amend the contents of a database. This included the ability to paginate the whole table of members. A search form was described, and this can be used for selecting specific members so that their record can be amended or deleted. Code was provided that allowed the administrator to edit and delete records. In the next chapter. we will explore a database with a few extra features. For instance, we will include the user’s title and require the user’s postal address and telephone number. Additional validation and security devices will also be introduced. More redundant buttons will be purged from the headers.