Page 1 of 2

How to sort search results in PHP or JS

Posted: Thu Oct 07, 2010 3:01 pm
by hm9
Hi,

I want to sort the database search results in php or somethingelse. Basically, the results are returned on a php page and need to be sorted either by columns. I managed to display the results on the page with the code below and it works, but now i need to add the sorting bit, but i am stuck with it. I dont want to fetch the data from the database again but just to manipulate the existing entries. how can this be achieved in php?

Code: Select all

$sql .= 'SELECT    * FROM dealstable  WHERE  search  like "%' . $dest . '%" ';


$result = mysql_query($sql) or die("Query error: ".mysql_errno().": ".mysql_error());


while ($row = mysql_fetch_array($result))
{

print "<tr>"
;


print "<td><img  height='60' src='" . $row["id"] . "'></td>"
;

print "<td align='center'>" . $row["deals"] . "</td>"
;


want to add the sort option on the search results so that users can filter

something like:


Code: Select all

Sort  by 
<select name="sort" " id="sort" style="width:150px;">
<option value="lowprice">Lowest Price Asc</option>
<option value="highprice">Highest Price Desc</option>
etc
Thanks in advance :(

Re: How to sort search results in PHP or JS

Posted: Thu Oct 07, 2010 3:05 pm
by s.dot
The sort is going to be in the query.
You will need an ORDER BY clause.. such as..

Code: Select all

.. ORDER BY `price` DESC
quick google showed this example: http://www.tizag.com/mysqlTutorial/mysqlorderby.php

When someone selects a different sort by, you just change the order by in the query.

Re: How to sort search results in PHP or JS

Posted: Thu Oct 07, 2010 3:19 pm
by pickle
I think the original poster only wants to retrieve the data from the DB and be able to sort it after the 1 query is done. I've done this in the past when sorting can easily be done in Javascript without needing to bother the database again.

Doing the sorting in PHP without accessing the database again is a bit of a pain because you have to store the results either in $_SESSION or a form - plus you're bothering the web server again.

The best way to do this is use Javascript (unless you want this functionality available to the 12 people who don't have Javascript turned on). Specifically, I've used the tablesorter plugin & it works quite well.

Re: How to sort search results in PHP or JS

Posted: Thu Oct 07, 2010 3:26 pm
by Jonah Bron
hm9 wrote:something like:

Code: Select all

Sort  by
<select name="sort" " id="sort" style="width:150px;">
<option value="lowprice">Lowest Price Asc</option>
<option value="highprice">Highest Price Desc</option>
etc
Thanks in advance :(
In Javascript, you could do something cool, like clicking on the column title to sort by that column (click again to reverse order).

Re: How to sort search results in PHP or JS

Posted: Fri Oct 08, 2010 6:12 am
by hm9
Thats correct Pickle. I want to sort the results on the page that are already dislayed. You mentioned a plugin. is that easy to implement? do i have to install it on the web server? need to check with my host if the allow it?


Jonah: you mentioned In Javascript, you could do something cool, like clicking on the column title to sort by that column (click again to reverse order). do you any examples that are similar to my case i can look at?

thanks again

Re: How to sort search results in PHP or JS

Posted: Fri Oct 08, 2010 9:38 am
by pickle
Follow my link to the plugin - it explains how to use it.

Re: How to sort search results in PHP or JS

Posted: Fri Oct 08, 2010 11:12 am
by Jonah Bron
There's a demo right there on the Tablesorter plugin page.

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 8:07 am
by hm9
ok I followed the tutotials and it works but

I tried using the paging bit but it doesnt work. According to the tutorials, i need to add the following section in the head of the page


$(document).ready(function() { $("table") .tablesorter({widthFixed: true, widgets: ['zebra']}) .tablesorterPager({container: $("#pager")}); });


and also downloaded the add on for paging js and reference it in the header

<script type="text/javascript" src="jquery.tablesorter.pager.js">

But this doesnt work. any idea where the problem is?


Thanks

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 8:48 am
by mikosiko
did you add the

Code: Select all

  <div id="pager" class="pager">
....
....
</div>
at the end of your code? ... it works ok for me.

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 9:56 am
by hm9
I did but not working. where exactly it is suppoesed to be placed?
what goes inside the div? is it the table that does the sorting?

I now have to javascript functions in the head of the page:


<script type="text/javascript">
$(document).ready(function() { $("#myTable").tablesorter(); } );
</script>

<script type="text/javascript">
$(document).ready(function() { $("table") .tablesorter({widthFixed: true, widgets: ['zebra']}) .tablesorterPager({container: $("#pager")}); });
</script>
Can you please clarify?


thanks

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 10:57 am
by mikosiko
in my case I use it after the </tbody></table> tags in this way

Code: Select all

  <div id="pager" class="pager">
    	<form>
    		<img src="addons/pager/icons/first.png" class="first"/>
    		<img src="addons/pager/icons/prev.png" class="prev"/>
    		<input type="text" class="pagedisplay"/>
    		<img src="addons/pager/icons/next.png" class="next"/>
    		<img src="addons/pager/icons/last.png" class="last"/>
    		<select class="pagesize">
    			<option selected="selected"  value="10">10</option>
    			<option value="20">20</option>
    			<option value="30">30</option>
    			<option  value="40">40</option>
    		</select>
    	</form>
   </div>
if you read the documentation/examples in detail you will see a complete usage example

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 2:31 pm
by hm9
It still not working. Ity gives error message: 'undefined is null or nit an object'
The example does not show how to combine paging with sorting? which documentation you are referring to?
Can you clarify what you pt in the header?

this is what I have:
<script type="text/javascript" src="js/jquery-1.4.2.min.js"> </script>
<script type="text/javascript" src="js/jquery.tablesorter.pager.js"></script>
<script type="text/javascript" src="js/jquery.tablesorter.min.js"></script>

<script type="text/javascript">
$(document).ready(function() { $("#myTable").tablesorter(); } );
</script>


<script type="text/javascript">
$(document).ready(function() { $("table") .tablesorter({widthFixed: true, widgets: ['zebra']}) .tablesorterPager({container: $("#pager")}); });
</script>

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 2:44 pm
by mikosiko
http://tablesorter.com/docs/

there you will find the documentation and examples

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 2:59 pm
by hm9
Ok managed to get it tweaked, but noted that the max page number is only applied once the page is refreshed. the first time the page is rendered, it displays all records in the database e.g. 40 records, then the user will have to use the page size option to limit the number of records. is it possible to apply the limit when the page is rendered first time and then use the page option to show the other records?

I may have 200 records in the database and dont want to display them all in the first place.

Re: How to sort search results in PHP or JS

Posted: Tue Oct 12, 2010 3:19 pm
by mikosiko
weird... that works for me perfectly....