How to generate a query using an array of delimited columns & values

Question

I have this array:

$filter=['color*black','color*blue','color*red','paint*apex','paint*dalton'];

Each value in $filter has two substrings separated by *. The first substring represents a database table column and the second represents a desired value for that column.

My products table looks like this:

id    name    color    paint
1     p1      black     compo
2     p2      red       dalton
3     p3      pink      apex
4     p4      blue      apex
5     p5      cream     compo

Using $filter, I need to search the products table and return all rows with a paint value of apex or dalton AND a color value of black, blue, or red.

The desired output is a mysql query that will only return these rows:

id    name    color    paint
2     p2      red       dalton
4     p4      blue      apex

Sergii K · Accepted Answer · 2017-05-06 11:17:14Z

2

If You need to construct a query like this SELECT * FROM products WHERE (color IN ('black', 'blue', 'red')) AND (paint IN ('apex', 'dalton')), then the code below might be useful (please, check it here):

$filter = array(
    0 => "color*black",
    1 => "color*blue",
    2 => "color*red",
    3 => "paint*apex",
    4 => "paint*dalton"
);

$elements = [];

foreach ($filter as $value) {
    list($before, $after) = explode('*', $value);
    $elements[$before][] = $after;
}

$parts = [];

foreach ($elements as $column => $values) {
    $parts[] = "(`$column` IN ('" . implode("', '", $values) . "'))";
}

$query = 'SELECT * FROM `products` WHERE ' . implode(' AND ', $parts);

Running this query against the given table data structure:

id    name    color    paint
1     p1      black     compo
2     p2      red       dalton
3     p3      pink      apex
4     p4      blue      apex
5     p5      cream     compo

will match the following rows:

2     p2      red       dalton
4     p4      blue      apex

edited May 6, 2017 at 11:17

answered May 6, 2017 at 11:02

Sergii K

8661 gold badge9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abilash Erikson Over a year ago

could you please run this with given table structure and entry .

Sergii K Over a year ago

@abilasher I've checked your question, but, unfortunately can't help you with it..

Sahil Gulati · Accepted Answer · 2017-05-06 10:44:23Z

1

Here we are using explode, foreach and array_values to achieve desired output.

Try this code snippet here

<?php

$filter = array(
    0 => "color*black",
    1 => "color*blue",
    2 => "color*red",
    3 => "paint*apex",
    4 => "paint*dalton");

$result=array();
foreach($filter as $value)
{
    list($before,$after)=explode("*",$value);
    $result["before"][$before]=$before;
    $result["after"][$after]=$after;
}
$result["before"]=  array_values($result["before"]);
$result["after"]=  array_values($result["after"]);
print_r($result);

Output:

Array
(
    [before] => Array
        (
            [0] => color
            [1] => paint
        )
    [after] => Array
        (
            [0] => black
            [1] => blue
            [2] => red
            [3] => apex
            [4] => dalton
        )
)

answered May 6, 2017 at 10:44

Sahil Gulati

15.2k4 gold badges28 silver badges43 bronze badges

2 Comments

Abilash Erikson Over a year ago

could you please tell how can i perform the sql operation .

Sahil Gulati Over a year ago

@abilasher You can try this one.. eval.in/788364 in the last SQL query i have given OR i have changed it to AND conditions between color and paint.

Collectives™ on Stack Overflow

How to generate a query using an array of delimited columns & values

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related