Need help with search form results.

[fishwild]

Hopefully second times the charm.

 

I have a form that dynamically populates from the MySQL database using PHP.

 

Each distinct listing for three separate fields is shown in a series of check boxes.

 

The form, when submitted, populates three arrays based on checkbox selection. These arrays then run through the separate fields looking for a match for each instance selected.

 

 

THE PROBLEM:

One of the arrays ($form_array3) needs to run through a field with a possible list of delimited values. (i.e. Value1, Value 2, Value3, etc...)

 

If there is only one value in the field then the search works.

 

I need it to sort the delimited list and bring back the selected value to check against.

 

 

 

if($form_array1 || $form_array2 || $form_array3){
   $sql .= " WHERE ";
   if($form_array1){
      foreach($form_array1 as $fa1){
         $a[] = "form_array1='$fa1'";
      }
      $aj = join(" OR ",$a);
      $fj[] = "(".$aj.")";

   }
   if($form_array2){
      foreach($form_array2 as $fa2){
         $b[] = "form_array2='$fa2'";
      }
      $bj = join(" OR ",$b);
      $fj[] = "(".$bj.")";

   }
   if($form_array3){
      foreach($form_array3 as $fa3){
         $c[] = "form_array3='$fa3'";
      }
      $cj = join(" OR ",$c);
      $fj[] = "(".$cj.")";
   }

      $sql .= join(" AND ",$fj);

}

 

Thanks for the help! My head hurts and I have to clean the blood off the wall eventually.

[Yesideez]

Have you used those variables before?

 

If you use var_dump you can show the contents of your arrays - right-click the browser and view source to see the output nicely formatted.

 

var_dump($c);

[shlumph]

I THINK I read this correctly, and this is what I would do:

<?php
if($form_array1 || $form_array2 || $form_array3){

$sql .= " WHERE ";
$hit = 0;

if($form_array1) {
	$a = implode(",", $form_array1);
	$sql .= "form_array1 IN ({$a}) ";
	$hit++;
}

if($form_array2) {
	$a = implode(",", $form_array2);

	if($hit > 0) {
		$sql .= " OR ";
	}

	$sql .= "form_array2 IN({$b}) ";
	$hit++;
}

if($form_array3) {
	$a = implode(",", $form_array3);

	if($hit > 0) {
		$sql .= " OR ";
	}

	$sql .= "form_array3 IN({$b}) ";
}
}

 

But to be sure, what is it that your final SQL should look like, when put together? There is probably a better way to do what I did above, as well.

[fishwild]

I don't think I'm explaning this right. Let's try this.

 

The following is contains an array submitted through a form using GET.

The array contains values selected via checkboxes.

 

The code below checks to see if any of the array values are in the specific DB field.

For each instance a value exists in the DB field it puts that value into the $a array.

 

The DB field I am trying to check against contains (or can contain) a comma-delimited string of values.

 

I need the code below to go through that DB field which may or may not contain comma-separated values and put the ones that do into $a[].

 

 

 

if($form_array1){
      foreach($form_array1 as $fa1){
         $a[] = "form_array1='$fa1'";
      }
      $aj = join(" OR ",$a);
      $fj[] = "(".$aj.")";

   }

[shlumph]

What do you want your final SQL statement to look like? I was thinking you want something like this:

SELECT * FROM table_name WHERE form_array1 IN (1, 2, 3, 4) OR form_array2 IN (4, 6, 7, 10) OR form_array3 IN (1, 2, 3, 4) 

[fishwild]

It would be nice to use the IN function but I can't get it to work because of the comma-delimited list in the DB field.

 

 

[mrMarcus]

echo out your query and post so i can where your errors are.

 

if i'm understanding you correctly, i accomplished something like this a little while back, i just need to know more about what you're doing and what's happening.

[fishwild]

Here is an example of the SQL query:

 

SELECT field1, field2, field3 FROM database WHERE (field1='Value1' OR field1='Value2') AND (field2='Value1' OR field2='Value2') AND (field3 LIKE '%Value1%' OR field3 LIKE '%Value2%' OR field3 LIKE '%Value3%' OR field3 LIKE '%Value4%') AND date >= CURDATE() ORDER BY date DESC, field1 ASC

 

NOTE: I have gotten around the delimited list problem for field3 by using LIKE and including "%" in the SQL query. I don't really care for doing it this way, but an "=" will not bring me back any results due to the information in the field being delimited.