Eliminate duplicated values in an array that is being populated from a query looping

[Yess]

Hi,

Apologies for the long explanation I'm just trying to be as clear as possible.

Basically, I have a query that looks for products matching the criteria selected by the user through check boxes. The query is in a loop that will loop as many times as the number of criteria selected by the user.

E.g, if the user selected 2 criteria (criteria_id 11 and criteria_id 46), The query will loop the first time looking for products meeting criteria 11, and next time looking for products meeting criteria 46. Every match is assigned to an array and then arrays are the printed.

The problem is that I want to print just the products that meet all the selected criterias. At the moment as long as there is at least one product meeting the first criteria (even if it doesn't meet the second), all products are printed.

How can I print only the ones that match all the criteria selected?

Thanks any help will be very much appreciated as I'm really frustrated by now!

<?php
 
 
 
 
 
$queriesWithMatchs=0; $zero=0; $rowMatches=array(); $id=array(); $arrayOfId=array();
 
 
//check that the form has been submitted and that values have been assigned. Then asign this values to variables used in queries.
 
if (isset($_POST['submitte'])) {
 
 
 
//check that a criteria was selected otherwise dont proceed.Open main div to print within the main body.
if (!empty($_POST['epeat_checkbox'])) {
 
 
 
 
if (isset($_POST['org'])) {
 
        $org=$_POST['org'];
 
                        }
                        else {//do something
                        }
 
 
if (isset($_POST['prodCrit'])) {
 
    is_array($prodCrit=$_POST['prodCrit']);
                        echo $prodCrit[0].'<br/>';
 
                        }
                        else {//do something
                        }
 
 
 
 
 
 
 //count how many criterias were selected to make the querie loop just as many times   
$criteriaSelected= $_POST['epeat_checkbox'];
 
      $numberOfCriteriaSelected = count($criteriaSelected);
 
 
            for($i=0; $i < $numberOfCriteriaSelected; $i++)
                {
                 $criteriaId=$criteriaSelected[$i] ;
 
                  echo('criteria id:'.$criteriaId . "<br/> ");
 
 
 
 
        require_once ('../mysqli_connect.php');
 
 
 
 
 $q = "SELECT product.model AS model, product.id_product AS id,brand.brand_name AS brand, product_type.type_name AS types FROM brand, product_type, product, $prodCrit[0]  WHERE product.id_product = $prodCrit[0].id_product AND $prodCrit[0].id_criteria=$criteriaId  AND product_type.id_type=product.id_type AND product.id_brand=brand.id_brand;";
        $r =mysqli_query ($dbc, $q) or die(mysqli_error($dbc)); // Run the query.
 
 
 
        if ($r) { // If it ran OK, get the records and increment $rowsPerCriteria by one every time there at least one match per query, that is when the selected criteria(i) is matched.
 
 
          $rowsPerCriteria=0; 
                  while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
 
                //add query results to this array each time it loops. 'model' is the alias given in the query "AS"  
                $rowsPerCriteria++;
                echo 'count id'. count($id).'<br/>';
 
                 $id[] = $row['id'];
                 $rowMatches[] = $row['model'];
                $brandMatches[] = $row['brand'];
                $typeMatches[] = $row['types'];
 
 
                }//end of while
                echo'1 query';
 
                while(list($key, $val)=each($id)){
                echo $val." ";
                }
 
                if (in_array($row['id'],$id)){
                echo'in array'.$row['id'] .'<br/>';
 
                }
 
                else{
                echo' not in array'.$row['id'] .'<br/>';
 
                }
 
        //Check that the query return at least a record and if it did, increment the $querywithmatches by one. 
 
        if ($rowsPerCriteria>$zero)
          {
                  $queriesWithMatchs+=1;
          }
 
 
 
          mysqli_free_result ($r);
    }//en of if $r
     else{
             echo' didnt run';
         }
 
 
 
    }//en of for loop    
 
 
 
//Compare the numb of queries with matches to the numb of selected criteria, if it is the same start printing.
        if($queriesWithMatchs==$numberOfCriteriaSelected&&$numberOfCriteriaSelected>0){
 
 
        sort($id);
 
 
 
 
        echo '<Table cellpadding="5">';
             echo'<th> Type</th> <th> Brand</th><th> Model</th>';
      $values=array_map(null,array_values($typeMatches), array_values($brandMatches),array_values($rowMatches),array_values($id));
 
            foreach($values  as $v)
        {
            list($v1, $v2, $v3, $v4)=$v;
 
 
            echo'<tr><td>'. $v1.' </td> <td>'. $v2.'</td><td>'. $v3.'</td><td> '.$v4.'</td></tr>';
 
 
        }
 
 
 
 
 
         echo'</table>';
         echo '</div>';
        } 
 
        else{    
        echo '<h4> Products matching all your selected criteria</h4>';
        echo 'Sorry no product matches all you selected criteria';
        echo '</div>';
         }
 
 
 
    }
    else{ 
 
    echo'<h4>No criteria was selected</h4>
 
    exit;
    }
 
 
}
 
?>

[Dormilich]

I’d say, construct an appropriate SQL query that contains all conditions to meet (i.e. if there are 2 conditions, then your WHERE clause needs to contain 2 conditions, etc.)

[Yess]

Thanks for the quick reply. The problem is that the values I will be looking for are coming from checkboxes and the user may select one to many boxes. So I may be wrong but I don't think that would solve my problem. Perhaps there is another way???

[Rabbit]

There's no need to run a SQL query for each criteria. Combine the criterian into one query and then run that.

[Yess]

Forgive me if I'm misunderstanding what you said. How can I combine all the criteria into one query if I don't know which criteria and how many criterions the user will select? I'm a complete newbie and don't know how to do that.

[Rabbit]

Let me ask you this, how do you know how many queries to run if you don't know how many criterion will be selected beforehand? It's the same thing. But instead of running a different query for each criteria, you are instead building a dynamic sql string. Appending the criteria for each one selected. And then running that at the end.

[Yess]

I think I know what you mean i just don't know how to do it. I'm a newbie to both php and sql. I would really appreciate it if you could provide me with an example. Thanks!

[Rabbit]

In pseudocode, it'd be something like this

strSQL = "SELECT * FROM Table1 WHERE "
 
For Each criteria In criterion
   strSQL = strSQL + criteria + " OR "
End For
 
Remove last " OR " from strSQL
 
Execute strSQL

I assume it's an OR criteria from what I've heard. But for all I know, you mean to use an AND.

[Yess]

Thanks a lot, I'll give it a go and shall let you know the outcome.