> 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 | | | 0000-00-00 |
> > > > |
> > > > > > | dateTo | date | | | 0000-00-00 |
> > > > |
> > > > > > | title | varchar(25) | | | |
> > > > |
> > > > > > | body | text | | | NULL |
> > > > |
> > > > > >
> > > >
> > +----------+-------------+------+-----+------------+----------------+
> > > > > > 7 rows in set (0.00 sec)
> > > > > >
> > > > > > //Table 'posts' in phpMyAdmin tab
> > > > > > id category user dateFrom dateTo title
> > > > > > body
> > > > > > 3 some stuff maurice 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
> > > > > > Monday! This entry should dissapear
> > > > > > 10 russ category russmichell 2001-08-16 2001-09-04 testing
> > > > > > This is another test!
> > > > > >
> > > > > > For example I would like to know how many posts there are in
> > > > > > the category 'russ category' (Should
> > > > > > tell me there are 3 and only 1 for 'some stuff' ...
> > > > > >
> > > > > > Can anyone help me toward a nice SQL query that would to
> > > > this for me!?
> > > > > > Cheers all.
> > > > > >
> > > > > > Russ
> > > > > >
> > > > > > #-------------------------------------------------------#
> > > > > >
> > > > > > "Believe nothing - consider everything"
> > > > > >
> > > > > > Russ Michell
> > > > > > Anglia Polytechnic University Webteam
> > > > > >
> > > >
> > > > > > e: [EMAIL PROTECTED]
> > > > > > w: www.apu.ac.uk/webteam
> > > > > > t: +44 (0)1223 363271 x 2331
> > > > > >
> > > > > > www.theruss.com
> > > > > >
> > > > > > #-------------------------------------------------------#
> > > > > >
> > > > > >
> > > > > > --
> > > > > > PHP Database Mailing List (http://www.php.net/)
> > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > > > > For additional commands, e-mail: [EMAIL PROTECTED]
> > > > > > To contact the list administrators, e-mail:
> > > > > > [EMAIL PROTECTED]
> > > > > >
> > > > >
> > > > > The information contained within this email is
> > > > confidential. It may also be
> > > > > legally privileged. It is intended only for the stated
> > > > addressee(s) and
> > > > > access to it by any other person is unauthorised. If
> > you are not an
> > > > > addressee, you must not disclose, copy or circulate or in
> > > > any other way use
> > > > > or rely on the information contained in this email. Such
> > > > unauthorised use
> > > > > may be unlawful. If you have received this mail in error,
> > > > please inform us
> > > > > immediately by telephone on (028) 82833295 or e-mail us at
> > > > > [EMAIL PROTECTED] and delete it and all copies from
> > > > your system.
> > > > >
> > > >
> > > > #-------------------------------------------------------#
> > > >
> > > > "Believe nothing - consider everything"
> > > >
> > > > Russ Michell
> > > > Anglia Polytechnic University Webteam
> > > >
> > > > e: [EMAIL PROTECTED]
> > > > w: www.apu.ac.uk/webteam
> > > > t: +44 (0)1223 363271 x 2331
> > > >
> > > > www.theruss.com
> > > >
> > > > #-------------------------------------------------------#
> > > >
> > >
> > > The information contained within this email is
> > confidential. It may also be
> > > legally privileged. It is intended only for the stated
> > addressee(s) and
> > > access to it by any other person is unauthorised. If you are not an
> > > addressee, you must not disclose, copy or circulate or in
> > any other way use
> > > or rely on the information contained in this email. Such
> > unauthorised use
> > > may be unlawful. If you have received this mail in error,
> > please inform us
> > > immediately by telephone on (028) 82833295 or e-mail us at
> > > [EMAIL PROTECTED] and delete it and all copies from
> > your system.
> > >
> >
> > #-------------------------------------------------------#
> >
> > "Believe nothing - consider everything"
> >
> > Russ Michell
> > Anglia Polytechnic University Webteam
> >
> > e: [EMAIL PROTECTED]
> > w: www.apu.ac.uk/webteam
> > t: +44 (0)1223 363271 x 2331
> >
> > www.theruss.com
> >
> > #-------------------------------------------------------#
> >
>
> The information contained within this email is confidential. It may also be
> legally privileged. It is intended only for the stated addressee(s) and
> access to it by any other person is unauthorised. If you are not an
> addressee, you must not disclose, copy or circulate or in any other way use
> or rely on the information contained in this email. Such unauthorised use
> may be unlawful. If you have received this mail in error, please inform us
> immediately by telephone on (028) 82833295 or e-mail us at
> [EMAIL PROTECTED] and delete it and all copies from your system.
>
#-------------------------------------------------------#
"Believe nothing - consider everything"
Russ Michell
Anglia Polytechnic University Webteam
e: [EMAIL PROTECTED]
w: www.apu.ac.uk/webteam
t: +44 (0)1223 363271 x 2331
www.theruss.com
#-------------------------------------------------------#
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]