Help with searching and displaying data

kira148

I'm a bit of a noob at PHP and I'm trying to learn as much as I can in context to what I'm doing. So a little background:

I've got this phone listing for the place that I work. It's housed in a mysql database and I've got a page that searches it.

Form to Search:

<!-- Search by Last Name form -->
<form name="search" method="post" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
<label for="find">Seach by Last Name:</label> <input type="text" name="find" />
<input type="hidden" name="field" value="last_name" />
<input type="hidden" name="hidden" value="yes" />
<input type="hidden" name="hidden2" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<br />
<!-- Choose a department form -->
<form name="search" method="post" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
<label for="find">Seach by Department:</label> 
<select name="find"> 
<option selected="yes"></option>
<option value="Administration">Administration</option>
<option value="Art">Art</option>
<option value="Athletics">Athletics</option>
<option value="Band">Band</option>
<option value="Business">Business</option>
<option value="Chorus">Chorus</option>
<option value="Culinary">Culinary</option>
<option value="Custodial">Custodial</option>
<option value="Drafting and Design">Drafting & Design</option>
<option value="Drivers Ed">Drivers Ed</option>
<option value="English">English</option>
<option value="ESE">ESE</option>
<option value="Foreign Language">Foreign Language</option>
<option value="Guidance">Guidance</option>
<option value="Health Care District">School Nurse</option>
<option value="Information Technology">IT</option>
<option value="Math">Math</option>
<option value="Media">Media</option>
<option value="Medical Academy">Medical Science</option>
<option value="Office Staff">Office Staff</option>
<option value="Psychology">Psychologist</option>
<option value="Reading">Reading</option>
<option value="School Food Service">Cafeteria</option>
<option value="School Police">School Police</option>
<option value="Science">Science</option>
<option value="Social Studies">Social Studies</option>
<option value="Theater">Theater</option>
</select>
<input type="hidden" name="field" value="department" />
<input type="hidden" name="hidden" value="yes" />
<input type="hidden" name="hidden2" value="yes" />
<input type="submit" name="search" value="Search" />
</form></center>

PHP Code:

<?php 

//This is only displayed if they have submitted the form 
if (isset($_POST['hidden2']))
	{
$hidden = $_POST['hidden'];
$find = $_POST['find'];
$field = $_POST['field'];
if ($hidden =="yes") {  

echo "<table width='900'><tr><td valign='top' class='noBorder'>Employee Listing for <b>$find.</b></td></tr></table>";

//If they did not enter a search term we give them an error 
if ($find == "") { 
echo "You forgot to enter a search term"; 
} 
else{
// Otherwise we connect to our Database 

$con = mysql_connect("localhost","root","");
 if (!$con)
  {
 die('Could not connect: ' . mysql_error());
 }
mysql_select_db("phonelist", $con);
// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

$result = mysql_query("SELECT * FROM listing  WHERE $field LIKE '%$find%'");

// Create table heads
echo "<table cellpadding='0' cellspacing='0' width='100%' align='center'><tr><td align='center' colspan='2' border='0'><b>--- Search Results ---</b></td></tr>";

while($row = mysql_fetch_array( $result)) { 
	//add values inside table
echo "<tr><td valign=\"top\">".'<b>' . $row['last_name'] . "," . " " .$row['first_name'] . '</b>' . "<br />" . $row['teacher_number'] . "<br />" . $row['department'] . "</td><td align=\"right\" valign=\"top\">". 'PX:' . ' ' . '<b>' . $row['px_number'] . '</b>' . "<br />". 'Room / PA Number:' . ' ' . $row['room_number'] . "<br />". 'Planning Period:' . ' ' . $row['planning_period'] . "</td></tr>";
echo "<tr><td colspan=\"2\"><HR><td></tr>";
echo "<tr><td colspan=\"2\">&nbsp;<td></tr>";

}
//End Table
echo "</table>";

} 
}
}
?>

I've been happy with it up until now but I'm trying to expand on it. I want to add links for each letter of the alphabet so when they click one it performs the same functions as above but instead of

$result = mysql_query("SELECT * FROM listing  WHERE $field LIKE '%$find%'");

It'll use a different query. For instance

"select * from listing where last_name like \'A%\' ";

I've been banging my head against a wall to understand how it's done but I'm a visual learner and without example I have a hard time understanding. Could anyone offer some insight on how I could incorporate that into my existing script?

Thanks in advance to anyone who responds. Have a great day!

sneakyimp

You'll need to create a link for each letter of the alphabet. You can do this manually or you can do it with this loop

$links = array();
for($i=ord('A'); $i <= ord('Z'); $i++) {
  $links[] = '<a href="' . $_SERVER['PHP_SELF'] . '?letter=' . chr($i) . '">' . chr($i) . '</a>';
} 
echo implode('&nbsp;|&nbsp;', $links);

Each of those links points back to your page. I assume this is what you need to do because your form also uses $_SERVER['PHP_SELF'] as its action.

Now in your PHP logic, you need to take a peek at $GET['letter']. If this value is not empty, you can write your query like so:

if (isset($_GET['letter']) && !empty($_GET['letter'])) {
  // IMPORTANT!  you should probably validate the input to prevent sql injection
  if (!preg_match('/^[a-z]$/i', $_GET['letter'])) die('invalid letter');

  $sql = "select * from listing where last_name like '" . mysql_real_escape_string($_GET['letter']) . "%'";
}

EDIT: made validation check case-insensitive

bradgrafelman

Since I'm lazy and always enjoy doing less typing, note that you could change this:

for($i=ord('A'); $i <= ord('Z'); $i++) {

to this:

foreach(range('A', 'Z') as $letter) {

(and make the appropriate chr($i) => $letter changes as well).

EDIT: So without cheating and changing $letter to something stupid like $l, I suppose it's not a huge savings as far as the # of characters goes, but I still think the second version is easier to "read" at a glance.

sneakyimp

I like it.

stephenmarshall

i have a script here you might like.

Don't know if you want it..

its really good let contact me if you do

kira148

Stephen I seem to be failing miserably at PMing you. I'd love to see what you have. You can email me at joseph.ascanio [at] palmbeachschools.org. That is my work email.

[MOD: I obfuscated your email addy just a bit]

kira148

Thank you guys for posting. I'm going to try some of these as soon as I get into work tomorrow morning.

stephenmarshall

email dont work kira send me an email

[myname].eu
at

G
M
A i have really obfuscated this one
I
L . com