How to sort search results in PHP or JS

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

How to sort search results in PHP or JS

Post 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 :(
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: How to sort search results in PHP or JS

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How to sort search results in PHP or JS

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to sort search results in PHP or JS

Post 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).
hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

Re: How to sort search results in PHP or JS

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How to sort search results in PHP or JS

Post by pickle »

Follow my link to the plugin - it explains how to use it.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: How to sort search results in PHP or JS

Post by Jonah Bron »

There's a demo right there on the Tablesorter plugin page.
hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

Re: How to sort search results in PHP or JS

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to sort search results in PHP or JS

Post 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.
hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

Re: How to sort search results in PHP or JS

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to sort search results in PHP or JS

Post 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
hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

Re: How to sort search results in PHP or JS

Post 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>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to sort search results in PHP or JS

Post by mikosiko »

http://tablesorter.com/docs/

there you will find the documentation and examples
hm9
Forum Newbie
Posts: 14
Joined: Sun May 09, 2010 4:27 pm

Re: How to sort search results in PHP or JS

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: How to sort search results in PHP or JS

Post by mikosiko »

weird... that works for me perfectly....
Post Reply