Can someone forward me to a tutorial on how I can create on a web page a sort capability.
There’s this table with the following headings:
Name Address Phone Company Email Position
I need to know how to create a custom sort capability if someone wants to, say, sort by Address. They simply click a SORT button next to one of the headings.
All information is from a database table that has the above information.
Thank you for your help!
Assuming by database you mean a MySQL database this is what you do.
Add a link by each field on your webpage. Each adds a new GET parameter called “sort”.
For example, clicking addresss takes you to example.php?sort=address and clicking email takes you to example.php?sort=email.
You then need to check for this parameter in your database query.
//Database query has already been generated
if($_GET['sort'] == "address")
{
$query .= " SORT BY address ASC";
}
else if($_GET['sort'] == "email")
{
$query .= " SORT BY email ASC";
}
//Continue for each possible parameter
Alternatively you could use switch, but I think the code above does what you were asking.
Something like here? http://www.hawkee.com/snippet/4189/
Here’s a solution that has already been coded up for you.
$query .= " SORT BY address ASC";
do you this SORT BY keyword is there in mysql?
It’s better to post some realistic code so that the newcomer won’t get confused.
@pmw57:
thanks for posting good link.
Oh yeah, what terrible advice I just gave. I should have said ORDER BY.
Looks like I need to find a ‘Search’ capability as well for this same table. Any adivce?
Correct.
It’s okay to call me the village idiot, folks. I’ve been doing coding for quite awhile, but some things I haven’t experienced. This being one.
My code is as follows:
PHP:
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getEmployee = "SELECT empl_id, firstname, lastname, company, `position`, location, phone, email FROM employee_info";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
<?php if ($totalRows_getEmployee == 0) { // Show if recordset empty ?>
<p class="warning">Their currently are no Employees to list</p>
<?php } // Show if recordset empty ?>
<?php if ($totalRows_getEmployee > 0) { // Show if recordset not empty ?>
<table width="98%" border="0" cellspacing="0" cellpadding="0">
<tr style="background: #fef3e6;">
<th style="color: #2e2203; padding-left: 10px;" scope="col"> Name </th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Company</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Position</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Email</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Phone</th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
<?php $counter = 0; ?>
<?php do { ?>
<tr <?php if ($counter++ %2) {echo 'class="hilite"';} ?>>
<td style="padding-left: 10px;" scope="col"><span style="text-transform: uppercase;"><a href="employee.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>"><?php echo $row_getEmployee['firstname']; ?> <?php echo $row_getEmployee['lastname']; ?></a></span></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['company']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['position']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['email']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['phone']; ?></td>
<td scope="col"><a href="update.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">EDIT</a></td>
<td scope="col"><a href="delete.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">DELETE</a></td>
</tr>
<?php } while ($row_getEmployee = mysql_fetch_assoc($getEmployee)); ?>
</table>
<?php } // Show if recordset not empty ?>
Now, trying Coryboy’s method first, you’re asking me to do this:
if($_GET['sort'] == "lastname")
{
$query_getEmployee .= " SORT BY lastname DESC";
}
else if($_GET['sort'] == "company")
{
$query_getEmployee .= " SORT BY company ASC";
}
// So and so forth
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getEmployee = "SELECT empl_id, firstname, lastname, company, `position`, location, phone, email FROM employee_info";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
And then you’re asking me to “Add a link by each field on your webpage. Each adds a new GET parameter called “sort”.”, which would be this, correct:
<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=firstname">Name</a></th>
Please correct me if I’m wrong.
@pmw57: I read the jQuery method, and it seemed very attractive. But since the template was created in PHP (and I’ve been asked to make modifications to it), I’ll try the PHP method first, though I’d like to try the jQuery method just to see what it’s like.
I made a mistake in the code I posted. It’s ORDER BY, not SORT BY.
Sorry, but I’m receiving “Notice: Undefined index: sort…” error within the code:
if($_GET['sort'] == "firstname")
{
$query_getEmployee .= "ORDER BY firstname ASC";
}
else if($_GET['sort'] == "lastname")
{
$query_getEmployee .= "ORDER BY lastname ASC";
}
The form method is set to “GET”.
If the sort type hasn’t been set, that will trigger that notice. One way to deal with that is by first checking if it exists, before assigning it to a variable.
$sort = '';
if (!empty($_GET['sort'])) {
$sort = mysql_real_escape_string($_GET['sort']);
}
$allowedSorts = array('firstname', 'lastname');
if (in_array($sort, $allowedSorts)) {
$query_getEmployee .= 'ORDER BY ' . $sort .' ASC';
}
However, there are better ways of dealing with that now. The filter_input function is a better way to approach his because it can sanitise the input at the same time.
$sort = filter_input(INPUT_GET, 'sort', FILTER_SANITIZE_SPECIAL_CHARS);
$allowedSorts = array('firstname', 'lastname');
if (in_array($sort, $allowedSorts)) {
$query_getEmployee .= 'ORDER BY ' . $sort .' ASC';
}
hmmm…that’s an interesting way of building it. Wish I could say that I ‘completely’ understand it.
I tried this method, clicked on my link to sort, but it didn’t sort correctly.
Almost, however you need to watch the order of your code:
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
// you need to declare your SQL statement first
$query_getEmployee = "SELECT empl_id, firstname, lastname, company, `position`, location, phone, email FROM employee_info";
// the following then adds the ORDER BY command to the end of the SQL statement. It must go after you declare the SQL statement
if($_GET['sort'] == "lastname")
{
$query_getEmployee .= " ORDER BY lastname DESC";
}
else if($_GET['sort'] == "company")
{
$query_getEmployee .= " ORDER BY company ASC";
}
// So and so forth
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
I’ve applied the code, but when I click to sort by Last Name, it doesn’t sort it by last name. No error messages.
This is what I have:
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getEmployee = "SELECT * FROM employee_info";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
if (isset($_POST['sort']))
{
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
// you need to declare your SQL statement first
$query_getEmployee = "SELECT empl_id, firstname, lastname, company, `position`, location, phone, email FROM employee_info";
// the following then adds the ORDER BY command to the end of the SQL statement. It must go after you declare the SQL statement
if($_GET['sort'] == "firstname")
{
$query_getEmployee .= " ORDER BY firstname ASC";
}
else if($_GET['sort'] == "lastname")
{
$query_getEmployee .= " ORDER BY lastname ASC";
}
// So and so forth
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
}
<div id="mainbody">
<?php if ($totalRows_getEmployee == 0) { // Show if recordset empty ?>
<p class="warning">Their currently are no Employees to list</p>
<?php } // Show if recordset empty ?>
<?php if ($totalRows_getEmployee > 0) { // Show if recordset not empty ?>
<form id="search" name="search" method="post" action="">
</p>
<p style="text-align: right;">
<label for="searchbox">Search: </label>
<input name="search" type="text" id="search" size="30" maxlength="50" />
<input name="go" type="submit" id="go" value="Go" />
</p>
</form>
<form id="sortform" name="sortform" method="post" action="">
<table width="98%" border="0" cellspacing="0" cellpadding="0">
<tr style="background: #fef3e6;">
<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=firstname">First Name</a> </th>
<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=lastname">Last Name </a></th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Company</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Position</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Email</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Phone</th>
<th scope="col"> </th>
<th scope="col"> </th>
</tr>
<?php $counter = 0; ?>
<?php do { ?>
<tr <?php if ($counter++ %2) {echo 'class="hilite"';} ?>>
<td style="padding-left: 10px;" scope="col"><span style="text-transform: uppercase;"><a href="employee.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>"><?php echo $row_getEmployee['firstname']; ?></a></span></td>
<td style="padding-left: 10px;" scope="col"><span style="text-transform: uppercase;"><a href="employee.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>"><?php echo $row_getEmployee['lastname']; ?></a></span></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['company']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['position']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['email']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['phone']; ?></td>
<td scope="col"><a href="update.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">EDIT</a></td>
<td scope="col"><a href="delete.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">DELETE</a></td>
</tr>
<?php } while ($row_getEmployee = mysql_fetch_assoc($getEmployee)); ?>
</table></form>
<?php } // Show if recordset not empty ?>
Any other suggestions?
It’s real important to develop some basic debugging skills. Instead of wondering why some complex piece of code “doesn’t work”, you progressively figure it out, by using smaller steps. Otherwise, you will struggle forever.
When you write some code, and it doesn’t work the way you thought it would work, it means you have made an incorrect assumption about the way the code you have written actually works. So, for a moment, forget about making your code “work”, and focus on understanding what the code is actually doing.
You can generally narrow down to most problems very quickly by checking your conditional statements(if, else, and the various types of loops), and checking the values of your variables and function return values.
For example
if (isset($_POST['sort']))
{
echo 'isset post sort condition was true!';
Now you know whether that if statement was working as expected. The next thing you might do would be to check the if/elseif statements nested inside it.
Often, you will need to inspect the value of a variable. You can use echo, or print_r(), or var_dump(). var_dump() is the most descriptive of them, because it also tells you what type of data it is(string, null, array etc…)
A lot of problems will be due to either trying to use variables that don’t really exist, or them having a value you don’t expect/anticipate. This is why you check them to discover whats really going on.
Give it a shot.