How to create a query dynamically for an array of elements?

Question

I have a basic query as follows

$query = "SELECT * FROM files WHERE (subject_code = '".$subject_code."' )";

I have to modify this query to create a query where we have multiple subject codes fetched from an array.

$query1="SELECT subject_code FROM subject WHERE (sbranch = '".$branch."' )";
$result = mysqli_query($bmysqli, $query1);

Now result holds all the required subject_codes say result[0]=SC1,result[1]=SC2 etc....

Now I want to the original query to be modified something as follows

$query = "SELECT * FROM files WHERE (subject_code = SC1 OR subject_code = SC2 .....)";

Note that i don't know what are the values of SC1 SC2 etc.It depends on the branch and is inside $result.

How can i accomplish this?

https://stackoverflow.com/questions/20203063/mysql-where-id-is-in-array — urfusion, Jul 07 '17 at 11:25
So should i use SELECT * FROM files WHERE subject_code IN ('".$result."')"); Should is use mysql fetch array? — Electronics For KTU, Jul 07 '17 at 11:28
Check my answer below. I have updated it as result is an associative array. — Ninad Ramade, Jul 07 '17 at 12:02
Possible duplicate of [Mysql where id is in array](https://stackoverflow.com/questions/20203063/mysql-where-id-is-in-array) — Nico Van Belle, Jul 07 '17 at 13:05

Bibhudatta Sahoo · Answer 1 · 2017-07-07T11:42:10.413

1

You can use Mysql In operator Like this

SELECT * FROM files WHERE subject_code in ('SC1','SC2')";

You can achieve ('SC1','SC2') by using implode php function,
like this:

$subCodes = implode("','", $subject_array ); 
SELECT * FROM files WHERE subject_code in ('".$subCodes ."')"

edited Jul 07 '17 at 11:42

answered Jul 07 '17 at 11:26

Bibhudatta Sahoo

4,808
2
27
51

But i dont know SC1,SC2 its in $result.How to convert $result to above format? – Electronics For KTU Jul 07 '17 at 11:35

Ninad Ramade · Answer 2 · 2017-07-07T12:13:36.420

1

$query1="SELECT subject_code FROM subject WHERE (sbranch = '".$branch."' )";
$result = mysqli_query($bmysqli, $query1);
$codes=(array)$result; //this will convert it in normal array. old school typecasting ;)
$codes=array_values($codes);
$query = "SELECT * FROM files WHERE subject_code in (".implode(",",$codes).")";
//considering result is an object of subject codes.

CODE UPDATED ABOVE as it is an object.

You don't need to append to the first query. Just build a new query using MySQL in operator and php implode function.

edited Jul 07 '17 at 12:13

answered Jul 07 '17 at 11:35

Ninad Ramade

256
2
9

This is not working because $result is associative array.Can you help me with this.. – Electronics For KTU Jul 07 '17 at 11:53
Edited the answer. Please mark my answer as correct if it works. Thanks :) – Ninad Ramade Jul 07 '17 at 11:58
Error array_values() expects parameter 1 to be array, object given – Electronics For KTU Jul 07 '17 at 12:07
Out of all the answers this is exactly what i want but still cannot make it to work.. – Electronics For KTU Jul 07 '17 at 12:07
So when you said that it is an associative array which is not. it is an object. Answer updated again. – Ninad Ramade Jul 07 '17 at 12:12
just typecast with (array) – Ninad Ramade Jul 07 '17 at 12:14

score 1 · Answer 3 · answered Jul 07 '17 at 11:35

You can use implode to convert array to string comma delimited

like below:

$branch_array = array('subject1', 'subject2', 'subject3');
$branch = implode("','", $subject_array ); 

$query1="SELECT subject_code FROM subject WHERE sbranch  in ('".$branch."')";

result:

 'subject1', 'subject2', 'subject3'

Hope that resolve your issue

score 1 · Accepted Answer · answered Jul 07 '17 at 13:01

1

You can use a subquery like this:

$query = 'SELECT * FROM files 
WHERE subject_code IN (
    SELECT subject_code FROM subject WHERE sbranch = "' . $branch . '"
)';

answered Jul 07 '17 at 13:01

codeit

111
8

Wow beautiful answer. – Electronics For KTU Jul 07 '17 at 13:29
I dont know how to thank you i was working on this for hours and you provided a simple solution.. – Electronics For KTU Jul 07 '17 at 13:32
1

glad I could help :) – codeit Jul 07 '17 at 14:10

score 0 · Answer 5 · answered Jul 07 '17 at 11:28

0

SELECT * FROM files WHERE subject_code in ('SC1','SC2')

answered Jul 07 '17 at 11:28

Ravi Kumar

313
2
13

But i dont know SC1,SC2 its in $result.How to convert $result to above format? – Electronics For KTU Jul 07 '17 at 11:33
$ids = join("','",$arrayOfValues); $sql = "SELECT * FROM files WHERE subject_code IN ('$ids')"; – Ravi Kumar Jul 07 '17 at 11:36

zartab · Answer 6 · 2017-07-07T11:36:31.100

0

You can amend your php array to comma delimited string of SubjectCodes.

then:

$query1="SELECT * FROM files WHERE subject_code in (".$NewString.") ";

$NewString will look something like this:

"'SC1', 'SC2', 'SC3'"

edited Jul 07 '17 at 11:36

answered Jul 07 '17 at 11:31

zartab

1
2

The $result array has the subjects but how to convert $result to above format? – Electronics For KTU Jul 07 '17 at 11:34
@ElectronicsForKTU implode( "," , $YourArray) – zartab Jul 07 '17 at 11:39

score 0 · Answer 7 · answered Jul 07 '17 at 11:40

0

 $result = array(0=>'SC1',1=>'SC2');  //this is your array
 $branch = implode("','", $result );
 $query .= "
   SELECT * FROM files WHERE subject_code in ('".$branch."') "; //this is query

answered Jul 07 '17 at 11:40

Dharmendra Singh

1,186
12
22

How to create a query dynamically for an array of elements?

7 Answers7