CODEWITHSUNDEEP
phpmysqlsqlarrays
Pooja
Pooja

Reputation: 613

How to create an SQL query using an array?

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 : enter image description here

Upvotes: 1

Views: 319

Answers (2)

id'7238
id'7238

Reputation: 2666

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

Upvotes: 0

eventHandler
eventHandler

Reputation: 1211

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.

Upvotes: 1

Related Questions