--- 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; }
could better be written:
if(count($aWHERE)) {
$query_rsVJ .= ' WHERE '.implode(' AND ',$aWHERE);
}else{
//Let the user know to enter search values or ...
// print_r($aWHERE);
die('<p align="center"><strong>You must enter search
parameters</strong></p>');
}also beware of mixing HTML and PHP haphazardly.
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
in your code you seem to name this var $orderParam_rsVJ
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.
a select statement without a where clause normally returns all records?
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
have you done a print out of $_GET and $_POST?
it's not being recognized. I moved the code around in
what do you mean by not recogized? is the var not in the request arrays or is mysql ignoring/choking-on the order by clause of the sql statement?
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]
maybe use count() iso empty() because it instantly tells anybody reading that line that $aWHERE is an array. (the 'a' prefix only helps you ;-)
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'";
}
your snippet (directly above) is not very clear, but...
I don't think you should be putting quotes around the fieldname of
the order by clause (although you might want to use backticks - check the mysql manual for more info on those) and the braces look a little off (I always specify the braces even for single if statements to avoid possible confusion). it also looks like you missed a dot before the last assignment operator in your code snippet
eg:
$aWHERE = array(); if(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN ($s_Ind)" ; if(!empty($s_State)) $aWHERE[] = "Jobs.State IN ($s_State)";
/* ......[snip].... */
if(count($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']);
}if (isset($orderParam_rsVJ) &&
($orderParam_rsVJ = strval($orderParam_rsVJ)))
{
$query_rsVJ .= "order by $orderParam_rsVJ";
// or using backticks (untested):
$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.
there is only one statement, which you are building piece by piece.
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.
the fact that you didn't get it to work doesn't make it a bad idea - it might mean that its a little tricky to get you head round tho ;-)
an easy way of getting your WHERE vars into a url for reposting (i.e. when you click on an orderby link) is by using http_build_query() to create a usable url query string
http://nl2.php.net/http_build_query
another thing to keep in mind is that you should be sanitizing all the data input by the user before using it in a SQL query string. otherwise a user could do very nasty things - try googling for 'SQL injection hacks' (or something along those lines)
Stuart
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
