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

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

You should mention that in your question. Your sample code does not make clear that you don't use the same db for everything..
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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Related