How to create an SQL query using an array?

Question

I have an Array with data and I want to create an SQL statement (In which I am going to use where to where in).

I have tried to make query but no success.

SQL will be like :

SELECT * 
FROM documents 
WHERE category = "recruitment" 
AND sub_category in("forms") 
OR category = "onboarding" 
AND sub_category IN ("sop") 
OR category = "policies" 
AND sub_category IN("forms");

and Array is this :

{"Recruitment":["Forms"],"Onboarding":["Sop"],"Policies":["Forms"]}

I have tried this code :

foreach ($db_sub as $data=>$key){
    $query = "where document.category = ".$data." or where document.sub_category in ('".$key."')";
}

But getting error array to string conversion. Plz help me.

Thanks in advance.

Error :

Can you share the generated query such that others could see how it looks like? — Nico Haase
– Nico Haase, Commented Jan 11, 2021 at 8:59
Also, please add more details about the error message itself - is this even related to MySQL, or is this a PHP error? — Nico Haase
– Nico Haase, Commented Jan 11, 2021 at 8:59
Other Issue That query will need some bracketing. Always when using AND and OR to ensure things are processed in the order you want they must be bracketed — RiggsFolly
– RiggsFolly, Commented Jan 11, 2021 at 9:15

eventHandler · Accepted Answer · 2021-01-11 09:30:01Z

1

You are trying to concatenate a string with an array. Since your array's values are also arrays, you will have to convert them to string first. It could be done with the join function.

foreach ($db_sub as $data=>$key){
    $query = "where document.category = ".$data." or where document.sub_category in ('".join(", ", $key)."')";
}

Now you shouldn't get array to string conversion error.

edited Jan 11, 2021 at 9:30

answered Jan 11, 2021 at 9:20

eventHandler

1,20913 silver badges24 bronze badges

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

Comments

id'7238 · Accepted Answer · 2021-01-11 09:52:45Z

0

Use this example to create the required SQL query:

$whereClause = [];
$params = [];

foreach ($db_sub as $data => $key){
    if (!is_array($key)) $key = [$key];
    $whereClause[] = '(documents.category = ? AND documents.sub_category IN (?' . str_repeat(', ?', count($key) - 1) . '))';
    $params[] = $data;
    $params = array_merge($params, $key);
}

$whereClause = !empty($whereClause) ? 'WHERE ' . implode(' OR ', $whereClause) : '';

$query = "SELECT * FROM documents $whereClause";

$sth = $pdo->prepare($query);
$sth->execute($params);
$result = $sth->fetchAll();

Here variable $pdo is a PDO object

edited Jan 11, 2021 at 9:52

answered Jan 11, 2021 at 9:45

id'7238

2,7261 gold badge6 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to create an SQL query using an array?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related