Re: stumped by "order by"

you need to persist the choices made before the first run thru of the code...best way to is put the where clause into a session variable...then check for the existance/value of the session variable before execution...if its there then just get the sort order var from the url...if not run thru the array code to set it properly...then save that array set to the session var... bastien

From: Stuart Felenstein <stuart4m@yahoo.com> To: Jochem Maas <jochem@iamjochem.com> CC: stuart4m@yahoo.com, php-db@lists.php.net Subject: Re: [PHP-DB] stumped by "order by" Date: Sun, 19 Dec 2004 13:01:23 -0800 (PST) --- Jochem Maas <jochem@iamjochem.com> wrote: The problem is when I click the column sorter link and the page re-loads, it looks like the where array is getting wiped out, values are gone. I get a "divison by zero" error. I'm assuming this is becasue the is this error occurring in mysql or php? could you post the relevant line number or sql statement that causes the error? I've stopped the "division by zero", but let me explain with the code: //Here is the test to make sure user entered at least //one search value if(!empty($aWHERE)) { $query_rsVJ .= ' WHERE '.implode(' AND ',$aWHERE); }else{ ?> //Let the user know to enter search values or ... <?php print_r($aWHERE); ?> <p align="center"><strong><?php print_r ("You must enter search parameters"); ?> </strong></p> <?php exit; } Also: //If no records are found: $row_rsVJ = mysql_fetch_assoc($rsVJ); if ($row_rsVJ == false) { ?> <p align="center"><strong><?php print_r ("No Matches Found");?></strong></p> <?php exit; } Now I have been using print_r and var_dump on the various variables and sql statements. Yet the variable $orderParameter_rsVJ was not returning anything, blank. Then I removed the two clauses above, the exit scripts. At that point the $orderParameter_rsVJ variable was printing out the correct value based on the header link I selected , but with a division by zero. And no records. That is when I found the problem with the where array. Am I right in assuming that when you click a header (order by) link that the page displays the result in the correct order BUT does not include the where clause that was in effect? (my ideas below assume this!) There will be no records returned without the where clause. So I'm left with either my exit script or if I remove that a blank page of no records returned. I assume when the page is first called some filtering values as passed to the page which are used to build the various WHERE clauses, in addition a default order by statement (e.g. 'ORDER BY title'): Correct although the ORDER BY is still a problem as it's not being recognized. I moved the code around in a few different ways and used Bastien's order as well. $query_rsVJ = " SELECT Jobs.Post, Jobs.JID,Jobs.Title, Jobs.City, Ind...............[snip] $aWHERE = array(); if(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN ($s_Ind)" ; if(!empty($s_State)) $aWHERE[] = "Jobs.State IN ($s_State)";......[snip] if(!empty($aWHERE)) { $query_rsVJ .= ' WHERE '.implode(' AND ',$aWHERE); $orderParam_rsVJ = "City"; if (isset($_GET['order_rsVJ'])) { $orderParam_rsVJ = (get_magic_quotes_gpc()) ? $_GET['order_rsVJ'] : addslashes($_GET['order_rsVJ']); $query_rsVJ = "order by '$orderParam_rsVJ'"; } This last SQL statement here is not being included in the result set. Even the default - "City" is not being sorted as such. the values used to create the WHERE clauses are probably disappearing because you are not doing one of the following (btw doing both is not a good idea): 1. proliferating the WHERE related values in the ORDER BY links - i.e. the column headers need to include all the name value pairs related to the WHERE clauses as well as the name/value pair related to ordering. e.g.: ./foo.php?sortfield=price instead of: ./foo.php?field1=bar&field2=qux&sortfield=price I'm going to have to think about this one. Sounds like a lot of code. I'm not complaining :) 2. storing the last used filter (i.e. the last given set of WHERE clause related values) in the session, a DB, file*** or whatever in order to remain state - in this case you need to retrieve the values from where they are stored and setup the variables to build the SQL as well as having a mechanism for detecting whether to use new values sent from the browser rather than those already stored. I tried setting $aWhere as a session variable. Bad approach as it didn't work. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php