...

View Full Version : [SOLVED] IF Statement Matrix - Too many options



facets
09-13-2005, 01:41 PM
Hi All,

I'm attempting to build a search engine for my mysql db.
The form has 14 fields to search on but I though i'd start with 5 to "work out the logic". Each of the following is either text input of drop down
Paper Category, Manufactured Name, Computer Lookup Prefix, Face Stock, Adhesive

I want ot be able to search on any, all or some of these fields.
So i'm guessing that some kind of IF matrix is needed to to state..

if (isset($PaperCategoryId)) then $query .= "db.paperId = $PaperCategoryId";
if (isset($PaperCategoryId) && ($ManufacturedName)) then $query .= "db.paperId = $PaperCategoryId"; $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName)) then $query .= "db.manufacturedId = $ManufacturedName";
if (isset($ManufacturedName) && $ComputerLookupPrefix) then $query .= "db.manufacturedId = $ManufacturedName"; $query .= "db.ComputerLookupPrefixId = $ComputerLookupPrefix

that's just a smidge of what I need to cover the 5 x 5 x 5 x 5 options.
does anyone have a simpler way to do this?

tia, will

nikkiH
09-13-2005, 02:46 PM
An idea (not tested or anything):
Store the data names and stuff in a hash, 2 dim array, or other construct, then loop.

Pseudocode for associative array (hash in php?)

store all $varname and $sqlstring pairs in hash

for each element in hash do
-- if $varname is set then append $sqlstring
loop

You might need some sort of eval or something to check for the $varname defined thing, since it will be stored as a string. Not sure...

facets
09-13-2005, 02:56 PM
excellent. i was thinking something like that, with some outside help ;)

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?

facets
09-13-2005, 03:49 PM
this appear to be working ok.
but it's filling in both option even though only one maybe set.



if (isset($paperCategoryId))
if (isset($stockId))

$searchDb = array('ausapapersummary.paperCategoryId', 'ausapapersummary.stockId');
$search = array($paperCategoryId, $stockId);

for($x = 0; $x<count($search); $x++) {
echo ($query .= " AND ".$searchDb[$x]." = ".$search[$x]."");
}



How would I set the $search array to only pick up variables that where if(isset()

Fou-Lu
09-13-2005, 04:11 PM
Where are these values comming from? As a search engine of sorts, I'd say from a form correct?
Instead, create your form as an array of fields. Here's what I mean:


<!-- blah blah blah, to the form -->
<input type="text" name="search[paperCategoryId]" value="" />
<input type="text" name="search[stockId]" value="" />
<input type="text" name="search[linerId]" value="" />
<!-- etc -->

Then with your script, something like so:



$table_alias = 'ausapapersummery.';
$condition = '';
if (is_array($_POST['search']))
{
$condition = ' WHERE 1=1';

foreach ($_POST['search'] AS $key => $val)
{
// Here's the tricker, text fields are always set, even if its null:
if (!empty($val))
{
$condition .= ' AND `' . $table_alias . $key . '` = "' . mysql_real_escape_string($val) . '"';
}
}
}
$query = "SELECT $table_alias.* FROM table AS ausapapersummery" . $condition;

Or something of the sorts. I'd recommend an array of acceptable fields and datatypes to help prevent sql injections as well. If your data is not from a form, this method can still be used, but you would need to alter your array to choose from. This is untested as well, btw.
Offhand, if I need to take a stab at your full problem, I'm guessing your using a form method, and passing text fields. Your method searches for an isset() on the field => text fields are always sent, if empty it will send null. This is why I use !empty() instead, as it ignores whether or not the variable exists or not, it mearly checks to ensure it has a value.


Oh, thought I'd mention as well, with a search engine you may find some use with a LIKE syntax instead of '='. This depends of course on how specific you want the user to be, and how much data you want to return.
Cheers.

facets
09-13-2005, 04:20 PM
Thanks for your ideas!
I'm so close with this coding that i'm hesitant to change it.

currentl if the variable is not set all that prints out is ' AND ='
any idea on how I would stop that?




if (isset($paperCategoryId)) $var1 = $paperCategoryId;
if ($paperCategoryId > 0) $var3 = 'ausapapersummary.paperCategoryId';
if (isset($stockId)) $var2 = $stockId;
if ($stockId) $var4 = 'ausapapersummary.stockId';

$searchDb = array($var3, $var4);
$search = array($var1, $var2);

for($x = 0; $x<count($searchDb); $x++) {
$query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
}

Fou-Lu
09-13-2005, 04:32 PM
Where is it that your variables are comming from? Is it a form text field?
If it is, the problem is here:


if (isset($paperCategoryId)) $var1 = $paperCategoryId;
if ($paperCategoryId > 0) $var3 = 'ausapapersummary.paperCategoryId';
if (isset($stockId)) $var2 = $stockId;
if ($stockId) $var4 = 'ausapapersummary.stockId';

Where you use isset() functions, change them to !empty() instead. As mentioned, text fields send a variable when empty, but no value.
Like so:


if (!empty($paperCategoryId))
{
$var1 = $paperCategoryId;
$var3 = 'ausapapersummary.paperCategoryID';
}
if (!empty($stockId))
{
$var2 = $stockId;
$var4 = 'ausapapersummary.stockId';
}

I've also removed the if ($paperCategoryId > 0) section. It won't hurt to send it, as it will ignore it within the SQL.
This should fix the problem for you, stressing still that I'm expecting this data from a form. Should this be true, and you still experience similar/no output, please ensure that $paperCategoryId and $stockId exist, and have been extracted from the _POST superglobal (register_globals off).
Then get back to me if you have problems.
I'd still recommend a roadmap route like mine. This will allow you to create as many fields as you want, without adding additional script code for hardcoding. After you have yours working, give mine a shot but add all 14 fields to your form -> you'll see that it works peachy

facets
09-13-2005, 04:49 PM
thanks again.
the data is coming from a form.
more precisely many drop down menu's.
the data echo's back ok and all variables are set etc.

the whole code looks more like :




$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 1=1 ";

$var1 = 0;
$var2 = 0;
$var3 = 0;
$var4 = 0;
$var5 = 0;
$var6 = 0;

if (!empty($paperCategoryId)) {
$var1 = $paperCategoryId;
$var2 = 'ausapapersummary.paperCategoryID';
}

if (!empty($stockId)) {
$var3 = $stockId;
$var4 = 'ausapapersummary.stockId';
}

if (!empty($adhesiveId)) {
$var5 = $adhesiveId;
$var6 = 'ausapapersummary.adhesiveId';
}

$searchDb = array($var2, $var4, $var6);
$search = array($var1, $var3, $var5);

for($x = 0; $x<count($searchDb); $x++) {
$query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
}



so now my rendered SQL command looks like :

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 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 1=1 AND 0 = 0 AND 0 = 0 AND ausapapersummary.adhesiveId = 25

AND 0 = 0 sin't a bad line to have in a statement??

Fou-Lu
09-13-2005, 05:06 PM
Yeah it is, sorry I thought you meant that it was searching for field = '0', which would be ok (so long as if you have values under '0' your happy with adding them :p)
Simply alter which ever ones you need here:


if (!empty($paperCategoryId) AND $paperCategoryId > 0) {
$var1 = $paperCategoryId;
$var2 = 'ausapapersummary.paperCategoryID';
}

If $paperCategoryId is being defined before this, you can actually drop the !empty() section completely, and evaluate its value instead. This only works (without notice) if $paperCategoryId is defined before:
eg:
$paperCategoryId = $_POST['paperCategoryId']; for example.
Make sure as well that you clean values using mysql_real_escape_string() function prior to running the query.
If you want to use my example however, we'll need to alter the way the information is gathered into a more indepth multidimensional array to make good use of the table aliasing and JOINS. Sorry, never realized these were on seperate tables before -> mine won't work 'peachy' ;)
Give that a shot.

facets
09-14-2005, 12:36 AM
The 0=0 at the end of my SQL is working ok.
So could you inform me as to why this is not good?

Also the 0=0 is getting added because I can't work out how to stop the line from being created. Any ideas?




$var1 = 0;
$var2 = 0;
$var3 = 0;
$var4 = 0;
$var5 = 0;
$var6 = 0;

if (!empty($paperCategoryId) AND $paperCategoryId > 0) {
$var1 = $paperCategoryId;
$var2 = 'ausapapersummary.paperCategoryID';
}

$searchDb = array($var2, $var4, $var6);
$search = array($var1, $var3, $var5);

for($x = 0; $x<count($searchDb); $x++) {
$query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
}

Fou-Lu
09-14-2005, 05:06 AM
Hmm, I guess that 0=0 does evalutate to (bool) true, so I suppose it won't be a trouble to leave them in there. I've just always used 1=1 for my initial conditions, I assumed that 0=0 would be problematic.

I should be paying more attention to your variable initialization, thats where its sitting. The zero's I thought were comming from the form, so you probably won't need to make use of the $variable > 0 section either.
Try initializing your variables to an empty string and see how that works:


$var1 = '';
$var2 = '';
$var3 = '';
$var4 = '';
$var5 = '';
$var6 = '';

If that doesn't clear it up, also add this:


for($x = 0; $x<count($searchDb); $x++) {
if (!empty($searchDb[$x]))
{
$query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
}
}


Or, you can leave your variables initialized with zeros, and check for that:


for($x = 0; $x<count($searchDb); $x++) {
if ($searchDb[$x] > 0)
{
$query .= " AND ".$searchDb[$x]." = ".$search[$x]."";
}
}


Still recommend the dynamic route ;)

facets
09-14-2005, 05:24 AM
Thanks again for you assistance.

I've taken up your idea of a more dynamic structure.
Now the only thing I can't work out is how to get the $statement variable to set correctly. It displays

AND Array = 1 rather than
AND ausapapersummary.paperCategoryId = 1

Any ideas?



$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 1=1 ";

$fields = array('paperCategoryId', 'manufacturerName', 'cpl', 'stockId', 'adhesiveId', 'linerId', 'supplierId', 'suitabilityFoil', 'suitabilityYellowLight', 'suitabilityLabel', 'suitabilityOpacity', 'suitabilityBronze', 'suitabilityScreen', 'suitabilityIceBucket');

$statements = array('ausapapersummary.paperCategoryId', 'ausapapersummary.manufacturerName', 'ausapapersummary.cpl', 'ausapapersummary.stockId', 'ausapapersummary.adhesiveId', 'ausapapersummary.linerId', 'ausapapersummary.supplierId', 'ausapapersummary.suitabilityFoil', 'ausapapersummary.suitabilityYellowLight', '', 'ausapapersummary.suitabilityLabel', 'ausapapersummary.suitabilityOpacity', 'ausapapersummary.suitabilityBronze', 'ausapapersummary.suitabilityScreen', 'ausapapersummary.suitabilityIceBucket');

foreach ($fields as $field) { if (!empty($_POST[$field]) && $_POST[$field] > 0) {

$query .= " AND ".$statements." = ".$_POST[$field][$x]."";

}
}

echo $query;

Fou-Lu
09-14-2005, 05:56 AM
Ok lets see...
Instead of using a foreach loop, still use a for loop.
However, this route may be easier for you in the long run. Its the same principle as my initial code, but with alterations for the use of the table alias within the security checks:


$statement_check = array(
'ausapapersummary' => array ('paperCategoryId', 'manufacturerName', 'cpl', 'stockId', 'ahesiveId', 'linerId', 'supplierId', 'suitabilityFoil', 'suitabilityYellowLight', 'suitabilityLabel', 'suitabilityOpacity', 'suitabilityBronze', 'suitabilityScreen', 'suitabilityIceBucket'),
);
// I noticed that all are under ausapapersummary alias. Thats ok, I'd still do it this way as it allows easy manipulation to if the alias changes.

if (is_array($_POST['search']))
{
foreach ($_POST['search'] AS $key => $val)
{
// Here's the tricker, text fields are always set, even if its null:
foreach ($statement_check AS $table_alias => $value_check)
{
if (!empty($val) AND in_array($key, $value_check))
{
$query .= ' AND `' . $table_alias . '.' . $key . '` = "' . mysql_real_escape_string($val) . '"';
}
}
}
}

Now, this route requires that $_POST['search'] be an array. Which would be like so:


<select name="search[]">
<options />
</select>
<select name="search[]">
<options />
</select>

Or textfields or whatever else you want to use.
As well, this isn't quite a dynamic query since you've hardcoded so much into the script already - which is fine. This method will also eliminate any notices you may get from uninialized variables or undefined index's as well.
This method is also untested, so you may want to throw it onto a seperate file to test it first.

facets
09-14-2005, 01:45 PM
I've gone down another path.. i hope you don;t mind so much.
Just one small glitch now. With a switch option.
Do the bolded lines look right. They don;t appear to be reading the statement.



$fields = array(
'ausapapersummary.paperCategoryId' => 'paperCategoryId',
'ausapapersummary.manufacturerName' => 'manufacturerName',
'ausapapersummary.cpl' => 'cpl',
'ausapapersummary.stockId' => 'stockId',
'ausapapersummary.adhesiveId' => 'adhesiveId',
'ausapapersummary.linerId' => 'linerId',
'ausapapersummary.supplierId' => 'supplierId',
'ausapapersummary.suitabilityFoil' => 'suitabilityFoil',
'ausapapersummary.suitabilityYellowLight' => 'suitabilityYellowLight',
'ausapapersummary.suitabilityLabel' => 'suitabilityLabel',
'ausapapersummary.suitabilityOpacity' => 'suitabilityOpacity',
'ausapapersummary.suitabilityBronze' => 'suitabilityBronze',
'ausapapersummary.suitabilityScreen' => 'suitabilityScreen',
'ausapapersummary.suitabilityIceBucket' => 'suitabilityIceBucket',
);

foreach ($fields as $statement => $field) { if (!empty($_POST[$field]) && $_POST[$field] > 0) {

$action = isset($_GET['action']) ? $_GET['action'] : '';

switch($action) {
case "ausapapersummary.manufacturerName" : $query .= " AND ".$statement." LIKE '%".$_POST[$field]."%'"; break;
case "ausapapersummary.cpl": $query .= " AND ".$statement." = ".$_POST[$field].""; break;
default: $query .= " AND ".$statement." = ".$_POST[$field].""; break;
}

}
}

Fou-Lu
09-14-2005, 03:28 PM
The switch as a function itself is correct, you don't need to break your default, but I don't think that will cause any problems. I'm not certain offhand from looking at it if it will work the way you want to, but in my head its right.

Escape these ones though:
$_POST[$field]
with
mysql_real_escape_string($_POST[$field]);
SQL injections are not fun.
As well, you may want to consider changing this one:
isset($_GET['action']) ? $_GET['action'] : '';
to:
isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
depending on if action can come from either a URL or through a form.

facets
09-15-2005, 04:41 AM
HI, I'm having issues with two fields in this code. An they both happen to be text input fields. If I go back to some older code I can get them working but i'd liek to bring this all into one 'function' (wrong terminology but you et the idea)

I'm guessing that the bolded like is not grabbing the variables from the input correctly.

Any suggestions?

tia.




$manufacturerName = $_POST['manufacturerName'];
$cpl = $_POST['cpl'];

$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 1=1 ";

// ******* CHILD 2

$fields = array(
'ausapapersummary.paperCategoryId' => 'paperCategoryId',
'ausapapersummary.stockId' => 'stockId',
'ausapapersummary.adhesiveId' => 'adhesiveId',
'ausapapersummary.linerId' => 'linerId',
'ausapapersummary.supplierId' => 'supplierId',
'ausapapersummary.suitabilityFoil' => 'suitabilityFoil',
'ausapapersummary.suitabilityYellowLight' => 'suitabilityYellowLight',
'ausapapersummary.suitabilityLabel' => 'suitabilityLabel',
'ausapapersummary.suitabilityOpacity' => 'suitabilityOpacity',
'ausapapersummary.suitabilityBronze' => 'suitabilityBronze',
'ausapapersummary.suitabilityScreen' => 'suitabilityScreen',
'ausapapersummary.suitabilityIceBucket' => 'suitabilityIceBucket'
);

foreach ($fields as $statement => $field) { if (!empty($_POST[$field]) && $_POST[$field] > 0) {

$query .= " AND ".$statement." = ".mysql_real_escape_string($_POST[$field])."";
}

}

$var9 = 0;
$var10 = 0;
$var11 = 0;
$var12 = 0;

if (!empty($manufacturerName)) {
$var9 = $manufacturerName;
$var10 = 'ausapapersummary.manufacturerName';
}

if (!empty($cpl)) {
$var11 = $cpl;
$var12 = 'ausapapersummary.cpl';
}

$searchDb1 = array($var10, $var12);
$search1 = array($var9, $var11);

for($x = 0; $x<count($searchDb1); $x++) {
$query .= " AND ".$searchDb1[$x]." LIKE '%".$search1[$x]."%'";
}

echo $query;

Fou-Lu
09-15-2005, 10:24 AM
Hmm...
It looks okay, the only thing that may throw it off would be the integer comparison for the string data; which shouldn't be a problem in this case. Non-empty string values with '>0' operation should return true.

If you want, you can give me a copy of your html and a sql dump of the tables and data in use, and I can generate a fully working example for this. I noticed you want to complete this on your own, but you can use my version as a reference (with detailed comments as well). I also noticed you are querying for both '=' and 'LIKE'. You can choose either one of them, its a matter of what you are looking for. LIKE may return more results on a query than an '=' which normally includes one row except if the name or id or whatever value is used more than once.

facets
09-15-2005, 12:52 PM
JACKPOT!!!

Thank you for the tip.
The only two fields not returning numbers was the two text inputs i'm having trouble with. SO.. your comment trggered a thought :
>> Non-empty string values with '>0' operation should return true.

So I removed
&& $_POST[$field] > 0 and it (kid of) worked.
Just an sql error telling me that the following is incorrect 'stockId = text' .
Which is obvious.

SO.. How would I formulate a && $_POST[$field] > 0 to include 'must have data'?

then I can put my If statement back to process '=' or '%LIKE%'.
(I need to match exact records.).

thanks again for your valuable assistance!!!

Fou-Lu
09-15-2005, 02:33 PM
Hey cool, I didn't think that would cause a problem, but there you go!
Here's how to fix the string error with your sql:


$query .= " AND ".$statement." = ".mysql_real_escape_string($_POST[$field])."";
// Change this to:

$query .= " AND " . $statement . " = '" . mysql_real_escape_string($_POST[$field]) . "'";

All thats changed is the value for statement, I threw single quotations around it.
For exact matches, use '='. Like will match anything containing:
%VAL = Anything that ends in VAL
%VAL% = Anything that contains VAL
VAL% = Anything that begins with VAL.
If you prefer to use LIKE (I don't, I like to use '=' when available), use LIKE 'VAL' which will only match val.
Hopefully this will finish this one up ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum