Page 1 of 1

Search Table - If's/And/Or's

Posted: Sun Sep 11, 2005 6:29 am
by facets
Hi all,

I'd like to search a table in a DB with multiple variable.
so far I have 'Left Join'ed the tables together and can do a search for a single summaryId but i'm stumped at how I could do this with multiple variable.

ie, $stockId AND/OR $linerId AND/OR $supplierId

my code looks like this at the moment...

Code: Select all

$query = "SELECT ausapapersummary.summaryId, ausapapersummary.paperCategoryId, aupapercategory.paperCategory, ausapapersummary.colloPaperName, ausapapersummary.manufacturerName, ausapapersummary.cpl, ausapapersummary.stockId, austock.stockDescription, ausapapersummary.adhesiveId, auadhesive.adhesiveDescription, auliner.linerDescription, ausapapersummary.linerId, ausapapersummary.supplierId, ausupplier.supplier FROM ausapapersummary ";

$query .="LEFT JOIN aupapercategory ON ausapapersummary.paperCategoryId = aupapercategory.papercategoryId
LEFT JOIN austock ON ausapapersummary.stockId = austock.StockId
LEFT JOIN auadhesive ON ausapapersummary.adhesiveId = auadhesive.adhesiveId
LEFT JOIN auliner ON ausapapersummary.linerId = auliner.linerId
LEFT JOIN ausupplier ON ausapapersummary.supplierId = ausupplier.supplierId WHERE ";

if ($stockId)
$query .= "ausapapersummary.stockId = '$stockId'";

if ($adhesiveId)
$query .="ausapapersummary.adhesiveId = '$adhesiveId'";

if ($linerId)
$query .="ausapapersummary.linerId = '$linerId'";
Any asistance would be greatly appreciated. TIA, Will/

Posted: Sun Sep 11, 2005 7:23 am
by feyd
use a secondary variable. Each one should check if it's empty, adding 'OR' when it isn't. Alternately, you could use an array for the various "options." Each option does a simple $foo[] = 'something'; After all of them, you simply implode() the array and concatenate like normal.. :)

Posted: Tue Sep 13, 2005 8:53 am
by facets
so something like :

if (isset($paperCategoryId)) $var1[] = $paperCategoryId
if (isset($stockId)) $var2[] = $stockId
if (isset($linerId)) $var3[] = $linerId)
if (isset($supplierId)) $var4[] = $supplierId

$myarray = myarray($var1, $var2, $var3, $var4);
$setup_sql_statement = implode(",", $myarray);

foreach i (echo $setup_sql_statement)
$query .= "AND ausapapersummary.paperCategoryId = '$i' ";
done

obviously the above won;t work because of the mixed languages but is that what you mean?

tia, will

Posted: Tue Sep 13, 2005 7:59 pm
by feyd
no. A single array, not 5 :P