Hi Frank,
Thank you for the help and example you included in your reply.
With the help gained by all that answered I concocted this work around which
works perfectly.
I have included the code to help anyone else who needs this kind of result.
It has two table references
the first gets admin data and the seconds uses the admin data to reference
the 2nd table for all instances.
------ My Code Snippet -----
$whichadminsql = "select id, user from $tbn";
$whichadminres = mysql_query($whichadminsql);
while($adname = mysql_fetch_array($whichadminres)){
$adid = $adname[id];
$adnm = $adname[user];
$query = "select count(admin_id) as Admin1 from $tbn3 where admin_id=$adid
";
$result = mysql_query($query);
$object = mysql_fetch_object($result);
$Admin[$i] = $object -> Admin1;
$adminstatsshow .= "$adnm has $Admin[$i] posts.<br>";
}
// Variables used are specific to my tables and needs and will need changing
to your suit your needs.
------ End My Code Snippet -----
Once again Thank you
"Frank Flynn" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hi Dave,
>
> The easiest, most direct, ANSI - SQL way to do what you ask is:
>
> SELECT count(*) as num_posts, Admin_id
> FROM <<your_table_Name>>
> WHERE <<optional but you could narrow the results here>>
> GROUP BY Admin_id
> ORDER BY Admin_id
>
> This will give you:
>
> num_posts Admin_id
> --------- --------
> 3 1
> 4 2
> 5 3
>
> And naturally you can do whatever you like with these values in your PHP
> code. The only potential "got ya" is if you had an Admin_id 4 who had no
> records you would not get 0, 4 you would not get a row for 4 at all.
There
> is a way around. Ask me if you need it.
>
> Also GROUP BY has it's own kind of WHERE clause called HAVING. The WHERTE
> clause operates on the raw data (like any other query) but HAVING works on
> the aggregate functions (like count, min, max, ave) so in this example we
> could add this line right after the GROUP BY:
> HAVING count(*) > 4
> This would make the query only return the 5, 3 row.
>
> Good Luck,
> Frank
>
> On 5/1/02 12:24 PM, "[EMAIL PROTECTED]"
> <[EMAIL PROTECTED]> wrote:
>
> > From: "Dave Carrera" <[EMAIL PROTECTED]>
> > Date: Wed, 1 May 2002 13:02:51 +0100
> > To: <[EMAIL PROTECTED]>
> > Subject: Count and group ?
> >
> > Hi All
> >
> >
> >
> > I have added a row to my table which inputs which admin user has amended
> > a record in a table on my db.
> >
> >
> >
> > I have this working but what I would like to do is count how many
> > instances of the admin id I have stored.
> >
> >
> >
> > So if my list looks like this..
> >
> >
> >
> > Admin_id
> >
> > 1
> >
> > 2
> >
> > 2
> >
> > 2
> >
> > 3
> >
> > 3
> >
> > 3
> >
> > 3
> >
> > 3
> >
> > 2
> >
> > 1
> >
> > 1
> >
> > Then the result I would like to display is this..
> >
> >
> >
> > Admin (1) = 3 posts
> >
> > Admin (2) = 4 posts
> >
> > Admin (3) = 5 posts
> >
> >
> >
> > Basically displaying the total post each admin has made
> >
> >
> >
> > Any help or guidance is very appreciated and thank you in advance
> >
> >
> >
> > Dave Carrera
> >
> > Php Developer
> >
> > http://davecarrera.freelancers.net
> >
> > http://www.davecarrera.com
> >
> >
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
