[SOLVED] Pagination Sort By

[richo89]

I currently have search page whereby the users results are displayed and paginated.

 

However, I need to implement something whereby the user can sort by Price Ascending, Price Descending, A-Z Ascending, A-Z Descending and so fourth. Therefore when the user selects a sort by option from a dropdown it then runs the query however it would ORDER BY price ASC for example.

 

How can I add a sort by drop down selection that will order my results.

 

My code for pagination is the following:

 

<?
//Grabbing the variable prefixID which is UNIQUE from carSearch.php
$prefixID = (int) $_GET['prefixID'];

$page_name="buyConfirm.php"; //  If you use this code with a different page ( or file ) name then change this 

@$column_name=$_GET['column_name']; // Read the column name from query string. 

$prefix = $_GET['prefix'];
$prefix2 = $_GET['prefix2'];
$prefix3 = $_GET['prefix3'];
$prefix4 = $_GET['prefix4'];
$prefix5 = $_GET['prefix5'];

$start=$_GET['start'];								
if(!($start > 0)) {                       
$start = 0;
}

$eu = ($start - 0); 
$limit = 1;                                
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit; 

$query2 = "SELECT * FROM prefix WHERE prefixID='".$prefixID."'";  
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);

$query = "SELECT * FROM prefix WHERE prefixID='".$prefixID."'";  
if(isset($column_name) and strlen($column_name)>0){
$query = $query . " order by $column_name";
}
$query = $query. " limit $eu, $limit ";
$result=mysql_query($query);
echo mysql_error();

echo "<table border=0 width=50%>"; 
while($noticia = mysql_fetch_array($result)) 
{ 
if($bgcolor=='#f3efef'){$bgcolor='#f3efef';} 
else{$bgcolor='#f3efef';} 
echo "<tr >"; 
echo "<td width='57%' height='67' align='center' valign='center' bgcolor=$bgcolor id='plateno' background='img/blankPlateLrg.jpg'> <font face='Arial' size='14'> $noticia[plate] </font></td>";  
echo "<td width='8%' height='67' align='center' valign='center' bgcolor=$bgcolor><font face='Arial' size='6'>    </font></td>";
echo "<td width='8%' height='67' align='center' valign='center' bgcolor=$bgcolor><font face='Arial' size='6'>    </font></td>";
echo "<tr >";
echo "<TD>   </TD>";
echo "<tr >";
echo "<td width='20%' colspan='3' height='26' align='left' valign='center' bgcolor=$bgcolor id='sellerInfo'>Price: £$noticia[cost] </td>";  
echo "<tr >";
echo "<td width='20%' colspan='3' align=left valign='left' bgcolor=$bgcolor id='platemore'>Plate Representation: $noticia[keywords]</td>";
echo "</tr>"; 

$custid = $noticia[Customer_ID];
}

$query3 = "SELECT * FROM customer WHERE Customer_ID='".$custid."'"; 
if(isset($column_name) and strlen($column_name)>0){
$query3 = $query3 . " order by $column_name";
}
$query3 = $query3. " limit $eu, $limit ";
$result=mysql_query($query3);
echo mysql_error();

echo "<table border=0 width=50%>"; 
while($noticia = mysql_fetch_array($result)) 
{ 
if($bgcolor=='#f3efef'){$bgcolor='#f3efef';} 
else{$bgcolor='#f3efef';} 
echo "<tr >";
echo "<td width='20%' colspan='3' height='26' align='left' valign='center' bgcolor=$bgcolor id='sellerInfo'>Seller: $noticia[Title] $noticia[Firstname] $noticia[surname]  </td>";  
echo "<tr >";
echo "<td width='28%' colspan='3' align=left valign='left' bgcolor=$bgcolor id='platemore'>Contact Number: $noticia[Telephone] </td>";
echo "<tr >";
echo "<td width='28%' colspan='3' align=left valign='left' bgcolor=$bgcolor id='platemore'>E-mail Address: <a href='$noticia[Email]'>$noticia[Email]</a></td>";
echo "<tr >";
echo "<td width='28%' colspan='3' align=left valign='left' bgcolor=$bgcolor id='platemore'> </td>";
echo "</tr>"; 
$mailto = $noticia[Email];
} 

echo "</td></tr></table>";

?>

[MadTechie]

it seam you have one already via

?column_name=price

[richo89]

How could I implement a drop selection that changes this column order by once selected?

[MadTechie]

You can't via PHP, that's HTML and JS

create as drop down (HTML)

then onchange, change the address location to include

?column_name=X

or use ajax (but the same idea)

 

you can adapt any redirect via drop down script

[richo89]

You can't via PHP, that's HTML and JS

create as drop down (HTML)

then onchange, change the address location to include

?column_name=X

or use ajax (but the same idea)

 

you can adapt any redirect via drop down script

 

Ok thanks i'll look into something with HTML and JavaS to see if I can get something that reloads when I select a particular dropdown - I've never touched Ajax so bit unsure about that!

[MadTechie]

here's a quick example to get you started

<FORM name="sortby">
<SELECT name="sortname" onchange="window.location='?column_name='+document.sortby.sortname.options[document.sortby.sortname.selectedIndex].value">
<OPTION <?php echo ($_GET['column_name'] == 'name')?"SELECTED":""; ?> value="name">name
<OPTION <?php echo ($_GET['column_name'] == 'other')?"SELECTED":""; ?> value="other">other
<OPTION <?php echo ($_GET['column_name'] == 'price')?"SELECTED":""; ?> value="price">price
</SELECT>
</FORM> 

[richo89]

here's a quick example to get you started

<FORM name="sortby">
<SELECT name="sortname" onchange="window.location='?column_name='+document.sortby.sortname.options[document.sortby.sortname.selectedIndex].value">
<OPTION <?php echo ($_GET['column_name'] == 'name')?"SELECTED":""; ?> value="name">name
<OPTION <?php echo ($_GET['column_name'] == 'other')?"SELECTED":""; ?> value="other">other
<OPTION <?php echo ($_GET['column_name'] == 'price')?"SELECTED":""; ?> value="price">price
</SELECT>
</FORM> 

 

Thank you ever so much thats got me started perfectly, i've now made it so that column_name is cost ASC or cost DESC.

 

However, when I select the option it gives me the following URL:

http://localhost/plate%20pad/search.php?column_name=cost%20ASC

 

But the url needs to look like the following:

http://localhost/plate%20pad/search.php?start=0&column_name=cost%20ASC&prefix=A&prefix2=1&prefix3=A&prefix4=B&prefix5=C

[richo89]

Working code!

 

<FORM name="sortby" action="search.php" method="get">
<SELECT name="sortname" onchange="window.location='?start=0&column_name=&prefix=%&prefix2=%&prefix3=%&prefix4=%&prefix5=%&'+document.sortby.sortname.options[document.sortby.sortname.selectedIndex].value">
<option value="" selected="selected">Please Select
<OPTION <?php echo ($_GET['column_name'] == 'cost ASC')?"SELECTED":""; ?> value="column_name=cost%20ASC">Price Ascending
<OPTION <?php echo ($_GET['column_name'] == 'cost DESC')?"SELECTED":""; ?> value="column_name=cost%20DESC">Price Descending
</OPTION>
</SELECT>
</FORM> 

 

Thank you kindly from MadTechie your help is highly appreciated.

[MadTechie]

While I'll like to say your welcome.. I'm not 100% sure your code will work correctly, does the prefix's still hold their values ?

 

you could setup all the parameters again and pass them back but i fine it easier in the long run to just strip out the keys i want to replace and set new ones

 

for example

 

update on change

<SELECT name="sortname" onchange="Sort(document.sortby.sortname.options[document.sortby.sortname.selectedIndex].value)">

 

add JS

<script language="javascript">
function Sort(sort)
{
var url = window.location.href;
url = url.replace(/&?column_name=[^&]*&?/g, "");
window.location=url+'&column_name='+sort;
}
</script>

 

I think I'll move this to the javascript section