detect duplicate array values php, and then dynamically generate SQL query

Question

so far I have these two arrays:

 $field_name[] =  $selectedArr['field_name'];
 $field_value[] =  $selectedArr['field_value']; 

one of these arrays contains field names while the other contains field values. Now, if two or more field names array values are the same then the operator in the SQL query should be OR (if not the same AND) between these two or more fields and AND between the rest of the fields.

so if say:

$field_name[0]="Type";
$field_name[1]="Type";
 $field_name[2]="Varietal";

and

$field_value[0]="Red";
$field_value[1]="White";
$field_value[2]="Chardonney";

how can I check this with a loop or anything else, and then based on the results dynamically generate sth like this:

$SQLQuery=("SELECT * FROM wines WHERE  $field_name[0]=$field_value[0] 
 OR  $field_name[1]=$field_value[1] AND $field_name[2]=$field_value[2]");

can anybody show me or direct me on how to do this? thank you in advance.

Answer 1

$field_name[0]="Type";
$field_name[1]="Type";
$field_name[2]="Varietal";


$field_value[0]="Red";
$field_value[1]="White";
$field_value[2]="Chardonney";

$result_array = array();
foreach ($field_name as $k=>$v)
{
    $result_array[$v][] = $v."='".$field_value[$k]."'";
}

foreach ($result_array as $k=>$v)
{
    $result_array[$k] = "(".implode(" OR ", $result_array[$k]).")"; 
}

echo implode(" AND ", $result_array);

UPDATE

Btw, if you want to end up with FIELD IN ('val1', 'val2',etc) instead of FIELD = 'Val1' OR FIELD = 'Val2' you can use this:

$result_array = array();
foreach ($field_name as $k=>$v)
{
    $result_array[$v][] = "'".$field_value[$k]."'";
}

foreach ($result_array as $k=>$v)
{
    if (count($result_array[$k]) > 1) $result_array[$k] = "$k IN (".implode(" OR ", $result_array[$k]).")"; 
    else $result_array[$k] = "$k = $v[0]";
}

echo implode(" AND ", $result_array);

Answer 2

You can use array_count() to get a list of how frequently each element shows up in array. Singles will have a count of 1, duplicated values will have a count of > 1 (e.g. 2,3,4,etc...).

Something like this should do the trick, assuming that $selectedArry contains ALL of the field/value pairs:

$data = array();
foreach($selectedArr as $sub) {
    if (!isarray($data[$sub['field_name']])) {
         $data[$sub['field_name']] = array();
    }
    $data[$sub['field_name']][] = $sub['field_value'];
}

$clauses = array()
foreach ($data as $key => $values) {
    if (count($sub) == 1) {
       $clauses[] = "($key = {$values[0]})";
    } else {
       $clauses[] = "($key IN (" . implode(',', $values) . "))";
}


$sql = "SELECT * FROM windows WHERE " . implode(' AND ', $clauses);

Answer 3

Assuming the array lengths are the same, iterate over one of them, do a compare on the sibling array to see if the values match. Use this logic to generate slots on a third array, $field_operator, where you set 'AND' or 'OR' on each pass.

Then for loop over the length once again and concatenate the arrays to construct a valid SQL query, something like this:

$sql .= $field_name . ' = ' . $field_value . ' ' . $field_operator;

Answer 4

First at all, you query logic is incorrect,
you would end-up getting lots of unexpected records

You can make use on something like

$arr = array();
foreach ($field_name as $key=>$name)
{
   if (!isset($arr[$name]))
   {
      $arr[$name] = array();
   }
   $arr[$name][] = $field_value[$key];
}

$where = array();
foreach ($arr as $column=$val)
{
   $where[] = "{$column} IN('".implode("',", $val).'")";
}

$sql = '.... where '.implode(' AND ', $where);

// the above example does not take care of sql injection

Answer 5

you can make life much easier by constructing your data arrays more smartly.

$wine_selection['Type'][] = "Red";
$wine_selection['Type'][] = "White";
$wine_selection['Varietal'][] = "Chardonney";

$sql = "SELECT * 
        FROM wines 
        WHERE Type IN (".implode(',', $wine_selection['Type']).")
        AND Varietal IN (".implode(',' $wine_seelction['Varietal']).")";