Finding a Record Using the Admin Browser

 

Introduction.  Now we are going to augment our functionality by incorporating some capacity to find records.  At this point we will search only based on last names and we will only find the first record that satisfies some criteria.  From this, it should be relatively clear how this functionality could be augmented to search based on other fields, likely by entering a select/option box.

The other thing we want to do is make use of the MySQL LIKE function so that we can find substrings at the beginning, end or middle of a last name.  We will append/concatenate a % at the beginning and end of our find text in order to increase the adaptability of our find by doing the following.

'%'.$txt_find.'%'

The code for the find is placed just after the other code so that it can share certain variable names such as $row.  But hopefully, the significance of this will become clear as we discuss the code.

As usual we will work with the admin_browser.php.  Again, the new code is highlighted in red.

 

<?php
// assign the values for database access
$host = "localhost";
$user = "your_user_name";
$password = "your_password";
$db_name = "database_name";
$table_name = "user_registration";

session_start( );
// initializing the record count
// and session variable to keep track of current record
if (!isset($_SESSION['current_row']))
{

$_SESSION['current_row'] = 1;

}

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "SELECT * FROM $table_name";
// executing the query
$result_set = mysql_db_query($db_name, $query_string, $link);
// obtaining the number of rows in the record set
$number_rows = mysql_num_rows($result_set);
// Now we have our code that handles the command buttons
// the block of code to move the admin to the previous record
//
// if the First button has been pressed
if ($cmdFirst != "")
{

// setting the row counter to 1
$_SESSION['current_row'] = 1;

}
// the block of code to move the admin to the previous record
// if the Previous button has been pressed
if ($cmdPrevious != "")
{

// making certain the admin is not already at the first record
if ($_SESSION['current_row'] == 1)
{

echo("<font size=5>You are already at the first record!</font>");

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// the block of code to move the admin to the next record
// if the Next button has been pressed
if ($cmdNext != "")
{

// making certain the admin is not already at the last record
if ($_SESSION['current_row'] == $number_rows)
{

echo("<font size=5>You are already at the last record!</font>");

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] + 1;

}

}
// if the Last button has been pressed
if ($cmdLast != "")
{

// setting the row counter to the last record
$_SESSION['current_row'] = $number_rows;

}
// if the Update button has been pressed
if ($cmdUpdate != "")
{

// ensuring the checkboxes have some value
if ($chk_php != 1) $chk_php = "";
if ($chk_jsp != 1) $chk_jsp = "";
if ($chk_mysql != 1) $chk_mysql = "";
// setting a variable to use for the update query
$user_id_number = $_SESSION['current_user_id'];
// developing the update query string based on the user_id
$update_string = "UPDATE $table_name SET
first_name = '$txt_first_name',
last_name = '$txt_last_name',
email = '$txt_email',
password = '$txt_password',
php_interest = '$chk_php',
jsp_interest = '$chk_jsp',
mysql_interest = '$chk_mysql',
credit_card = '$sel_credit_card',
education = '$rb_education'
WHERE (user_id = '$user_id_number')";
// updating the entry
$update_set = mysql_db_query($db_name, $update_string, $link);
// giving the admin a message that the update occurred
echo("<font size=4><b>The update was completed</b></font>");
// change the current record and display based on where you are
if ($_SESSION['current_row'] == 1)
{

$_SESSION['current_row'] == 2;

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// if the Delete button has been pressed
if ($cmdDelete != "")
{

// setting a variable to use for the delete query
$user_id_number = $_SESSION['current_user_id'];
// developing the deletion query string based on the user_id
$delete_string = "DELETE FROM $table_name WHERE (user_id = '$user_id_number')";
// deleting the entry
$delete_set = mysql_db_query($db_name, $delete_string, $link);
// decrementing the total number of entries due to the deletion
$number_rows--;
// change the current record and display based on where you are
if ($_SESSION['current_row'] == 1)
{

$_SESSION['current_row'] = 2;

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// a variable for printing
$current_row_number = $_SESSION['current_row'];
// go to this current row
mysql_data_seek($result_set, $_SESSION['current_row']);
// and retrieve it into an array
$row = mysql_fetch_row($result_set);
// if the Find button has been pressed
if ($cmdFind != "")
{

// appending a % at the beginning and end
// in order to get best use of LIKE function
$txt_find = '%'.$txt_find.'%';
// developing the find query string based on the user_id
$find_string = "SELECT * FROM $table_name WHERE (last_name LIKE '$txt_find')";
// executing the query
$find_set = mysql_db_query($db_name, $find_string, $link);
// change the current record and display based on what you find
if (mysql_num_rows($find_set) > 0)
{

$row = mysql_fetch_row($find_set);
// I need to figure out a way to determine the current row number
// So far I have not found an appropriate built-in function

}
else
{

echo("<font size=4><b>Couldn't find such a record</b></font>");

}

}
?>
<html>
<head>
<title>Admin Browser for User Registration Web</title>
</head>

<body bgcolor = "00bbdd" text="004466" link="004466" vlink="007799">
<form action="admin_browser.php" method=post>
<h2>Admin Browser for User Registration</h2>
<table>
<tr>
<td><font size = 4 color=004466>First Name:</font>
</td>
<td><input type=text name="txt_first_name" size=20 value = "<?php echo $row[1]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>Last Name:</font>
</td>
<td><input type=text name="txt_last_name" size=20 value = "<?php echo $row[2]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>EMail Address:</font>
</td>
<td><input type=text name="txt_email" size=50 value = "<?php echo $row[3]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>Password:</font>
</td>
<td><input type=password name="txt_password" size=50 value = "<?php echo $row[4]; ?>">
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Interests:</font>
</td>
<td><input type=checkbox name="chk_php" value=1
<?php if (($row[5]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>PHP</font>
</td>
</tr>
<tr>
<td>
</td>
<td><input type=checkbox name="chk_jsp" value=1
<?php if (($row[6]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>JSP</font>
</td>
</tr>
<tr>
<td>
</td>
<td><input type=checkbox name="chk_mysql" value=1
<?php if (($row[7]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>MySQL</font>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Credit Card:</font>
</td>
<td><select name="sel_credit_card">
<option value="Discover" <?php if (($row[8]) == 'Discover') echo 'selected'; ?>>Discover
<option value="MasterCard" <?php if (($row[8]) == 'MasterCard') echo 'selected'; ?>>Mastercard
<option value="Visa" <?php if (($row[8]) == 'Visa') echo 'selected'; ?>>Visa
</select>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Education:</font>
</td>
<td><input type=radio name="rb_education" value="NoCollege"
<?php if (($row[9]) == 'NoCollege') echo 'checked'; ?>>
<font size = 4 color=004466>No College</font><br>
<input type=radio name="rb_education" value="College"
<?php if (($row[9]) == 'College') echo 'checked'; ?>>
<font size = 4 color=004466>College</font><br>
<input type=radio name="rb_education" value="Masters"
<?php if (($row[9]) == 'Masters') echo 'checked'; ?>>
<font size = 4 color=004466>Masters</font><br>
<input type=radio name="rb_education" value="PhD"
<?php if (($row[9]) == 'PhD') echo 'checked'; ?>>
<font size = 4 color=004466>Ph.D.</font>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td align = right>
<?php echo("<font size=4>Record $current_row_number</font>"); ?>
</td>
<td align = left>
<?php echo("<font size=4>of $number_rows</font>"); ?>
</td>
</tr>
<tr>
<td align = right>
<input type = submit name="cmdFirst" value="First">
<input type = submit name="cmdPrevious" value="Previous">
</td>
<td align = left>
<input type = submit name="cmdNext" value="Next">
<input type = submit name="cmdLast" value="Last">
</td>
</tr>
<tr>
<td align = right>
<input type = submit name="cmdUpdate" value="Update">
</td>
<td align = left>
<input type = submit name="cmdDelete" value="Delete">
</td>
</tr>
<tr>
<td align = right>
<input type = submit name="cmdFind" value="Find Last Name">
</td>
<td align = left>
<input type = txt name="txt_find" size=15>
</td>
</tr>
</table>
</form>
<?php
// establishing a session variable that can be used to
// obtain the user_id of the record currently displayed
// to be accessed when we return
// this will be important for deletions and modifications
$_SESSION['current_user_id'] = $row[0];
mysql_close($link);
?>
</body>
</html>

 

The form should now look like the following.  Notice there are no changes to the form other than there is an additional Update button in the same row with the Delete button.

 

 

So you should modify the code for your server's connections to MySQL and then upload it and move around.

Code Explanation.  The truly new lines of code are the following.  We will put them in a table in order to put an appropriate discussion in an adjacent cell.

 

// if the Find button has been pressed
if ($cmdFind != "")
{
 

 If the Find button has been pressed
// appending a % at the beginning and end
// in order to get best use of LIKE function
$txt_find = '%'.$txt_find.'%';
 Appending the % operator to the beginning and end to allow for better substring searches.
 

// developing the find query string based on the user_id
$find_string = "SELECT * FROM $table_name WHERE (last_name LIKE '$txt_find')";
 

 Construct the query string to be used for the update. 

Notice how the record selection is determined by the WHERE clause based on LIKE and the augmented $txt_find.
 

// executing the query
$find_set = mysql_db_query($db_name, $find_string, $link);

 Actually execute the query to find such a record.

// updating the entry
$update_set = mysql_db_query($db_name, $update_string, $link);

 

// change the current record and display based on what you find
if (mysql_num_rows($find_set) > 0)
{

$row = mysql_fetch_row($find_set);
// I need to figure out a way to determine the current row number
// So far I have not found an appropriate built-in function

}
else
{

echo("<font size=4><b>Couldn't find such a record</b></font>");

}

}

 If the query returns a result then we take particular actions.

Move to the first row in the resulting $find_set if it has some entries.

Don't move at all and give the admin an appropriate message if no such records are found.

 
<tr>
<td align = right>
<input type = submit name="cmdFind" value="Find Last Name">
</td>
<td align = left>
<input type = txt name="txt_find" size=15>
</td>
</tr>
 		 A new row is placed in the form table which gives an appropriate submit button and text box for the admin to type what the yare looking for.