Re: [PHP-DB] stumped by "order by"
Stuart Felenstein wrote:
--- Jochem Maas <[EMAIL PROTECTED]> 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 ...
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('You must enter search
parameters');
}
also beware of mixing HTML and PHP haphazardly.
Also:
//If no records are found:
$row_rsVJ = mysql_fetch_assoc($rsVJ);
if ($row_rsVJ == false) {
?>
Found");?>
}
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 or
Re: [PHP-DB] 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 <[EMAIL PROTECTED]>
To: Jochem Maas <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: [PHP-DB] stumped by "order by"
Date: Sun, 19 Dec 2004 13:01:23 -0800 (PST)
--- Jochem Maas <[EMAIL PROTECTED]> 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 ...
Also:
//If no records are found:
$row_rsVJ = mysql_fetch_assoc($rsVJ);
if ($row_rsVJ == false) {
?>
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] stumped by "order by"
--- Jochem Maas <[EMAIL PROTECTED]> 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 ...
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
Re: [PHP-DB] stumped by "order by"
hi Stu,
...
>
> Okay I wasn't sure but thought I might need to add
> another query statement in. So it would have made it
> work except! , I found a new problem with this issue.
>
> Hopefully I can clearly explain it.
> There is a default $orderParam_rsVJ set up. So when
> the results are returned to the user things come back
> sorted by the default column.
>
> 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?
> order by statement is issued with no parameters.
what do you mean by '..is issued with no paramters.'?
if your sql statements ends in 'ORDER BY ' then that will cause an error
(but not div by zero surely?)
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!)
>
> Hope this makes sense and while I think it through
> perhaps someone has an idea of remedy.
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'):
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
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.
if you are taking one of the preceding measures then likely it's not
doing what its supposed to... in such cases a liberal sprinkling of
print_r() statements often help to determine where values are going
missing. here is a little func that makes the output nicer to read in a
browser:
function pre_print($arg, $returnVal = false)
{
static $isOnWeb;
if (!isset($isOnWeb))
{
$isOnWeb = (isset($_SERVER) &&
isset($_SERVER['HTTP_HOST']))
? true
: false;
}
if ($returnVal) { ob_start(); }
echo ($isOnWeb) ? '' : "\n ---\n";
print_r($arg);
echo ($isOnWeb) ? '' : "\n ---\n";
if ($returnVal) { return ob_get_clean(); }
}
--
sorry, I didn't clearly understand your problem, hopefully I haven't
completely misunderstood the problem.
rgds,
Jochem
***before anybody complains that using files directly to store state
related info is per definition bad - consider /tmp mounted in RAM :-).
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] stumped by "order by"
--- Bastien Koert <[EMAIL PROTECTED]> wrote:
> Hi Stu
>
> something like
>
> Bif(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN
> ($s_Ind)" ;
> if(!empty($s_State)) $aWHERE[] = "Jobs.State IN
> ($s_State)";
> if(!empty($s_TType)) $aWHERE[] = "Jobs.Term IN
> ($s_TType)";
> if(!empty($JTitle)) $aWHERE[] = "Jobs.Title LIKE
> '%$JTitle%'";
> if(!empty($City)) $aWHERE[] = "Jobs.City LIKE
> '%$City%'";
> if(!empty($Days)) $aWHERE[] = "Jobs.Post >=
> DATE_SUB(CurDate(), Interval ($Days) DAY)";
>
> //the implode array statement to make the array into
> the where clause
> $query_rsVJ .= implode
>
> //then check if the sort element is passed
> if (isset($_GET['order_rsVJ'])) {
> $orderParam_rsVJ = (get_magic_quotes_gpc()) ?
> $_GET['order_rsVJ'] :
> addslashes($_GET['order_rsVJ']);
>
> $query_rsVJ.= " order by $orderParam_rsVJ ";
>
Okay I wasn't sure but thought I might need to add
another query statement in. So it would have made it
work except! , I found a new problem with this issue.
Hopefully I can clearly explain it.
There is a default $orderParam_rsVJ set up. So when
the results are returned to the user things come back
sorted by the default column.
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
order by statement is issued with no parameters.
Hope this makes sense and while I think it through
perhaps someone has an idea of remedy.
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] stumped by "order by"
Hi Stu
something like
$query_rsVJ = " SELECT Jobs.PostStart,
Jobs.JobID,Jobs.JobTitle,
Jobs.City, Ind.Categories, VendorSignUp.CompanyName,
US.States, Term.Tax FROM Jobs
INNER JOIN Ind ON (Jobs.Industry = Ind.CIDs)
INNER JOIN US ON (Jobs.State = US.SID)
INNER JOIN SignUp ON (Jobs.VID = SignUp.VID)
INNER JOIN Term ON (Jobs.Tax = Term.TID)";
Bif(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN
($s_Ind)" ;
if(!empty($s_State)) $aWHERE[] = "Jobs.State IN
($s_State)";
if(!empty($s_TType)) $aWHERE[] = "Jobs.Term IN
($s_TType)";
if(!empty($JTitle)) $aWHERE[] = "Jobs.Title LIKE
'%$JTitle%'";
if(!empty($City)) $aWHERE[] = "Jobs.City LIKE
'%$City%'";
if(!empty($Days)) $aWHERE[] = "Jobs.Post >=
DATE_SUB(CurDate(), Interval ($Days) DAY)";
//the implode array statement to make the array into the where clause
$query_rsVJ .= implode
//then check if the sort element is passed
if (isset($_GET['order_rsVJ'])) {
$orderParam_rsVJ = (get_magic_quotes_gpc()) ?
$_GET['order_rsVJ'] : addslashes($_GET['order_rsVJ']);
$query_rsVJ.= " order by $orderParam_rsVJ ";
}
bastien
From: Stuart Felenstein <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: [PHP-DB] stumped by "order by" Date: Sun, 19 Dec 2004 05:46:20
-0800 (PST)
My problem is I can't seem to find the right place to
put an "order by" clause in my statement.
I have this select statement:
$query_rsVJ = " SELECT Jobs.PostStart,
Jobs.JobID,Jobs.JobTitle,
Jobs.City, Ind.Categories, VendorSignUp.CompanyName,
US.States, Term.Tax FROM Jobs
INNER JOIN Ind ON (Jobs.Industry = Ind.CIDs)
INNER JOIN US ON (Jobs.State = US.SID)
INNER JOIN SignUp ON (Jobs.VID = SignUp.VID)
INNER JOIN Term ON (Jobs.Tax = Term.TID)";
But then I also have a dynamic where clause that is
based on what values the user has chosen:
if(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN
($s_Ind)" ;
if(!empty($s_State)) $aWHERE[] = "Jobs.State IN
($s_State)";
if(!empty($s_TType)) $aWHERE[] = "Jobs.Term IN
($s_TType)";
if(!empty($JTitle)) $aWHERE[] = "Jobs.Title LIKE
'%$JTitle%'";
if(!empty($City)) $aWHERE[] = "Jobs.City LIKE
'%$City%'";
if(!empty($Days)) $aWHERE[] = "Jobs.Post >=
DATE_SUB(CurDate(), Interval ($Days) DAY)";
When the sql is submitted the results come back (in
columns of course) Then the user can click the column
head and sort by that column.
$orderParam_rsVJ = "City";
if (isset($_GET['order_rsVJ'])) {
$orderParam_rsVJ = (get_magic_quotes_gpc()) ?
$_GET['order_rsVJ'] : addslashes($_GET['order_rsVJ']);
}
I just have had no luck putting in
order by "$orderParam_rsVJ in the current statement
(above).
If I put it in the select statement , the it's out of
place with the where clause.
If I put it at the end of the where clause, obviously
it is not seen.
Any ideas how I can set this up correctly ?
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] stumped by "order by"
My problem is I can't seem to find the right place to
put an "order by" clause in my statement.
I have this select statement:
$query_rsVJ = " SELECT Jobs.PostStart,
Jobs.JobID,Jobs.JobTitle,
Jobs.City, Ind.Categories, VendorSignUp.CompanyName,
US.States, Term.Tax FROM Jobs
INNER JOIN Ind ON (Jobs.Industry = Ind.CIDs)
INNER JOIN US ON (Jobs.State = US.SID)
INNER JOIN SignUp ON (Jobs.VID = SignUp.VID)
INNER JOIN Term ON (Jobs.Tax = Term.TID)";
But then I also have a dynamic where clause that is
based on what values the user has chosen:
if(!empty($s_Ind)) $aWHERE[] = "Jobs.Industry IN
($s_Ind)" ;
if(!empty($s_State)) $aWHERE[] = "Jobs.State IN
($s_State)";
if(!empty($s_TType)) $aWHERE[] = "Jobs.Term IN
($s_TType)";
if(!empty($JTitle)) $aWHERE[] = "Jobs.Title LIKE
'%$JTitle%'";
if(!empty($City)) $aWHERE[] = "Jobs.City LIKE
'%$City%'";
if(!empty($Days)) $aWHERE[] = "Jobs.Post >=
DATE_SUB(CurDate(), Interval ($Days) DAY)";
When the sql is submitted the results come back (in
columns of course) Then the user can click the column
head and sort by that column.
$orderParam_rsVJ = "City";
if (isset($_GET['order_rsVJ'])) {
$orderParam_rsVJ = (get_magic_quotes_gpc()) ?
$_GET['order_rsVJ'] : addslashes($_GET['order_rsVJ']);
}
I just have had no luck putting in
order by "$orderParam_rsVJ in the current statement
(above).
If I put it in the select statement , the it's out of
place with the where clause.
If I put it at the end of the where clause, obviously
it is not seen.
Any ideas how I can set this up correctly ?
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
