dealing with VARIABLES in WHERE statements or in mysql queries in GENERAL.....

[dsaba]

hello everyone I have a problem where I can't figure out what to do in a particular coding situation

let me explain:

 

I am making a mysql query with a variable for the WHERE clause

like this:

 

$samplequery = mysql_query("SELECT filename FROM mainfilelist WHERE $whereclause ORDER BY dateadded");

 

$whereclause would normally equal something like this:

$whereclause = "'filelanguage'='English'";

 

the problem is I also want to generate this mysql query where there is NO WHERE CLAUSE

so if I leave $whereclause blank:

$whereclause = "";

 

you can see where this is a problem in the generated mysql query:

$samplequery = mysql_query("SELECT filename FROM mainfilelist WHERE  ORDER BY dateadded");

 

there are TWO spaces in between WHERE and ORDER BY and that is an error in mysql syntax

also the WHERE is still part of the query although there is no whereclause needed

 

 

How do I fix this problem?

-thanks

[MadTechie]

<?php
$whereclause = "'filelanguage'='English'"; // or ""
if(empty($whereclause))
{
$samplequery = mysql_query("SELECT filename FROM mainfilelist ORDER BY dateadded");
}else{
$samplequery = mysql_query("SELECT filename FROM mainfilelist WHERE $whereclause ORDER BY dateadded");
}

?>

 

or

 

<?php
$whereclause = "'filelanguage'='English'"; // or ""
if(!empty($whereclause))
{
$whereclause = " WHERE ".$whereclause;
}
$samplequery = mysql_query("SELECT filename FROM mainfilelist $whereclause ORDER BY dateadded");
?>

[dough boy]

I would use the php ternary syntax for cleaner code.

 

<?php
$where = ($whereclause) ? 'WHERE '.$whereclause : NULL;
$samplequery = mysql_query("SELECT filename FROM mainfilelist $where ORDER BY dateadded");
?>

[dsaba]

 

or

 

<?php
$whereclause = "'filelanguage'='English'"; // or ""
if(!empty($whereclause))
{
$whereclause = " WHERE ".$whereclause;
}
$samplequery = mysql_query("SELECT filename FROM mainfilelist $whereclause ORDER BY dateadded");
?>

 

thanks for the help mad, I will use the first one, the second is not correct, because if the whereclause is NOT emtpy you will be missing a "WHERE" in the query

[emehrkay]

i agree with dough boy, with the exception of NULL. id use ''

[MadTechie]

i agree with emehrkay but i tried to keep the logic simple as statments like

$where = ($whereclause) ? 'WHERE '.$whereclause : "";

are harder to debug (well for me anyways )

[dough boy]

Yeah, it was hard for me at first.  The obvious advantage is that you from 6 lines of code down to 2.  It also saves on file size.  However, these are both "minute" but if you are anal like I have been told I am, I will nickel and dime anything.

[MadTechie]

i agree on all points

but still you win the clean code award