PHP and SQL

Anon

I want to do a search for products and on the find page, I want to have a series of IF statements that add a where clause if the datafield is not blank. For example:

$sql = "SELECT * FROM products";

if ($manufacturer != '') {
WHERE manufacturer = $manufacturer
};

if ($style != '') {
WHERE style = $style
};

How would I properly do this?

Anon

Here's how I do it. I create a variable (string), lets call it $whereSQL, and a variable to keep track of how many fields are being matched, which will will call $whereCount.

There purpose of the $whereCount is to decide whether " and " is required. $whereCount is set to 0, then incremented each time I add a field to search for.

At each step, if the field is being searched, I check the $whereCount. If $whereCount > 0 then I add " and " to $whereSQL, followed by " field = 'value' ", then $whereCount++. If $whereCount = 0, then I don't add the " and ", because it is the first field in the list.

I can then make the sql string by doing "SELECT -fields- FROM -table- WHERE " . $whereSQL . " ORDER BY -field-".

Don't quote me on any of this code, I'm sure I have some typos in there or something, but that is the general idea behind one way to do it.

-Pete

Anon

I like to put each where statement into an array, something like this in your IF statements:

$criteria[0] = "WHERE manuf = '$manuf'";
$criteria[1] = "WHERE style = '$style'";
etc...

Then use implode(" AND ",$criteria) to join them all together (check if at least one criteria exists, first). This way you don't have to worry about stripping off the last " AND " like with some methods...

My $0.02

---John Holmes...

Anon

try this ! it's a little barbarian, but it works !

<?
$rub = "bla";
$sousrub = "blo";
$mois = "";
$annee = "bli";
$perti = "";

if ($rub != "")
$WH = "$rub";
if ($sousrub != "")
$WH1 = "$WH and $sousrub";
else
$WH1 = "$WH";
if ($mois != "")
$WH2 = "$WH1 and $mois";
else
$WH2 = "$WH1";
if ($annee != "")
$WH3 = "$WH2 and $annee";
else
$WH3 = "$WH2";
if ($perti != "")
$where = "$WH3 and $perti";
else
$where = "$WH3";

echo $where;

and the display is :

bla and blo and bli

it works for a search by criteria on a job search engine by example... it's a good example and easy to understand.