RyanMinor Posted December 23, 2010 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
RyanMinor Posted December 24, 2010 Author Share Posted December 24, 2010 Bump for Reply Quote Link to comment Share on other sites More sharing options...
RyanMinor Posted December 27, 2010 Author Share Posted December 27, 2010 I just wanted to bump this up to see if anybody has any ideas. Quote Link to comment Share on other sites More sharing options...
XandarX Posted December 27, 2010 Share Posted 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. <input type=hidden name="order" value="state_abbreviation"> Then on the query: $state = mysql_query("SELECT state_abbreviation FROM state ORDER BY $order ASC", $connect); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.