CODEWITHSUNDEEP
phpmysqlarrays
user2689525
user2689525

Reputation: 5

Using a php array in a mysql query

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());`**

Upvotes: 0

Views: 124

Answers (2)

Rohan Kumar
Rohan Kumar

Reputation: 40639

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_*

Upvotes: 1

Daniel W.
Daniel W.

Reputation: 32350

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'");

Upvotes: 2

Related Questions