build mysql query from dynamic php array

Question

I have multiple checkboxes with category names and their values. After every form submission an array is generated.

Here is the example:

Array
(
    [user_type] => Array
        (
            [0] => Freelancer
            [1] => Company
        )

    [category] => 19
    [sub_category] => Array
        (
            [0] => Website UI Designing  
            [1] => Website Development 
        )

)

now I want from above array build a mysql query like---

select * from table_name 
    where user_type in ('Freelancer','Comapny') 
    and category in (19) 
    and sub_category in ('Website UI Designing','Website Development')

any help will be appreciated. thanks

Does category always give one value, and that too integer?

Rajdeep Paul
– Rajdeep Paul

2016-04-28 20:02:46 +00:00
Commented Apr 28, 2016 at 20:02 — Rajdeep Paul
– Rajdeep Paul, Commented Apr 28, 2016 at 20:02
Are you using prepared statements?

Steve
– Steve

2016-04-28 20:26:54 +00:00
Commented Apr 28, 2016 at 20:26 — Steve
– Steve, Commented Apr 28, 2016 at 20:26

Steve · Accepted Answer · 2016-04-28 22:01:29Z

1

$sql = "SELECT * FROM table_name WHERE user_type IN (" .rtrim(str_repeat("?,", count($array["user_type"]), ",")) .") AND category IN (" .rtrim(str_repeat("?,", count($array["category"]), ",")) .") AND sub_category IN (" .rtrim(str_repeat("?,", count($array["sub_category"]), ",")) .")";

$parameters = array_merge($array["user_type"], $array["category"], $array["sub_category"]);

You'd need to prepare the $sql and then execute with the $parameters argument.

edited Apr 28, 2016 at 22:01

answered Apr 28, 2016 at 20:38

Steve

7766 silver badges13 bronze badges

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

Comments

splash58 · Accepted Answer · 2016-04-28 20:23:57Z

0

array_walk_recursive ($array, function (&$i) { 
      if ( ! is_numeric($i)) $i = '"' . $i . '"'; });

$ws = array();

foreach($array as $k=>$v) {
   if (is_array($v)) $v = implode(',', $v);
   $ws[] = $k . ' in (' . $v . ')';
}

echo $where = 'where '. implode(' and ', $ws);
//  where user_type in ("Freelancer","Company") and category in (19) and sub_category in ("Website UI Designing","Website Development")

edited Apr 28, 2016 at 20:23

answered Apr 28, 2016 at 20:01

splash58

26.2k3 gold badges25 silver badges35 bronze badges

1 Comment

splash58 Over a year ago

Glad to help. Good luck!

Collectives™ on Stack Overflow

build mysql query from dynamic php array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related