From: Stuart Felenstein <stuart4m@xxxxxxxxx>
To: Jochem Maas <jochem@xxxxxxxxxxxxx>
CC: stuart4m@xxxxxxxxx, php-db@xxxxxxxxxxxxx
Subject: Re: stumped by "order by"
Date: Sun, 19 Dec 2004 13:01:23 -0800 (PST)
--- Jochem Maas <jochem@xxxxxxxxxxxxx> 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
