I always love that feeling when you beat a problem that refuses to go away.
So do I - but I aint beaten it yet! :-(
I'm still getting '2' as being the number of posts in each category when '2' is
actually only the
number of posts in *one* of the categories...weird-huh?
Cheers.
Russ
On Tue, 21 Aug 2001 12:46:37 +0100 Lunny Damian [EMAIL PROTECTED] wrote:
I always love that feeling when you beat a problem that refuses to go away.
Enjoy your lunch!
damian
-Original Message-
From: Russ Michell [mailto:[EMAIL PROTECTED]]
Sent: 21 August 2001 13:23
To: Lunny Damian
Cc: [EMAIL PROTECTED]
Subject: Re: RE: RE: [PHP-DB] Query construction
Damn!!
That'll be it..
I'm running phpMyAdmin 2.1.0 , php.4.0.3pl1 and MySQL 3.22.32
Then again I don't get an error message from your query..???
Perhaps it has to do with the query being embedded, that is
*where* it's embedded:
$totalsql = SELECT category,COUNT(*) FROM $Tpostings GROUP
BY category;
$totalresult = mysql_query($totalsql,$connect) or
error(Sorry! I was unable to process the
required SQL transaction.);
while($totalrow = mysql_fetch_array($totalresult)) {
$total = $totalrow['category'];
}
//Results in:
'some stuff' being echoed to the page..
So I tried:
$totalsql = SELECT category,COUNT(*) AS total FROM
$Tpostings GROUP BY category;
//Resulted in '2' being echoed to the page (2 is the number
of posts in the 'some stuff' category)
but 2 is displayed as being the number of posts in *all* the
categories which isn't the case...
What do you think? (It's my luchtime too and I'm gonna go get some...)
Cheers once agin for trying.
Russ
On Tue, 21 Aug 2001 12:19:00 +0100 Lunny Damian
[EMAIL PROTECTED] wrote:
Russ,
I recreated your table/data (it's lunchtime and I thought
I'd take a look at
this) - my query returned the rows as below:
--
category count (*)
Edit Delete russ category 3
Edit Delete some stuff 1
--
I'm running with phpMyAdmin 2.2.0rc3 on PHP Version
4.0.4pl1 (MySQL is
3.23.36) - perhaps the problem lies with the versions?
damian
-Original Message-
From: Russ Michell [mailto:[EMAIL PROTECTED]]
Sent: 21 August 2001 12:46
To: Lunny Damian
Subject: Re: RE: [PHP-DB] Query construction
Damian:
Thanks for that but it doesn't do what I wanted though. It
actually returns the first item ('some
stuff') from the table, where I wanted the total number of
occurances of *each category* in the
table, thus indicating the number of posts for each category
in the 'posts' table.
!
Many thanks.
Russ
On Tue, 21 Aug 2001 11:23:35 +0100 Lunny Damian
[EMAIL PROTECTED] wrote:
Russ,
This should work for you:
-
select category,count(*)
from posts
group by category;
-
All the best,
damian
-Original Message-
From: Russ Michell [mailto:[EMAIL PROTECTED]]
Sent: 21 August 2001 12:10
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Query construction
Hi there people:
I have a MySQL table consisting of category names among
other things.
I would like to be able to construct a query that tells me
the number of times a unique category
name occurs in the table:
//Table 'posts' from command-line
mysql explain posts;
+--+-+--+-+++
| Field| Type| Null | Key | Default|
Extra |
+--+-+--+-+++
| id | tinyint(3) | | PRI | 0 |
auto_increment |
| category | varchar(25) | | ||
|
| user | varchar(12) | | ||
|
| dateFrom | date| | | -00-00 |
|
| dateTo | date| | | -00-00 |
|
| title| varchar(25) | | ||
|
| body | text| | | NULL |
|
+--+-+--+-+++
7 rows in set (0.00 sec)
//Table 'posts' in phpMyAdmin tab
id category userdateFromdateTo title
body
3 some stuffmaurice 2001-09-23 2001-11-05
Maurcie's post this is Maurice's test
4 russ category russmichell 2001-08-17 2001-08-27 Russ is
theone this is another test
9 russ category russmichell 2001-08-16 2001-08-19 Begon by