0

I have the following query with a subquery, this is the php code:

$staticsQuery = "SELECT * FROM statics WHERE mmsi = (SELECT mmsi FROM positions,active WHERE active.mmsi = positions.position_ID)";
$staticsResult = mysql_query($staticsQuery);
        while($rows = mysql_fetch_array($staticsResult)){
            echo $rows['time'];
            echo $rows['mmsi'];
            echo $rows['name'];
            echo $rows['imo'];
        }

The subquery part is normally working, which prints out results. I am not sure if this outer query part is correct: SELECT * FROM statics WHERE mmsi =

This is the error I am getting,

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\retrieve.php on line 44

The error obviously comes from mysql_fetch_array because the query does not return a result. Please don't mark this question as a duplicate, because there can be a lot of different type issues with this error message.

Any help is welcome!

Stev
  • 1
  • 5

2 Answers2

0

if you will ever have more than one result, you need IN not =

Dave
  • 991
  • 1
  • 7
  • 15
  • @Khalid credit to you – Dave Jun 11 '14 at 20:52
  • It will not ever have more than one result, so I think the = operator is good. – Stev Jun 11 '14 at 21:00
  • Please try to elaborate more and explain why your solution works – Itay Gal Jun 11 '14 at 21:13
  • Well my solution doesn't work so far. The = operator is also not working, but if Khalid is saying you have to use 'IN' instead of '=' if you have more than one result, then I think the '=' is correct. – Stev Jun 11 '14 at 21:16
0

You can rewrite it in two ways.

1) Fix the sub-query to use "IN"

SELECT * FROM statics WHERE mmsi IN (SELECT mmsi FROM positions,active WHERE active.mmsi = positions.position_ID)

2) Use a JOIN instead of the sub-query

SELECT s.* from statics INNER JOIN positions p ON s.mmsi=p.mssi INNER JOIN active a ON p.position_ID=a.mmsi

samottenhoff
  • 638
  • 4
  • 13