Query count

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Bandy
Forum Newbie
Posts: 22
Joined: Sat Oct 21, 2006 5:14 am
Location: Croatia

Query count

Post by Bandy »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hi

I am trying to make count of specific content  in mysql database
For example: i want to make content menu and for each content i want to know how many  headlines is in content.

Sports(15)    // 15 headlines in content Sports
Politics(10)   // 10 headlines in content Politics
 Music(3)     // 3 headlines in content Music


My database look like this:

[syntax="sql"]
CREATE TABLE headlines (
  id		INT NOT NULL auto_increment,
  title  	TEXT NOT NULL,
  text	      TEXT NOT NULL,
 contid   INT NOT NULL,  
		PRIMARY KEY (id)
);


CREATE TABLE content(
  contid int not null,
  contentname text,
  about text,
  PRIMARY KEY(contid)
);
Do i make query for each content and then count it, or is there simpler way.

Thanks


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Do a query for it all then do a loop that does it for each row of data.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

I think he knows it can be done that way. He just wants to know if he can do it in a single query. I don't know if that can be done or not, I'm waiting for someone more intelligent to reply :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

SELECT COUNT(*) as count, headlines.title
FROM headlines
LEFT JOIN content USING (contid)
GROUP BY headlines.id
Maybe..


Moved to databases.
User avatar
Bandy
Forum Newbie
Posts: 22
Joined: Sat Oct 21, 2006 5:14 am
Location: Croatia

Post by Bandy »

I've came with this solution:

Code: Select all

SELECT COUNT(*) AS count, headlines.contid 
FROM headlines
LEFT JOIN content
USING (contid) GROUP BY content.contid
with code:

Code: Select all

$result = mysql_query($query) or die ("Query wrong: $query. " . mysql_error());


if (mysql_num_rows($result) > 0)
{

	while($row = mysql_fetch_object($result))
	{
	 echo '<ul>';
	 switch($row->contid)
	 	{
		case 1:
			echo '<li>Sports ( '.$row->count.' )</li>';
			break;
		case 2:
			echo '<li>Politics ( '.$row->count.' )</li>';
			break;
		case 3:
			echo '<li>Music ( '.$row->count.' )</li>';
			break;
			}
	echo '</ul>';
	}
}
It is not universal but ill use it, there could be better way to directly use contentname from content table in mysql,
any ideas?

Thanks
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

All you need to do is this to get the content name

Code: Select all

SELECT COUNT(*) AS count, content.contentname 
FROM headlines
LEFT JOIN content
USING (contid) GROUP BY content.contentname
User avatar
Bandy
Forum Newbie
Posts: 22
Joined: Sat Oct 21, 2006 5:14 am
Location: Croatia

Post by Bandy »

Sometimes i don't think.

Thanks.
Post Reply