Jump to content


Photo

Holy SQL Batman!


  • Please log in to reply
21 replies to this topic

#21 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 30 November 2005 - 02:08 PM

What I, err, wrote worked for me.

<{POST_SNAPBACK}>

Did yours have the new `type`?

#22 Detail

Detail

    King Detail

  • Hosted
  • 7,767 posts
  • Location:Dayonic
  • Projects:Dayvi.com
  •  Blu Spy

Posted 11 January 2006 - 04:16 PM

I'm back with more complex SQL querys..

SELECT k.id, k.title, k.latest, k.lateby, k.indent, k.`desc` , k.a_topic, r.`date`, r.`bymem`, 
COUNT( i.`id` ) topicsCount, COUNT( c.`id` ) postsCount
FROM `cats` k
LEFT JOIN `read_f` r ON ( r.`forum` = k.`id` AND r.`bymem` = '$members[id]' )
LEFT JOIN `items` i ON i.`cat` = k.`id`
LEFT JOIN `comments` c ON c.`inreplyto` = i.`id`
WHERE `a_topic` = '1' AND `indent` >=1
GROUP BY k.`id`
ORDER BY `order` ASC LIMIT 0, 30


This query gets the info form my forums
http://www.acidvat.c...php?view=forums

The bit of the query that is causing problems is
LEFT JOIN `items` i ON i.`cat` = k.`id`
LEFT JOIN `comments` c ON c.`inreplyto` = i.`id`


As you can see on the forums http://www.acidvat.c...php?view=forums
Topics and Posts are the same ammount.
This is not a php problem, because i get the same resault in PHPMyAdmin.

Anyone know why my query is giving postsCount and topicsCount as the same number?

Edited by Detail, 11 January 2006 - 04:17 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users