Dave,
[if you post your replies to the list, others may turn around a response
faster than I can! Also other newbies may benefit from the discussion!]
First observation: the second set of array references, to extract data
from the second query resultset, do not enclose the assoc array
indexes/keys in quotes (see the first set). [may not be strictly
necessary/my habit!?]
Second observation: there's no need to extract data that you do not
actually use/need (eg $id2 does not APPEAR to be necessary/used).
I'm not clear about the use of the second table "$tbn3".
What is the relationship between it and catid (FROM
$tbn2)/$showcategoryId?
Are you only using "$tbn3" to translate the Category Id code into a text
name (per $catrows[name])?
At the moment it appears as if every column and every row from $tbn3 is
being retrieved. For what purpose?
If my guess is correct, then where is the linkage between the two
SELECTs, and why not do the whole job in one joined SELECT?
Please advise,
=dn
----- Original Message -----
From: "Dave Carrera" <[EMAIL PROTECTED]>
To: "'DL Neil'" <[EMAIL PROTECTED]>
Sent: 16 March 2002 14:10
Subject: RE: [PHP-DB] a Count() ?
> Hi Again,
> I think I made a boo boo....
> As you may be able to make out from the code below I have implemented
> you example and the echo retreves the correct values :-)
>
> But when I add the $showitemcount var where I have put it it only
shows
> the number 2 for all categorys.
>
> You may notice I have called the category list from a separate table
and
> I think its here that I have made a boo boo.
>
> Any ideas
>
> Thank you for you patience :-)
>
> $cntcats ="SELECT count( catid ) as total, catid FROM $tbn2 GROUP BY
> catid";
> $cntresult = mysql_query($cntcats, $con) or die("error:
> ".mysql_error());
> while ( $cntcat = mysql_fetch_array( $cntresult ) )
> {
> $showcategoryId = $cntcat['catid'];
> $showitemcount = $cntcat['total'];
> echo "Retrieved: $showcategoryId~$showitemcount~";
> }
>
> $catmenu_sql = "select * from $tbn3";
> $catresult = mysql_query($catmenu_sql, $con) or die("error:
> ".mysql_error());
> while($catrows = mysql_fetch_array($catresult) ){
> $name2 = $catrows[name];
> $id2 = $catrows[id];
> $cat_menu .="<a
>
href=\"viewcat.php?sc=$id2\"> $name2 </a>$showitemcount<br/>";
> }
>
> Dave Carrera
> Php Developer
> http://davecarrera.freelancers.net
> http://www.davecarrera.com
>
>
> -----Original Message-----
> From: DL Neil [mailto:[EMAIL PROTECTED]]
> Sent: 16 March 2002 13:15
> To: Dave Carrera
> Cc: [EMAIL PROTECTED]
> Subject: Re: [PHP-DB] a Count() ?
>
> Hi Dave,
>
> > Below is an attempt at resolving my issue.
> >
> > Somewhere I am getting confused.....
>
> >From what you are showing it is not easy to see what is confusing
you!
> Is it the logic, or the SQL or PHP code?
>
> > Is there something here shouting out at you that's obviously wrong?
>
> The database needs to be selected and the loops need closing braces,
but
> I assume that this is an incomplete code fragment!?
>
> The mysql_fetch_array looks incorrect (I don't use it) - it has too
many
> arguments and none should be a numeric constant [please refer to
> manual], and feeds into $showitemcount in a way that seems to be
> 'double-handling' (if indeed it is correct at all). My recommendation
> would be to use mysql_fetch_assoc($cntresult):
>
> while ( $cntcat = mysql_fetch_array( $cntresult ) )
> {
> $showcategoryId = $cntcat['catid'];
> $showitemcount = $cntcat['catcnt'];
> echo "Retrieved: $showcategoryId~$showitemcount~";
> }
>
> Note how the $cntcat[] associative array is indexed by the column
> name/title, as retrieved from MySQL!? That sort of
self-documenting/flow
> of documentation is why I use this alternative of the fetch-es. BTW
I'm
> glad you added the AS alias code to your SQL - I forgot all about
adding
> that - until I'd hit send that is!?
>
> Finally, if your confusion is knowing that there is an error but not
> being able to work out if it is in PHP or MySQL: my suggestion would
be
> to use debug ECHO statements. For example in the code above.
>
> Also if you ECHO $cntcats immediately after setting the query, then if
> the resultset is in any way weird or 'not as expected', you can
> copy-paste the debug ECHO o/p straight into the native MySQL command
> line (or an admin pkg) and see what MySQL makes of it! (including the
> evaluation of the PHP variable/value $tbn2)!
>
> > Any help is appreciated.
>
> Hope it is helpful,
> =dn
>
>
>
> > Thanks in advance
> >
> >
> > $cntcats ="SELECT catid, count(*) as catcnt FROM $tbn2 GROUP BY
> catid";
> > $cntresult = mysql_query($cntcats, $con) or die("error:
> > ".mysql_error());
> > $cntcat = mysql_fetch_array($cntresult,1,catcnt) ;{
> > for($n =0; $n < count($cntcat); $n++){
> > $showitemcount = each($cntcat);
> >
> > Dave Carrera
> > Php Developer
> > http://davecarrera.freelancers.net
> > http://www.davecarrera.com
> >
> >
> > -----Original Message-----
> > From: DL Neil [mailto:[EMAIL PROTECTED]]
> > Sent: 16 March 2002 11:50
> > To: Dave Carrera; [EMAIL PROTECTED]
> > Subject: Re: [PHP-DB] a Count() ?
> >
> > Hi Dave,
> >
> > > I am trying to count how many product names in my db have the same
> > > category id and then show it ie:
> > >
> > > Catid 1 Product 1
> > > Catid 1 Product 2
> > > Catid 2 Product 3
> > > Catid 3 Product 4
> > > Catid 3 Product 5
> > >
> > > Result would be
> > >
> > > Catid1 has 2 products
> > > Catid2 has 1 products
> > > Catid3 has 2 products
> > >
> > > I think it has something to do with the GROUP command but the
mysql
> > doc
> > > dose not make it clear how to achive this task.
> > >
> > > Code examples, pointers to web resources or any info thankfully
> > > received.
> >
> >
> > Let's take it a step at a time. First of all assemble the SELECT to
> > produce your first list:
> >
> > SELECT * FROM tblNm;
> >
> > then pull in the GROUP BY clause to collect the row-results together
> in
> > some like-minded fashion. In this case you want to collect all or
the
> > rows pertaining to one category (ID) together. (you will need to be
> more
> > specific about what in the manual is making you uncertain):
> >
> > SELECT * FROM tblNm GROUP BY Catid1;
> >
> > Oops! All of a sudden we only get one line for each CatId (and the
> rest
> > of the columns produce fairly unpredictable data taken from only one
> of
> > the rows with that CatId). Get rid of the * (all columns) and
replace
> it
> > with the CatId colNm.
> >
> > Now follow your instincts and check out COUNT() in the manual, and
try
> > something like:
> >
> > SELECT Catid1, count(*) FROM tblNm GROUP BY Catid1;
> >
> > As I said 'follow your instincts' and take it one step at a time:
Code
> > the simplest query first, then try making it more complicated by
> > adding/amending one clause at a time, crafting the result until it
> suits
> > your purposes...
> >
> > Let us know how you get on!
> > =dn
> >
> >
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
