Counting rows with specific ids

[dkin]

I have many rows in my database, all with ids which are not unique. I would like to count these ids and display the numbers in a loop. Like so.

Row 1 (67)
Row 2 (34)
Row 3 (234)

All ids that match the id of the row should be counted and displayed (in here).

I have tried this.

$count = mysql_result(mysql_query("SELECT COUNT(*) FROM table WHERE $id=id"), 0);

But all that does is count the first row and loops the same number.

[bsterz]

Sorry - I don't fully understand the question..

Can you show me your query without the variable, just hard-code the variable and show the query and what you expect to have returned.

Thanks,

Bill

[ergophobe]

You need to do the following

- do a SELECT DISTINCT to find out which ids are actually in the DB
- walk through that result set in a WHILE loop
- use the ids from the first result set to build the query so that you hit all the ids you need.

Tom

[dkin]

This is the code for my page.

$results = mysql_query("SELECT * FROM nuke_guilds order by guild_name",$link);
$myrow = mysql_fetch_array($results);
$count = mysql_result(mysql_query("SELECT COUNT(*) FROM nuke_guilds"), 0);
$guildid = $myrow[id];

if ($count == "0") {
OpenTable();
print "<div align=\"center\"><font class=\"item\">Guilds</font><br><hr color=\"000000\">";
print "<br><br><a href=\"modules.php?name=$module_name&file=add_guild\">Click Here</a> to add your guild.<br><br>";
print "There are no Guilds listed in the Database.";
CloseTable();
exit;
}
else {
OpenTable();
print "<div align=\"center\"><font class=\"item\">Guilds</font><br><hr color=\"000000\">";
print "<br><br><a href=\"modules.php?name=$module_name&file=add_guild\">Click Here</a> to add your guild.<br><br>";
print "<table>";
do {
print "<tr><td align=\"center\">";
print "<b><a href=\"modules.php?name=$module_name&file=guild_detail&id=".$myrow[id]."\">".$myrow["guild_name"]."</a></b>";
print "<br></td></tr>";

}
while ($myrow = mysql_fetch_array($results));
print "</table>";
CloseTable();
}

But I would like count all the members in each guild and display the number of rows that match beside the guild name.

[coopster]

Rather than showing the code, you might be better off describing your data and the formatted output you desire, as you started off in the first message. It sounds like you have a table of data describing members and which guild they belong to. All you want is to print out the guild name with the total number of members per guild?

SELECT guild_name, COUNT(*) FROM nuke_guilds GROUP BY guild_name;

[dkin]

yes I want to display the amount of members beside the guild name. But I am going to have many, many guilds. I want to display the amount of members in the guild the number is beside. So the number beside the guild will almost always be different.

[ergophobe]

To make it a bit easier to use coopster's code with mysql_fetch_assoc() use an alias

SELECT guild_name, COUNT(*) as num_members FROM nuke_guilds GROUP BY guild_name;

then after you get your result set, you just go like this:

$list .= "<li>" . $row['guild_name'] . "(" . $row['num_members'] . " members)</li>";

Tom

[dkin]

The member names and guilds are in 2 seperate databases. I messed around with the first code for awhile but I could not get it to work.

[ergophobe]

Two separate databases or two separate tables?

I think we've been over this ground before....

As coopster said, you need to start by describing your table structure. Please, no extraneous details, but to help we need to know at least:

tablename
- primary key
- foreign keys
- essential info

For each table involved in the query.

Tom

[dkin]

Im not exactly sure what you mean by primary and foreign keys are but I will do my best.

There are two tables in the same database, nuke_guilds and nuke_members.

Depending on the guild the member is entered into it will have a different id
eg all members in Guild Webmasters world would have the unique id assigned to the guild when it was created.

So I would like to match all the member ids with the corresponding guild id then display the appropriate number beside the appropriate guild.

[ergophobe]

This should help:

The Computing Dictionary definition [computing-dictionary.thefreedictionary.com]

A whole bunch of other definitions [google.com], some a bit lengthier than the one in the computing dictionary.

A Basic lesson on primary and foreign keys [microsoft-accesssolutions.co.uk] oriented toward MS Access, but applicable to any relational DB.

Chapter 2. Primary and Foreign Keys [searchdatabase.techtarget.com] from techtarget.com