How Do I Append A SELECT Statement With AND/OR Variables?

[HowdeeDoodee]

I need to append my SELECT statement with checkbox value combinations. The user can select one or all of the six checkboxes. The checkbox values do not appear in the db at the same time so I would want the SELECT statement to pull up different values separated by the OR operator not the AND operator. Checkbox values separated by an AND operator would find no records. The checkbox values would be associated with the Source field in the db.

The checkbox values coming in from the form are ES, TH, SM, NV, TR, and EN. In the code below, The value from the form is made equal to a variable, the variable is put in a string with the word Source. That string is then made equal to another variable.

My question is...how do I code the query statement so any of the checkbox variables are searched? What you see below needs correction.

Edited to add: One of the problems with the code below is that I do not know how to handle a variable if there are no values in the variable. If the user selects two checkboxes, the query statement at the bottom the this code has six variables in it. In this example, four of those variables would be empty. Don't I need a way to avoid empty variables? Thank you in advance for any replies.

$query = "SELECT * FROM `View2` WHERE `Topic` REGEXP '[[:<:]]($SeeAlso)[[:>:]]'";

  if(!empty($ES)){ 
   $ES = "'ES'"; 
   $ES1 = "`Source`={$ES}"; 
   } 

  if(!empty($TH)){ 
  $TH = "'TH'";

$TH1 = "`Source`={$TH}";
}

if(!empty($SM)){
$SM = "'SM'";
$SM1 = "`Source`={$SM}";
}

if(!empty($NV)){
$NV = "'NV'";
$NV1 = "`Source`={$NV}";
}

if(!empty($TR)){
$TR = "'TR'";
$TR1 = "`Source`={$TR}";
}

if(!empty($EN)){
$EN = "'EN'";
$EN1 = "`Source`={$EN}";
}

//the following code needs correction

$query = $query . AND . ($ES1 OR $TH1 OR $SM1 OR $NV1 OR $TR1 OR $EN1);

 

[DRJ478]

I would recommend to collect the additional parameters in an array and then use impolde() to add the OR operator:

 $condition[] = "`Source`={$TH}";
  }
  
  if(!empty($SM)){
  $SM = "'SM'";
  $condition[] = "`Source`={$SM}";
  }

  if(!empty($NV)){
  $NV = "'NV'";
  $condition[] = "`Source`={$NV}";
  }

  if(!empty($TR)){
  $TR = "'TR'";
  $condition[] = "`Source`={$TR}";
  }

  if(!empty($EN)){
  $EN = "'EN'";
  $condition[] = "`Source`={$EN}";
  } 
# implode with 'OR'
$condString = implode(" OR ",$condition);
$query = $query . "AND ($condString)";
# just for inspection
echo($query);

 

[hos2]

hmmmm I don't get the whole picture of you're problem. if the checkbox values are stored in one field you could first loop through the values and make the or string out of it

while ( list($field, $value) = each ($HTTP_POST_VARS)) {
 if ($field <> "Submit" ){
  	$orvar=$orvar . '[yourefieldname] LIKE '%Source' .$value ."%' OR ";
			
    }

$orvar=substr($orvar,0,strlen($orvar)-3); // get rid of the last OR

$query = "SELECT * FROM `View2` WHERE " .$orvar

in this example I suppose you don't have named you're checkboxes as an array.


and why use the word source ??

you can als use something like a # so the value in the field would be something like

#TR#EN#SN

in this way you can easily search on #EN and not make the mistake that some other stringcombination or word matches EN

 

[hos2]

$orvar=$orvar . "[yourefieldname] LIKE '%Source" .$value ."%' OR ";
[code]
sorry had some quotes wrong ;)

 

[HowdeeDoodee]

Thank you for the replies. After the array has been handled in the script, can anyone give me the syntax that would put the proper clause at the end of this statement using `Source` as the second search variable variable?

$query = "SELECT * FROM `View2` WHERE `Topic` REGEXP '[[:<:]]($SeeAlso)[[:>:]]'";

 

[DRJ478]

What do you mean?

After the array has been handled in the script...

The solution has not changed. Sometimes I am wondering if too many replies make the OP not read the posts carefully anymore.
The solution is up there!

 

[hos2]

perhaps its easier if you put an example of some values in a field or more fields here because frankly I got no idea what you plan to do with that 'Source' or what the benefit is of that thing or string.

I already put the complete syntax here for you're answer but perhaps I got it wrong what you meant with that 'Source' thingy. I thought you connected it to every value and got a fieldvalue of

'SourceENSourceTRSourceNV' or something like that. example of mine is based on valuestrings like this

 

[HowdeeDoodee]

OK, I should have said "after the data from the form has been handled in the script." Please accept my apologies.

The title of this thread is: "How Do I Append A SELECT Statement With AND/OR Variables?"

I have built a form where the user can search using two columns as a criteria rather than just one column. Therefore the SELECT statement will need to search two columns in the db rather than just one column. To solve my problem I set up a sample database with known records in the db. The fields in the db are Source, Topic, Subtopic, and References.

Now, for any person who needs to see actual SELECT statement syntax that works, let me give you an example of syntax that works. For people out there who are novices, you can go to phpAdmin if available and play around with various syntax configurations like I did. Here is the SELECT statement that will give all the records for Topic = AAA. I will be able to use this syntax with the script snippet I posted at the beginning of the thread by substituting the actual values in the following SELECT statement with the variables found in the code snippet at the top of the thread.

$sql = 'SELECT * '
        . ' FROM `View2` '
        . ' WHERE `Topic` REGEXP "AAA"'
        . ' AND `Source` = "ES"'
        . ' OR ( `Topic` REGEXP "AAA"'
        . ' AND `Source` = "NV")'
        . ' OR ( `Topic` REGEXP "AAA"'
        . ' AND `Source` = "TH")'
        . ' OR ( `Topic` REGEXP "AAA"'
        . ' AND `Source` = "SM")'
        . ' OR ( `Topic` REGEXP "AAA"'
        . ' AND `Source` = "TR")'
        . ' OR ( `Topic` REGEXP "AAA"'
        . ' AND `Source` = "EN")';

 

[DRJ478]

Your SQL statement should be optimized - and that is what the MySQL server will do and spend CPU time on.
Suggestion 1:

$sql = "SELECT *
  FROM `View2`
  WHERE `Topic` REGEXP "AAA"
  AND (
  `Source` = "ES" OR 
  `Source` = "NV" OR 
  `Source` = "TH" OR 
  `Source` = "SM" OR 
  `Source` = "TR" OR 
  `Source` = "EN")";

In the above example the AND pertains to the entire expression inside the parantheses.
Or even easier a FIND_IN_SET:

$sql = "SELECT *
  FROM `View2`
  WHERE `Topic` REGEXP "AAA"
  AND 
  FIND_IN_SET(`Source`,"ES,NV,TH,SM,TR,EN")";