[RESOLVED] Dynamic Where Clause

[Earnan]

I have tried to search for Dynamic Where and didn't see anything that looked correct. I have tried to do WHERE = *.

I have a page that is a series of four lists.
Each list has almost the same query, just with different WHERE clauses.
Currently I have 4 different SQL statements that are all the same except for WHERE. I would like to turn this into a function to clean up the code somewhat.

My problem is that the WHERE clauses don't all have the same elements.

Does someone have a handy way of dynamically creating the WHERE clause without having to do a whole bunch of If or Switch statements? I realize I could make the first three the same and a seperate SQL for the fourth, but I am trying to learn PHP and MySQL, so I'd like to find the best answer.

Here are the for WHERE clauses:
WHERE t1.Active=1 AND t3.OnStage=0 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 0
WHERE t1.Active=1 AND t3.OnStage=1 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 1
WHERE t1.Active=1 AND t3.OnStage=0 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 1
WHERE t1.Active=0 AND t1.ID <> 58

Thank you.

[Installer]

A simple way would be to just put them into an array, then add each one to the query as needed, in a loop or otherwise:

$wheres = array('WHERE t1.Active=1 AND t3.OnStage=0 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 0',
                'WHERE t1.Active=1 AND t3.OnStage=1 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 1',
                'WHERE t1.Active=1 AND t3.OnStage=0 AND t3.Active=1 AND t1.ID <> 58 AND t3.ShowPos = 1',
                'WHERE t1.Active=0 AND t1.ID <> 58');

$query = "SELECT * FROM my_table " . $wheres[2];

[Crowly]

Another method, dont know how suitable it is for this case but the WHERE statement is very flexible as you can assign alot of diffrent values to the $argX variables.

(Ex you could assign the values with $GET/$POST/$_REQUEST, but then you should consider useing mysql_real_escape_string() to make sure no unwanted char's sneak into your WHERE statement.)

$arg1 = "t1.Active=1";
$arg2 = "t3.OnStage=0";
$arg3 = "t3.Active=1";
$arg4 = "t1.ID <> 58";
$arg5 = "t3.ShowPos = 0";

$query = sprintf("SELECT something FROM anytable t1, anytable2 t2, anytable3 t3
WHERE %s AND %s AND %s AND %s AND %s", $arg1, $arg2, $arg3, $arg4, $arg5);

$result = mysql_query($query);

I guess this could also be built into a function where $arg1..5 is parameters and $result is returned. Something like this, i havent testet it but i think it should work:

function my_query($arg1,$arg2,$arg3,$arg4,$arg5)
{
  $query = sprintf("SELECT something FROM anytable t1, anytable2 t2, anytable3 t3
WHERE %s AND %s AND %s AND %s AND %s", $arg1, $arg2, $arg3, $arg4, $arg5);

  $result = mysql_query($query);
  return $result;
}

// some code

$result2=my_query("t1.Active=1","t3.OnStage=0","t3.Active=1","t1.ID <> 58","t3.ShowPos = 0");

//Or you could do
//$result2=my_query($a1,$a2,$a3,$a4,$a5); 
//and then assign values to $a1..5 as needed in the WHERE as in the example above

while ($row=mysql_fetch_array($result2))
{
 // some code
}

// more code

[Earnan]

I ended up going with Installer's method, and it works, something just seems...wrongish to me. 🙂

Thanks for the help.