[Resolved] Subquery Help.

[Kudose]

Hello all. I am working with 4.0.22-standard.

I have this code:

$qqrt = "SELECT * FROM companies";
            if($city_id !== "all"){
              $qqrt .= " WHERE site_id = (SELECT site_id FROM locations WHERE city_id='".$city_id."')";
            }


      $query = mysql_query($qqrt);
      $tot_rows = mysql_num_rows($query);

If $city_id == all, then everything works fine. If $city_if !== all, then I get an error starting that $tot_rows doesn't have a valid resource.

I dont see a problem with the query....do you? Basically, I am trying to pull all records from companies where companies.site_id exists in locations. There is only 1 site_id in the table, but there are multiple city_id's with the same value.

TABLE companies

columns

site_id
coname
etc...

TABLE locations

columns

site_id
timestamp
city_id

TIA.

[AstroTeg]

I think using the WHERE IN clause will do what you're looking for:

... WHERE site_id IN (SELECT site_id FROM locations ...)

Untested but I think that might get what you're looking for.

But, I think you have a chance to clean up a potentially ugly query.

You have query within a query. They're good when you need them, but for what you're doing, I think a simple INNER JOIN will work just as nicely and be a lot faster (it'll reduce the amount of table/index scans):

(also, do you really need the *? I doubt it - in the query I use below, it'll return EVERYTHING. Instead, add each column, this will help improve query performance)

SELECT * FROM companies INNER JOIN locations ON locations.site_id = companies.site_id WHERE locations.city_id = [city ID]

Now, if a city ID is not provided, you just remove the 'WHERE locations.city_id = [city ID]' (or in coding terms, don't add the 'WHERE locations.city_id = [city ID]' clause unless a city ID has been provided).

Now, this query assumes each company has a location. If you want all the companies, even if they don't have a location, then a LEFT JOIN might be what you need. Check out inner and outer/left joins. They're a key to relating data between tables.

[Kudose]

I changed it to:

 $qqrt = "SELECT * FROM companies INNER JOIN locations ON locations.site_id = companies.site_id";
            if($city_id !== "all"){
              $qqrt .= " WHERE locations.city_id = '".$city_id."')";
            }

And I still get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

$city_id is printing as a number and it all works with $city_id == all.

grr.

[Kudose]

WHERE site_id IN doesnt work either.

[AstroTeg]

Post the resulting SQL string here. Or use mysql_error() to show the error. Or paste the sql string in phpmyadmin and run it and see what errors you are getting. You definitely have a SQL query error, but without seeing the sql or the query error, it'll be hard to troubleshoot.

[Kudose]

mysql_error fixed it. I dont know why I didnt try it....oh well.

I had an extra ) in there.

Thanks for your help. 🙂