Code:
<?php
session_start();
if (!($_POST['l_name'] || $_POST['f_name'] || $_POST['city']
|| $_POST['gender'] || isset($_GET['pageno']))) {
include('incl/headerDirectory.htm');
echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
<h2>The form fields were empty.</h2>
<p>Please return to
<a href=\"directory2.php\">our directory page</a>
and select your search criteria.</p>
</div><!-- end div directoryPreso -->
</div><!-- end contentMain -->
";
include('incl/footer.htm');
echo"</div><!-- end wholePage --> </body></html>";
exit();
} /* end if, and end header and general stuff */
$conn = mysql_connect("serverName", "login", "pswrd") or die(mysql_error());
$db = mysql_select_db("dbName", $conn) or die(mysql_error());
// SELECT query courtesy r937
//(I removed OUTERs to see if the query still works :-)
$sql = "SELECT l_name, f_name, city, num, address
FROM names
LEFT JOIN IDcity ON names.mem_id = IDcity.mem_id
LEFT JOIN cities ON IDcity.city_id = cities.city_id
LEFT JOIN IDphone ON names.mem_id = IDphone.mem_id
LEFT JOIN phone ON IDphone.phone_id = phone.phone_id
LEFT JOIN IDemail ON names.mem_id = IDemail.mem_id
LEFT JOIN email ON IDemail.email_id = email.email_id
WHERE ";
$orderBy = " ORDER BY l_name, f_name"; // the default sort order
if($_POST['l_name']) { // if the visitor entered a last name
$l_name = trim(mysql_real_escape_string($_POST['l_name']));
$l_name = ucfirst(strtolower($l_name));
$isValid = isValidLastName($l_name); //isValid functions near the bottom
if(!$isValid) {
//return the visitor to the form page to try again;
}
// if there is a * at the end of l_name, then replace it with % wildcard
$l_nameLength = strlen($l_name);
if(substr_count($l_name, '*', $l_nameLength-1, 1)) {
$l_name = preg_replace('/[*]/', '%', $l_name);
}
$wildCard = hasWildCard($l_name); //hasWildCard function is below
if($wildCard) {
$whereClause="l_name LIKE '$l_name'";
} else {
$whereClause="l_name = '$l_name'";
}
}
if($_POST['f_name']) { // if the visitor entered a first name
$f_name = trim(mysql_real_escape_string($_POST['f_name']));
$f_name = ucfirst(strtolower($f_name));
$isValid = isValidFirstName($f_name); //isValid functions are below
if(!$isValid) {
//return the visitor to the form page; (will write the code later)
}
// if there is a * at the end of f_name, then replace it with % wildcard
$f_nameLength = strlen($f_name);
if(substr_count($f_name, '*', $f_nameLength-1, 1)) {
$f_name = preg_replace('/[*]/', '%', $f_name);
}
// Check to see if there's a wild card at the end of f_name.
$wildCard = hasWildCard($f_name); //hasWildCard() function id below
if($wildCard) {
if($_POST['l_name']) {
$whereClause.=" AND f_name LIKE '$f_name'";
}
else {
$whereClause="f_name LIKE '$f_name'";
}
}
else { // if f_name has no wild card...
$hasNickName = nickName($f_name); // does f_name have a nickName?
if($hasNickName) {
if(!($_POST['l_name'])) { // if there is a nickName and no last name
$whereClause = $hasNickName;
}
else { // when there is a nickName and a last name
$whereClause.= " AND f_name = '$f_name'";
}
}
else { // if there is no nickName:
if($_POST['l_name']) {
$whereClause.=" AND f_name = '$f_name'";
}
else {
$whereClause="f_name = '$f_name'";
}
}
}
}
//check to see if visitor entered a city name in the form
if($_POST['city']) {
$city = trim(mysql_real_escape_string($_POST['city']));
$city = ucwords(strtolower($city));
$thisCity = checkCity($city); //checkCity() function near line 248
if(!($thisCity)) {
$msg = "Our records indicate that no current club member lives in
".$city.". Carefully check your spelling and adjust your search
accordingly."; //I'll use the message later
}
if($_POST['l_name'] || $_POST['f_name']) {
$whereClause .= " AND city = '$city'";
}
else {
$whereClause = "city = '$city'";
}
}
//check to see if visitor is searching by gender
if($_POST['gender']) {
$gender = mysql_real_escape_string($_POST['gender']);
if($_POST['l_name'] || $_POST['f_name'] || $_POST['city']) {
if($_POST['gender'] != "a") {
$whereClause .=" AND gender = '$gender'";
}
else {
$whereClause .= " AND gender IN ('m', 'f')";
}
}
else {
if($gender != "a") {
$whereClause = "gender = '$gender'";
}
else {
$whereClause = "gender IN ('m', 'f')";
}
}
}
//begin pagination code
//links displayed only if more than 20 records returned)
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
}
else {
$pageno = 1;
}
if(!(isset($_SESSION['whereClause']))) {
$_SESSION['whereClause'] = $whereClause;
}
else { // if $_SESSION['whereClause'] already has a value (possibly
// empty...)
if($_SESSION['whereClause'] != $whereClause) {
// if safe, update the SESSION variable with the new value of
// whereClause
if( $whereClause != "") {
$_SESSION['whereClause'] = $whereClause;
}
}
}
$_SESSION['f_name'] = $f_name; //these three are defined
$_SESSION['l_name'] = $l_name; //so that the form fields
$_SESSION['city'] = $city; //can retain their values
$query = "SELECT count(*) FROM names WHERE
".$_SESSION['whereClause'];
$result = mysql_query($query, $conn) or die(mysql_error());
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];
$rows_per_page = 20;
$lastpage = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno > $lastpage) {$pageno = $lastpage;}
if ($pageno < 1) {$pageno = 1;}
$limit = " LIMIT ".($pageno - 1) * $rows_per_page.", ".$rows_per_page;
/*********************************************
* Here's where the main SELECT statement is constructed *
*********************************************/
$_SESSION['sql'] = $sql . $_SESSION['whereClause'] . $orderBy . $limit;
$sql_result = mysql_query($_SESSION['sql'], $conn) or die(mysql_error());
$num_rows = mysql_num_rows($sql_result);
if ($num_rows == 0) {
include('incl/headerDirectory.htm');
echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
<h3>Sorry about that...</h3><p style=\"border-bottom:none\">
We found no members that satisfy your search.
Please return to <a href=\"directory2.php\">our directory page</a>
and perhaps try different search criteria, or use fewer criteria
to broaden your search.</p>
";
echo"</div><!-- end div direcPreso --></div><!-- end contentMain -->";
include('incl/footer.htm');
echo"</div><!-- end wholePage --></body></html>";
exit();
}
if ($sql_result) { //this if closes about 62 lines below here...
if ($num_rows == 1) {$member = "member";}
else {$member = "members";} //for outputting number of records below.
include('incl/headerDirectory.htm');
echo"<div id=\"contentMain\"><div id=\"directoryPreso\">
<h1>We found ".$numrows." ".$member."</h1><p id=\"theNames\">
Click a <span style=\"color:blue; text-decoration:underline\">blue first
name</span> to e-mail that person.</p>
";
// Print pagination links only if there are more than $rows_per_page rows
if($numrows > $rows_per_page) {
echo"<p style=\"text-align:center; border-bottom:0\">";
if($pageno == 1) {
echo "First Prev ";
}
else {
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=1'>First</a>
";
$prevpage = $pageno - 1;
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>Prev</a>
";
}
echo " (Page $pageno of $lastpage) ";
if($pageno == $lastpage) {
echo " Next Last ";
}
else {
$nextpage = $pageno + 1;
echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>
Next</a>
";
echo "<a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>Last</a>";
}
echo "</p>";
}
// Print the four column headings of the query results table.
echo"<table id=\"presoTable\"><tr><th>Last Name</th><th>First
Name</th><th>City</th><th>Phone Number</th></tr>
";
//init color flag; alternate color by li.odd/even defined in css
$rowColor = "odd";
while ($row = mysql_fetch_array($sql_result)) {
//if the person has an email address in the db, link it to their first name
if($row['address']!= NULL) {
$email_fName =
"<a href=\"mailto:".$row['address']."\">".$row['f_name']."</a>";
}
else { //if the person has no email address, just print their first name
$email_fName = $row['f_name'];
} // print first and last name, city and phone to the results table.
echo "<tr class=\"$rowColor\"><td>".$row['l_name']."</td><td>".
$email_fName."</td><td>".$row['city']."</td> <td>".$row['num'].
</td></tr>
";
$rowColor = ($rowColor == "odd") ? "even" : "odd";
} //close the table and write the Return link to the form page.
echo "</table><p id=\"return\">Return to the <a href=\"directory2.php\">
Directory page.</a></p>
";
} //this closes the big if($sql_result) construct
echo"</div><!-- end directoryPreso div --></div>
<!-- end contentMain -->
";
include('incl/footer.htm');echo"</div></body></html>";
function isValidLastName($alphaIn) {
if(preg_match('/[A-z]+[ \'-]?([ A-z]*){0,3}[*%]?/', $alphaIn)) {
return 1;
}
else return 0;
}
function isValidFirstName($alphaIn) {
if(preg_match('/[A-z]+[\'-]?[A-z]*[*%]?/', $alphaIn)) {
return 1;
}
else return 0;
}
function hasWildCard($nameIn) {
if(preg_match('/^[A-z \'-]+%/', $nameIn)) {
return 1;
}
else return 0;
}
function checkCity($cityIn) {
$cityIn = ucwords(strtolower($cityIn));
$cities = array('Alamo', 'Antioch', 'Atascadero', ... about fifty cities here);
if(!(in_array($cityIn, $cities))) {
return false;
}
}
function nickName($nameIn) {
$arr[0] = array(Bob, Robert, Rob, Bobby);
$arr[1] = array(Chuck, Charles, Charlie);
$arr[2] = array(Chris, Christopher);
.
. about seventy arrays used for finding and returning names similar to the
name the visitor entered in the First Name field of the form.
$arr[75] = array(Curtis, Curt, Kurt);
$whereString = "f_name IN ('"; // initialize whereString
for($i=0; $i < 76; $i++) { // and search each of the mini arrays,
// looking for a match to f_name
$arrLen = count($arr[$i]);
for($j=0; $j < $arrLen; $j++) {
if($arr[$i][$j] == $nameIn) { // if you find a match, append each name
// in that mini array to $whereString
for($k=0; $k < $arrLen; $k++) {
if($k < $arrLen - 1) {
$whereString .= $arr[$i][$k]."', '";
}
else { // After the final name is added to the IN clause, properly
$whereString .= $arr[$i][$k]."')";
return $whereString;
}
}
}
}
} // this one closes for($j=0;...
} // this one closes function nickName()
?>
Dan Schulz you will be missed 
Originally Posted by cstallins
Bookmarks