Need help with sorting results after a search

RyanMinor · December 23, 2010

I have a page that I am using to search a database. Basically what I want to do is allow the end user to sort by column headings after the search results are returned. The sort code that I am using works fine on a "lesser complicated" page that does not involve $_GET variables. I am not sure however if I adapted it properly. Also, I am having problems adding the new $_GET variables to the URL string when the user chooses to sort the returned search results. Thank you in advance for any suggestions. My code is below:

<?php 
//////////////////// INITIATE SESSION ////////////////////
if (!isset($_SESSION)) {
session_start();
}
//////////////////// ALLOW ACCESS ONLY TO ADMINISTRATORS ////////////////////
if ($_SESSION['person_priveleges'] == 0) {
header("location:index.php");
}
//////////////////// CONNECT AND SELECT DATABASE ////////////////////
require_once('connect.php');
mysql_select_db($database, $connect);
//////////////////// GET STATES ////////////////////
$state = mysql_query("SELECT state_abbreviation FROM state ORDER BY state_abbreviation ASC", $connect);
$data_state = mysql_fetch_assoc($state);
$rows_state = mysql_num_rows($state);




//////////////////// GET USER SEARCH RESULTS ////////////////////
if (array_key_exists('search', $_GET)) {
$error = array();
if ((empty($_GET['last_name_search'])) && (empty($_GET['organization_search'])) && (empty($_GET['state_search'])) && (empty($_GET['payment_status']))) {
	$error['search'] = "Please enter at least one search parameter.";
} else {
	$search = "SELECT * FROM person";
	$where = false;
	if (isset($_GET['last_name_search']) && !empty($_GET['last_name_search'])) {
		$last_name_search = $_GET['last_name_search'];
		$search .= " WHERE person_last_name LIKE '%$last_name_search%'";
		$where = true;
	} 
	if (isset($_GET['organization_search']) && !empty($_GET['organization_search'])) {
		$organization_search = $_GET['organization_search'];
		if ($where) {
			$search .= ' AND ';
  			} else {
			$search .= ' WHERE ';
			$where = true;
  			}
		$search .= " person_organization LIKE '%$organization_search%'";
	}
	if (isset($_GET['state_search']) && !empty($_GET['state_search'])) {
		$state_search = $_GET['state_search'];
		if ($where) {
			$search .= ' AND ';
  			} else {
			$search .= ' WHERE ';
			$where = true;
  			}
		$search .= " person_state LIKE '%$state_search%'";
	}
	if (isset($_GET['payment_status']) && !empty($_GET['payment_status'])) {
		$payment_search = $_GET['payment_status'];
		if ($where) {
			$search .= ' AND ';
  			} else {
			$search .= ' WHERE ';
			$where = true;
  			}
		$search .= " person_paid LIKE '%$payment_search%'";
	}

	if (isset($_GET['sort'])) {
		$sort = mysql_real_escape_string($_GET['sort']);
		if (strcasecmp($sort, "DESC") == 0) {
			$new_sort = "ASC";
		} else {
			$new_sort = "DESC";
		}
	} else {
		$sort = "ASC";
		$new_sort = "DESC";
	}
	if (isset($_GET['order'])) {
		$order = mysql_real_escape_string($_GET['order']);
		$search .= " AND person_priveleges = 0 ORDER BY $order";

		if (strcasecmp($sort, "DESC") == 0) {
			$search .= " DESC";
		} else {
			$search .= " ASC";
		}
	} else {
		$search .= " AND person_priveleges = 0 ORDER BY person_last_name";
		$query_search = mysql_query($search, $connect) or die(mysql_error());
		$result_search = mysql_fetch_assoc($query_search);
		$number_search = mysql_num_rows($query_search);
		$count = 0;
	}
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>2011 IDUES Project Directors Meeting</title>
<link href="layout.css" rel="stylesheet" type="text/css" />
</head>

<body class="twoColFixLtHdr">
<br />

<!--container starts here-->
<div id="container">

<?php include('header.php'); ?>

<?php include('menu_admin.php'); ?>

<?php include('sidebar_admin.php'); ?>

<!--main content starts here-->
<div id="mainContent">
<h2 class="page_title">REGISTRANT SEARCH</h2>

<div align="center"><?php if (isset($error)) {
echo '<div align="center"><span class="contact_us_error">';
foreach ($error as $alert) {
	echo "$alert<br />\n";
}
echo '</div></span>';
} ?></div>

<form id="search" name="search" method="get" action="">
<table width="60%" border="0" cellspacing="0" cellpadding="5">
<tr>
<td class="black_font"><label>Search by Last Name:</label></td>
<td class="black_font"><input type="text" name="last_name_search" id="last_name_search" /></td>
</tr>
<tr>
<td class="black_font"><label>Search by Organization:</label></td>
<td class="black_font"><input type="text" name="organization_search" id="organization_search" /></td>
</tr>
<tr>
<td class="black_font"><label>Search by State:</label></td>
<td class="black_font"><select name="state_search" id="state_search">
    <option value="">Select State</option>
<?php do { ?>
	<option value="<?php echo $data_state['state_abbreviation']?>"
	<?php if (isset($_POST['state']) && $_POST['state'] == $data_state['state_abbreviation']) {
		echo 'selected="selected"';
	}
	echo $data_state['state_abbreviation']; ?>><?php echo $data_state['state_abbreviation']?></option>
<?php } while ($data_state = mysql_fetch_assoc($state));
$rows = mysql_num_rows($state);
if ($rows > 0) {
	mysql_data_seek($state, 0);
	$data_state = mysql_fetch_assoc($state);
} ?>
</select></td>
</tr>
<tr>
  <td valign="top" class="black_font">Search by Payment Status:</td>
  <td class="black_font"><p>
    <label>
      <input type="radio" name="payment_status" value="Yes" id="payment_status_0" />
      Paid</label>
    <br />
    <label>
      <input type="radio" name="payment_status" value="Pending" id="payment_status_1" />
      Pending</label>
    <br />
    <label>
      <input type="radio" name="payment_status" value="No" id="payment_status_2" />
      Not Paid</label>
    <br />
  </p></td>
</tr>
</table>
<input type="submit" name="search" id="search" value="Search" />
</form>

<br />

<?php if (array_key_exists('search', $_GET) && !$error) { ?>

<?php if ($number_search == 0) 
{ ?> 
<div align="center" class="contact_us_error">No results match your search</div>
<span class="black_font">
<?php } else { ?> 

<strong>Total Search Results: <?php echo $number_search; ?></strong>

<br />

</span>

<table width="100%" border="0" align="center" cellpadding="5" cellspacing="0">
  <tr>
    <td bgcolor="#CCCCCC" class="black_font"> </td>
    <td bgcolor="#CCCCCC" class="black_font"><strong><a href="<?php echo $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING']."?order=person_last_name&sort=$new_sort"; ?>">Name</a></strong></td>
    <td bgcolor="#CCCCCC" class="black_font"><strong><a href="<?php echo $_SERVER['PHP_SELF']."?order=person_organization&sort=$new_sort"; ?>">Organization</a></strong></td>
    <td bgcolor="#CCCCCC" class="black_font"><strong><a href="<?php echo $_SERVER['PHP_SELF']."?order=person_state&sort=$new_sort"; ?>">State</a></strong></td>
    <td align="left" bgcolor="#CCCCCC" class="black_font"><strong><?php if ((isset($_GET['payment_status'])) && ($_GET['payment_status'] == 'Yes')) { ?> <a href="<?php echo $_SERVER['PHP_SELF']."?order=person_payment_method&sort=$new_sort"; ?>">Payment</a></strong></td> <?php } else { echo "Paid</strong></td>"; } ?>
  </tr>
<tr>
<?php do { ?>
<?php $count++; ?>
<?php $person_id = $result_search['person_id']; ?>
<?php $first_name = $result_search['person_first_name']; ?>
<?php $last_name = $result_search['person_last_name']; ?>
<?php $organization = stripslashes($result_search['person_organization']); ?>
<?php $state = $result_search['person_state']; ?>
<?php $paid = $result_search['person_paid']; ?>
<?php $payment_method = $result_search['person_payment_method']; ?>
<td class="black_font"><?php echo $count; ?></td>
    <td class="black_font"><?php echo "<a href='registrant_info.php?id=$person_id'>$last_name, $first_name</a>"; ?></td>
    <td class="black_font"><?php echo stripslashes($organization); ?></td>
    <td class="black_font"><?php echo $state; ?></td>
    <td class="black_font"><?php if ($paid =='Yes') { echo $payment_method; } else { echo $paid; } ?></td>
</tr>
<?php } while ($result_search = mysql_fetch_assoc($query_search)); ?>
</table>

<span class="black_font">
<?php } ?>
<?php } ?>
</span><br />
<br />
<br />
<br />
<br />
<br />
</div>
<!--main content ends here-->

<!--clearing element should immediately follow #mainContent div to force the #container div to contain all child floats-->
<br class="clearfloat" />

<?php include('footer.php'); ?>

</div>
<!--container ends here-->

</body>
</html>

RyanMinor · December 24, 2010

Bump for Reply

RyanMinor · December 27, 2010

I just wanted to bump this up to see if anybody has any ideas.

XandarX · December 27, 2010

Simple. You need to make the ORDER BY a string rather than static in your query.

In the form have a hidden field.

Then on the query:

$state = mysql_query("SELECT state_abbreviation FROM state ORDER BY $order ASC", $connect);

Sign In

Need help with sorting results after a search

Recommended Posts

RyanMinor

Link to comment

Share on other sites

RyanMinor

Link to comment

Share on other sites

RyanMinor

Link to comment

Share on other sites

XandarX

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information