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
