Query help?

[Mr Chris]

Hi All,

 

I have a query in php which works great:

 

$query = mysql_fetch_array(mysql_query("SELECT * FROM stories WHERE unix_timestamp(published) <= unix_timestamp(NOW()) order by story_id desc"));

 

 

However I want to incorporate another query in it, but I’m not sure how

 

$query = mysql_fetch_array(mysql_query("SELECT * FROM stories, (SELECT count(*) FROM comments WHERE story_id = stories.story_id)  WHERE unix_timestamp(published) <= unix_timestamp(NOW()) order by story_id desc"));

 

Can someone help?

 

Thanks

[whiterabbit]

In order to do that, you will have to use a Join statement.  You can either do Left Join or an Inner Join.  I would suggest reading up on those so you can figure out which one you need, but it is real easy to switch between the 2 by just changing INNER to LEFT...so your query would look like

 

SELECT * FROM stories, (SELECT count(*) FROM comments WHERE story_id = stories.story_id)  WHERE unix_timestamp(published) <= unix_timestamp(NOW()) order by story_id desc"

 

$query = "
   SELECT
      s.*,
      count(c.*) as total
   FROM
      stories as s
   INNER JOIN
      comments as c
   ON
     c.story_id = s.story_id
   WHERE
      unix_timestamp(s.published) <= unix_timestamp(NOW()) 
   ORDER BY
      s.story_id DESC
"