How to use an array in a WHERE sql clause?

Question

i am trying to run the following php code:

$con = mysql_connect("localhost", "root", "") or die('connection not made');
$db = mysql_select_db('name', $con) or die('db not selected');

$query1 = "SELECT * FROM nodesensors WHERE NodeID=2";
$result1 = mysql_query($query1, $con);
$sensorids = mysql_fetch_array($result1);

$query2 = "SELECT SensorID, Variable FROM sensors WHERE SensorID IN($sensorids)";
$result2 = mysql_query($query2, $con) or die('query not made');
$sensors = mysql_fetch_array($result2);

echo $sensors;

where i want to get only those sensors that have a SensorID, which is also a value in the 'sensorids' array. When i run the code i get the following:

Notice: Array to string conversion in C:\...\test.php on line 10
query not made

When i remove the "$" as follows:

$query2 = "SELECT SensorID, Variable FROM sensors WHERE SensorID IN(sensorids)";

the notice goes away, but still, the query is not made.

Is there any problem with the format of the 'sensorids' array?

Also, is 'echo' the right way to present the array or should i use another method?

Thanks a lot!

Answer 1

You don't have to use implode or anything, just combine your queries

SELECT SensorID, Variable FROM sensors WHERE SensorID IN
(
  SELECT id FROM nodesensors WHERE NodeID=2
)

A note for all the implode based answers:

That will only work if they select only 1 column in their first query, they are doing SELECT * which results in a multi-dimensional array and doesn't contain only sensor ids and hence that will fail. See the updated answer by diEcho to read more about that.

And use a newer and safer extension for MySQL stuff

How can I prevent SQL injection in PHP?

Answer 2

I would suspect you can do this in 1 query, but I don't know your name of the id field, might be SensorID or something.

$query2 = 
  "SELECT SensorID, Variable
   FROM sensors 
   WHERE SensorID IN (
     SELECT id
     FROM nodesensors
     WHERE NodeID=2
   )";
$result2 = mysql_query($query2, $con) or die('query not made');
$sensors = mysql_fetch_array($result2);

Alternatively:

$query2 = 
  "SELECT s.SensorID, s.Variable
   FROM sensors AS s
   INNER JOIN nodesensors ns ON ns.id = s.SensorID
   WHERE ns.NodeID=2";
$result2 = mysql_query($query2, $con) or die('query not made');
$sensors = mysql_fetch_array($result2);

Answer 3

fixes

• SELECT * is wrong, select only sensorID column from nodesensors

  SELECT sensor_column FROM nodesensors ...

• Use implode with glue ',' before applying it to second query

  $id_list = implode("','",$sensorids);

• Mysql IN takes comma separated values

  WHERE SensorID IN(" . $id_list . ")";

Answer 4

Try this and you are done, Please don't use Select * but use the only column contain senserid

<?php

    $con = mysql_connect("localhost", "root", "") or die('connection not made');
    $db = mysql_select_db('name', $con) or die('db not selected');

    $query1 = "SELECT * FROM nodesensors WHERE NodeID=2";
    $result1 = mysql_query($query1, $con);
    $sensorids = mysql_fetch_array($result1);

    $query2 = "SELECT SensorID, Variable FROM sensors WHERE SensorID IN(".implode(',',$sensorids).")";
    $result2 = mysql_query($query2, $con) or die('query not made');
    $sensors = mysql_fetch_array($result2);

    echo $sensors;

?>

Answer 5

Use Implode

$sensorids = implode(',',$sensorids);
$query2 = "SELECT SensorID, Variable FROM sensors WHERE SensorID IN($sensorids)";