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 <dlunny@slt.n-i.nhs.uk> 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:r.j.michell@apu.ac.uk] > > 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 > > <dlunny@slt.n-i.nhs.uk> 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:r.j.michell@apu.ac.uk] > > > > Sent: 21 August 2001 12:10 > > > > To: php-db@lists.php.net > > > > 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: r.j.michell@apu.ac.uk > > > > 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: php-db-unsubscribe@lists.php.net > > > > For additional commands, e-mail: php-db-help@lists.php.net > > > > To contact the list administrators, e-mail: > > > > php-list-admin@lists.php.net > > > > > > > > > > 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 > > > postmaster@slt.n-i.nhs.uk and delete it and all copies from > > your system. > > > > > > > #-------------------------------------------------------# > > > > "Believe nothing - consider everything" > > > > Russ Michell > > Anglia Polytechnic University Webteam > > > > e: r.j.michell@apu.ac.uk > > 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 > postmaster@slt.n-i.nhs.uk and delete it and all copies from your system. > #-------------------------------------------------------# "Believe nothing - consider everything" Russ Michell Anglia Polytechnic University Webteam e: r.j.michell@apu.ac.uk w: www.apu.ac.uk/webteam t: +44 (0)1223 363271 x 2331 www.theruss.com #-------------------------------------------------------#