Okay I have it now!

while ($row = mysql_fetch_array($result)) {
        $name = $row['name'];
        $dc = $row['datecreated'];
        $nameinurl = urlencode($name);
                        
//Get total posts for each category

$totalsql = "SELECT COUNT(category) AS total FROM $Tpostings WHERE category='$name' 
GROUP BY 
category";
$totalresult = mysql_query($totalsql,$connect) or error("Sorry!");
$check2 = mysql_num_rows($totalresult);
if ($check2 == 0) {
        $total = 0;
        }
else {
        while($totalrow = mysql_fetch_array($totalresult)) {
        $total = $totalrow['total'];
        }//end while
}//end else
}//end while

I used the WHERE clause on $name, which gives me the total postings for each category 
as a *name* 
as opposed simply to a *unique category*

Many thanks for all your efforts, it was those that led me to this solution!
Cheers.

Russ

On Tue, 21 Aug 2001 14:08:30 +0200 "Walter, Marcel" <[EMAIL PROTECTED]> wrote:

> What about this:
> select count(category), category from posts group by category
> 
> > -----Original Message-----
> > From:       Russ Michell [SMTP:[EMAIL PROTECTED]]
> > Sent:       Tuesday, August 21, 2001 14:44
> > To: Lunny Damian
> > Cc: [EMAIL PROTECTED]
> > Subject:    Re: RE: RE: RE: [PHP-DB] Query construction
> > 
> > > 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]
> 
> 
> If you have received this e-mail in error or wish to read our e-mail disclaimer 
>statement and 
> monitoring policy, please refer to http://www.drkw.com/disc/email/ or contact the 
>sender.

#-------------------------------------------------------#
                                
  "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]

Reply via email to