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?

stackoverflow.com/questions/20203063/mysql-where-id-is-in-array — urfusion
– urfusion, Commented Jul 7, 2017 at 11:25
So should i use SELECT * FROM files WHERE subject_code IN ('".$result."')"); Should is use mysql fetch array? — Electronics For KTU
– Electronics For KTU, Commented Jul 7, 2017 at 11:28
Check my answer below. I have updated it as result is an associative array. — Ninad Ramade
– Ninad Ramade, Commented Jul 7, 2017 at 12:02

codeit · Accepted Answer · 2017-07-07 13:01:52Z

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 7, 2017 at 13:01

codeit

1118 bronze badges

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

Comments

Fernan Vecina · Accepted Answer · 2017-07-07 11:35:43Z

1

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

answered Jul 7, 2017 at 11:35

Fernan Vecina

1631 silver badge9 bronze badges

Comments

Bibhudatta Sahoo · Accepted Answer · 2017-07-07 11:42:10Z

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 7, 2017 at 11:42

answered Jul 7, 2017 at 11:26

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

1 Comment

Electronics For KTU Over a year ago

But i dont know SC1,SC2 its in $result.How to convert $result to above format?

Ninad Ramade · Accepted Answer · 2017-07-07 12:13:36Z

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 7, 2017 at 12:13

answered Jul 7, 2017 at 11:35

Ninad Ramade

2762 silver badges9 bronze badges

6 Comments

Electronics For KTU Over a year ago

This is not working because $result is associative array.Can you help me with this..

Ninad Ramade Over a year ago

Edited the answer. Please mark my answer as correct if it works. Thanks :)

Electronics For KTU Over a year ago

Error array_values() expects parameter 1 to be array, object given

Electronics For KTU Over a year ago

Out of all the answers this is exactly what i want but still cannot make it to work..

Ninad Ramade Over a year ago

So when you said that it is an associative array which is not. it is an object. Answer updated again.

|

Ravi Kumar · Accepted Answer · 2017-07-07 11:28:19Z

0

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

answered Jul 7, 2017 at 11:28

Ravi Kumar

3192 silver badges13 bronze badges

2 Comments

Electronics For KTU Over a year ago

But i dont know SC1,SC2 its in $result.How to convert $result to above format?

Ravi Kumar Over a year ago

$ids = join("','",$arrayOfValues); $sql = "SELECT * FROM files WHERE subject_code IN ('$ids')";

zartab · Accepted Answer · 2017-07-07 11:36:31Z

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 7, 2017 at 11:36

answered Jul 7, 2017 at 11:31

zartab

12 bronze badges

2 Comments

Electronics For KTU Over a year ago

The $result array has the subjects but how to convert $result to above format?

zartab Over a year ago

@ElectronicsForKTU implode( "," , $YourArray)

Dharmendra Singh · Accepted Answer · 2017-07-07 11:40:55Z

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 7, 2017 at 11:40

Dharmendra Singh

1,25614 silver badges22 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

Comments

Comments

1 Comment

6 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

1 Comment

6 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related