How to get variables from MSQL query with WHERE IN clause

Question

I have a couple of MySQL tables where I run a query on like this:

$sql = "
SELECT my_item 
  FROM t1
     , t2 
 WHERE t1.id='$id' 
   AND t2.spec IN (208, 606, 645) 
   AND t1.spec = t2.spec
";

Note I am using the WHERE IN.

Next I run a query and use WHILE to try to get the results:

$retval = mysql_query($sql) or die('Query failed: ' . mysql_error());
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
$myitem = $row['my_item'];
echo "My item is $myitem<br />\n";
}

This prints three results, each with a different value for $myitem, based on the three options from the IN clause in the SELECT statement at the beginning.

How can I extract and store each of these three values in a separate variable each?

Thank you!

Wouldn't it be better if $myitem was an array, and then the 'variables' are simply the elements of the array? — Strawberry
– Strawberry, Commented Nov 26, 2015 at 10:05
That sounds like a good idea, Strawberry! To do that, should I use $myitem = array($row['my_item']); ? — Cucurigu
– Cucurigu, Commented Nov 26, 2015 at 10:07

Abhishek Sharma · Accepted Answer · 2015-11-26 10:28:16Z

2

use join

SELECT my_item FROM t1 join t2 
on t1.spec=t2.spec
WHERE t1.id='$id' 
AND t2.spec IN (208, 606, 645)

or create array

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
$myitem[] = $row['my_item'];
}

use this array likt that :-

foreach( $myitem as  $myitems){
echo "My item is $myitems<br />\n";
}

or use indexing

echo "My item is $myitems[0]";

edited Nov 26, 2015 at 10:28

answered Nov 26, 2015 at 10:05

Abhishek Sharma

6,6241 gold badge17 silver badges20 bronze badges

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

4 Comments

Cucurigu Over a year ago

Will give this a try, Rahautos! My problem, though, is extracting the 3 results in individual PHP variables from the while loop.

Cucurigu Over a year ago

Thanks a lot, Rahautos, it woks like a charm!

Abhishek Sharma Over a year ago

it's my pleasure :) - @Cucurigu

Abhishek Sharma Over a year ago

if my answer helps you please accept my answer @Cucurigu

Ananya Shandilya · Accepted Answer · 2015-11-26 10:10:35Z

0

You can store the $row['my_item'] in an array. Refer PHP arrays

answered Nov 26, 2015 at 10:10

Ananya Shandilya

13 bronze badges

Collectives™ on Stack Overflow

How to get variables from MSQL query with WHERE IN clause

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related