--- j0hncage <[EMAIL PROTECTED]> wrote:
> I have a query where I'd like to use 'ORDER BY id' to have some order
> to the way the query is displayed. Using ORDER BY works fine so long
> as the query form parameters are set for "All" initially, otherwise I
> get an error. Can someone point me in the right direction
> on correcting this error?
>
> Thanks very much for any help,
>
> john
> ----------------------------- w/o ORDER BY below-------------------
> <?
> $query =
> "SELECT *
> FROM actions_tbl
> WHERE 1 = 1 ";
> if($date != "All") $query .= "and date = '".$date."'";
> if($source != "All") $query .= "and source = '".$source."'";
> if($type != "All") $query .= "and type = '".$type."'";
> $result = mysql_query($query);
>
> $number = mysql_numrows($result);
> ----------------------------w/ORDER BY below---------------------
> <?
> $query =
> "SELECT *
> FROM actions_tbl
> WHERE 1 = 1
> ORDER BY id ";
> if($date != "All") $query .= "and date = '".$date."'";
> if($source != "All") $query .= "and source = '".$source."'";
> if($type != "All") $query .= "and type = '".$type."'";
> $result = mysql_query($query);
>
> $number = mysql_numrows($result);
The ORDER BY has to be at the end, just before a LIMIT if you have one. Hence,
you will want something like this:
$query = "SELECT * FROM actions_tbl WHERE 1 = 1 ";
$query .= ($date != "All") ? "and date = '$date' " : "";
$query .= ($source != "All") ? "and source = '$source' " : "";
$query .= ($type != "All") ? "and type = '$type' " : "";
$query .= "ORDER BY id ";
The most important differences between your code and mine is placing the ORDER
BY at the end of the query -- something which would be indicated if you printed
out the output of mysql_error() after running the query. The other important
change is the space after each value for your conditionals. You can't run
these together. If you print the query, you'd see this as not looking correct.
Again the mysql_error() would help here. You can achieve this with this code:
$result = mysql_query($query) or die("$query<hr>" . mysql_error());
This will display the query and error and stop the program if there is a
problem with your SQL.
I restructured things to use the conditional operator because it makes things
look a little neater. The only drawback is that you must supply both a value
if true and a value if false in this format:
return_value (condition) ? value_if_true : value_if_false;
In this case I returned an empty string if the condition was false.
James
