Using a php array in a mysql query

Question

I'm querying one database to get product stockcodes related to a news article

$result = mysql_query('
SELECT stockcode FROM news_related WHERE news = "'.$news_id.'"');

then I need to use the data taken from the stockcode column to query a second database. I'm using

$rows = mysql_fetch_array($result);

to put the info in to an array. How do I use that array in the second mysql query?

$also_result = mysql_query("SELECT * FROM WebProducts
WHERE WebProducts.stockcode THE ARRAY GOES HERE AND WebProducts.visible='Y'") or  die(mysql_error());`**

mysql_* usage is depricated

internals-in
– internals-in

2013-09-06 08:33:22 +00:00
Commented Sep 6, 2013 at 8:33 — internals-in
– internals-in, Commented Sep 6, 2013 at 8:33
Do you mean SELECT * The ALL is depreciated?

user2689525
– user2689525

2013-09-06 09:08:29 +00:00
Commented Sep 6, 2013 at 9:08 — user2689525
– user2689525, Commented Sep 6, 2013 at 9:08
php.net/manual/en/changelog.mysql.php

internals-in
– internals-in

2013-09-06 09:12:59 +00:00
Commented Sep 6, 2013 at 9:12 — internals-in
– internals-in, Commented Sep 6, 2013 at 9:12

Daniel W. · Accepted Answer · 2013-09-06 08:27:49Z

2

Sounds like a simple join for me.

mysql_query("SELECT * FROM WebProducts p
JOIN news_related n
ON p.stockcode = n.stockcode
WHERE n.news = " . $news_id . "
AND p.visible='Y'");

answered Sep 6, 2013 at 8:27

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

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

Comments

Rohan Kumar · Accepted Answer · 2013-09-06 10:04:57Z

1

Tr in a single query like,

$result = mysql_query('SELECT * FROM WebProducts WHERE WebProducts.stockcode IN 
             (SELECT stockcode FROM news_related WHERE news = "'.$news_id.'"
               AND WebProducts.visible="Y")');

From your approach it should be like,

$arrStock=array();
while($rows = mysql_fetch_array($result))
{
   $arrStock[]=$rows['stockcode'];
}
if(!empty($arrStock))
{
    $also_result=mysql_query("SELECT * FROM WebProducts WHERE WebProducts.stockcode
                  IN (".implode(',',$arrStock)." AND WebProducts.visible='Y'");

}

You know about the second parameter in mysql_query() which is connection identifier, in your case there are two databases so you should have 2 connections like $con1 and $con2

$result = mysql_query('SELECT * FROM WebProducts WHERE WebProducts.stockcode IN 
             (SELECT stockcode FROM news_related WHERE news = "'.$news_id.'"
               AND WebProducts.visible="Y")',$con1);// use $con1 for first db

and in the second query

  $also_result=mysql_query("SELECT * FROM WebProducts WHERE WebProducts.stockcode
              IN (".implode(',',$arrStock)." AND WebProducts.visible='Y'",$con2);
  // use $con2 for second db

Also the mysql_ is deprecated and will removed in the upcoming versions of PHP so use mysqli_*

edited Sep 6, 2013 at 10:04

answered Sep 6, 2013 at 8:26

Rohan Kumar

40.7k11 gold badges81 silver badges111 bronze badges

7 Comments

user2689525 Over a year ago

I can't, it's a second database on a different server.

Daniel W. Over a year ago

You should mention that in your question. Your sample code does not make clear that you don't use the same db for everything..

user2689525 Over a year ago

I did mention it was a second database.

Rohan Kumar Over a year ago

@user2689525 test the above answer I've made changes in it.

user2689525 Over a year ago

Doesn't seem to work. The data is in the $arrStock array but result returns nothing.

|

Collectives™ on Stack Overflow

Using a php array in a mysql query

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related