Jump to content


Photo

join query count


  • Please log in to reply
3 replies to this topic

#1 desithugg

desithugg
  • Members
  • PipPipPip
  • Advanced Member
  • 281 posts
  • LocationScarborough

Posted 13 August 2006 - 06:54 PM

$query = "SELECT forums.pic,forums.name,forums.des,forums.poster,forums.timer,forums.id,threads.fid FROM forums left join threads on threads.fid = forums.id  where category = 'general' ORDER BY ors ASC";
$result = mysql_query($query) or die(mysql_error());


on the threads table i want to count how many threads are there that have the threads.fid = forums.id
do i use count i have tried count(threads.fid) and threads,count(fid) but didnt work. any other ideas?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 13 August 2006 - 07:06 PM

try
$query = "SELECT forums.pic,forums.name,forums.des,forums.poster,
		forums.timer,forums.id, COUNT(threads.fid) as threadcount 
		FROM forums left join threads on threads.fid = forums.id  
		where category = 'general' 
		ORDER BY ors ASC";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 desithugg

desithugg
  • Members
  • PipPipPip
  • Advanced Member
  • 281 posts
  • LocationScarborough

Posted 13 August 2006 - 07:07 PM

i ahve tried that barand but i get this error
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 13 August 2006 - 07:09 PM

Oops

$query = "SELECT forums.pic,forums.name,forums.des,forums.poster,
		forums.timer,forums.id, COUNT(threads.fid) as threadcount 
		FROM forums left join threads on threads.fid = forums.id  
		where category = 'general'
		GROUP BY forums.id 
		ORDER BY ors ASC";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users