RE: [PHP-DB] Query construction
| From: | Russ Michell | Date: | Thu, 01 Jan 1970 00:00:00 +0000 |
| Subject: | RE: [PHP-DB] Query construction | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-11598@lists.php.net to get a copy of this message | ||
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" <Marcel.Walter@drkw.com> wrote:
> What about this:
> select count(category), category from posts group by category
>
> > -----Original Message-----
> > From: Russ Michell [SMTP:r.j.michell@apu.ac.uk]
> > Sent: Tuesday, August 21, 2001 14:44
> > To: Lunny Damian
> > Cc: php-db@lists.php.net
> > 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 <dlunny@slt.n-i.nhs.uk>
> > 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:r.j.michell@apu.ac.uk]
> > > > Sent: 21 August 2001 13:23
> > > > To: Lunny Damian
> > > > Cc: php-db@lists.php.net
> > > > 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
> > > > <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
> > > >
> > > > #-------------------------------------------------------#
> > > >
> > >
> > > 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
> >
> > #-------------------------------------------------------#
> >
> >
> > --
> > 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
>
>
> 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: r.j.michell@apu.ac.uk
w: www.apu.ac.uk/webteam
t: +44 (0)1223 363271 x 2331
www.theruss.com
#-------------------------------------------------------#