[RESOLVED] PHP/SQL "Order BY" Drop down menu field.

Heatmizer20

Hello all,
Ok hopefully someone could guide me in the right direction, maybe even a real simple example that i could later work out.

I have an a SQL query that displays the results. It splits the results onto multiple pages. lets say 4 pages with 10 rows on each. The rows per page are already defined in the code. here is what i would like to do.

=======================================================
I would like to have 3 form drop down boxes.

Sort by "title of SQL field < ------example "Make, Model, Price" field
Order by <------------------------- ASC (A - Z) or Desc (Z - A) only 2 field choices
3 Results per page <---------------- changing rows per page (5,10, 20)

Also a submit button that will excute the new change and carry over into all the pages.

=======================================================

I know from reading this form that i need to adjust the "ORDER by" in the SELECT statement, but im not getting it to work. I can't seem to get the form select to change the ORDER BY variable and carry into the next pages.

I would like to understand a real basic example so i could further understand the code that i am working with.

Any help i would greatly appreciate.

thanks
JB.

bastien

show your code...

but basically you need to make the order by clause a variable

"select * from table name order by $order"

Heatmizer20

Heres the code (sorry about that):

<form action="<?php echo $PHP_SELF?>" method="post">

  <select name="sortby" id="sortby">
    <option value="make" selected="selected">make</option>
    <option value="price">price</option>
    <option value="model">model</option>
  </select>

  <select name="listby" id="listby">
    <option value="asend">A to Z</option>
    <option value="desend">Z to A</option>
  </select>

  <select name="diplayperpage" id="displayperpage">
    <option value="10">10</option>
    <option value="15">15</option>
    <option value="20">20</option>
      </select>
  <input name="submit-sort" type="submit">
</form>

<?php

$user = "my_username";
$pass = "my_password";
$server = "localhost";
$db =  mysql_connect("$server","$user","$pass");
$table = "my_table";
mysql_select_db("$table",$db);
$result = mysql_query("SELECT * FROM Listing", $db);

$limit = 4;

$numresults =mysql_query("select * from Listing id");
$numrows = mysql_num_rows($numresults);


if (!$id) {
if (empty($offset)) {
   $offset=1;
}


$result = mysql_query("SELECT * FROM Listing LIMIT $offset,$limit", $db); 
	echo "<table border\"1\" cellspacing=\"0\" cellpadding=\"5\" align=\"\">Summary<br>\n";

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

$make_0 = $myrow["make"];
$model_0 = $myrow["model"];
$price_0 = $myrow["price"];

printf ("<td><tr><br><a href=\"pagesum.php?id=%s\">&nbsp; $model_0 &nbsp </a>&nbsp; ($price_0 &nbsp;price)<br>\n", $myrow['id']);
printf  ("$make_0 &nbsp; <a href=\"%s?id=%s\">$make</a></td><td></td></tr><br>\n", $PHP_SELF, $myrow['id']);
}
echo "</table>\n";
}

if ($offset>=4) {
    $prevoffset=$offset-4;
	print "<a href=\"$PHP_SELF?offset=$prevoffset\">Prev</a>&nbsp: \n";
}

$pages=intval($numrows/$limit);
if ($numrows%$limit) {
$pages++;
}

for ($i=1;$i<=$pages;$i++) {
$newoffset=$limit*($i-1);
print "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \n";
}

if (!(($offset/$limit)==$pages) && $pages!=1) {
$newoffset=$offset+$limit;
print "<a href=\"$PHP_SELF?offset=$newoffset\">Next</a><p>\n";
}

?>

I want to link the drop down form up top.

bastien

if (!$id) { 
if (empty($offset)) { 
   $offset=1; 
} 

//defaults for 
$sortby = 'make';
$listby = 'ASC';
$displayperpage = 10;

if (!empty($_POST['sortby'])){
  $sortby = $_POST['sortby'];
}
if (!empty($_POST['listby'])){
  $listby= $_POST['listby'];
}
if (!empty($_POST['diplayperpage'])){
  $diplayperpage= $_POST['diplayperpage'];
}

$sql = "SELECT * FROM Listing ORDER BY $sortby $listby LIMIT $offset,$diplayperpage";

$result = mysql_query($sql, $db);  

    echo "<table border\"1\" cellspacing=\"0\" cellpadding=\"5\" align=\"\">Summary<br>\n"; 

while ($myrow = mysql_fetch_array($result)){ 
...

Heatmizer20

FANNNNN TASTIC. you are the best. thank you so much. 🙂

of course there is one more problem. it sorts and changes the row display just like i wanted it to, but i doesnt carry the NEW changes onto the additional pages.

For example, If I have the rows set to 4 (default row display), getting 3 pages out of it (12 rows total) and then i change it to display 2 rows per page, getting 6 pages, it does it fine... on that page alone. but as soon as i click on the next page to view (Page 2), it reverts to 4 rows (default)and changes the number of pages to 3.

Do i have to move part of the code somewhere else so that the new results hold onto the following pages?

Thanks

-JB-

bastien

I would suggest setting them in a cookie or session variable to keep them alive...the other option is just to place then in a hidden input form element on the page

Heatmizer20

Setting the cookies or a session.... ok. thats something that i havent touched yet. Time to do some more research.

thanks.
-JB-

bastien

Its pretty simple,,, let me know if you get stuck

Heatmizer20

I will. i want to attempt the research first. i will let you know how i do.

thanks.
-JB-

Heatmizer20

Well. I'm a bit stuck. 🙁 I see how people are writing setcookies, but i dont know how to apply it here. I also read a bit of what the difference is between a session and cookie. Im assuming that refreshing the cookie is the way to go.

I have tried a bunch of different ways and read quite a few different problems, but its not helping to much. I haven't touched cookies before.

If you could help me out some more, i can understand it better.

how do i refresh the cookies to apply the new variables to the code... ?

Thanks.

-JB-

Heatmizer20

Well Well Well, after quite a bit of reading, I think i found the solution.

Is this the answer.

 if (!$id) {  

if (empty($offset)) {  

   $offset=1;  

}  

//defaults for 
if (!isset($_COOKIE['make'])) {
$sortby = 'make'; 
}

if (!isset($_COOKIE['listby'])) {
$listby = 'ASC'; 
}

if (!isset($_COOKIE['displayperpage'])){
$displayperpage = 10; 
}

if (!empty($_POST['sortby'])){ 
  $sortby = $_POST['sortby']; 
} 
if (!empty($_POST['listby'])){ 
  $listby= $_POST['listby']; 
} 
if (!empty($_POST['diplayperpage'])){ 
  $diplayperpage= $_POST['diplayperpage']; 
} 

$sql = "SELECT * FROM Listing ORDER BY $sortby $listby LIMIT $offset,$diplayperpage"; 

$result = mysql_query($sql, $db);   

    echo "<table border\"1\" cellspacing=\"0\" cellpadding=\"5\" align=\"\">Summary<br>\n";  

while ($myrow = mysql_fetch_array($result)){  
...

It works, but was there a different approach that i should have taken?......

bastien

If you use hidden form field elements then you don't need the cookie code

 if (!$id) {  

if (empty($offset)) {  

   $offset=1;  

}  

//defaults for
$sortby = 'make'; 
$listby = 'ASC'; 
$displayperpage = 10; 


if (!empty($_POST['sortby'])){ 
  $sortby = $_POST['sortby']; 
} 
if (!empty($_POST['listby'])){ 
  $listby= $_POST['listby']; 
} 
if (!empty($_POST['diplayperpage'])){ 
  $diplayperpage= $_POST['diplayperpage']; 
} 

$sql = "SELECT * FROM Listing ORDER BY $sortby $listby LIMIT $offset,$diplayperpage"; 

$result = mysql_query($sql, $db); 
    echo "<form action=".$_SERVER['PHP_SELF']." method='post'>
              <input type='hidden' name='sortby' value='$sortby'>  

              <input type='hidden' name='listby' value='$listby'>
              <input type='hidden' name='diplayperpage' value='$diplayperpage'>";

echo "<table border\"1\" cellspacing=\"0\" cellpadding=\"5\" align=\"\">Summary<br>\n"; 

while ($myrow = mysql_fetch_array($result)){  
...  

echo "</form>";

Heatmizer20

I will definitely try the hidden approach. Thank you so much for helping me out.
I really appreciate it.

thanks,
-JB-