selecting MAX, kinda.

[mforan]

<?php
$query = "SELECT allianceno FROM alliances WHERE draft=1";
$result = mysql_query($query) or die("Error: ".mysql_error());
$draf = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {$draf[] = $row[0];}

for ($i = 0; $i < count($draf); $i++) {
$query = "SELECT COUNT(username) FROM users WHERE alliance='".$draf[$i][0]."'";
$result = mysql_query($query) or die("Error: ".mysql_error());
$usersina = array();
$row = mysql_fetch_array($result, MYSQL_NUM);
$usersina = $row[0];
echo "alliance number = $draf[$i] users: $usersina<br>";
}

?>

 

this simply echos out the count of usernames within the selected alliance number. the count comes from the users table.

 

my problem is that i want to grab only the alliance number with the highest amount of users in it so i can use this alliance number elsewhere. any ideas peoplez?

[premiso]

order by count(usernames) desc limit 1

 

should do it if I am not mistaken. I am sure there is a better way to do it, but have my hands full atm.

[Muffins]

You probably don't want count($draf) in your for loop as it has to run the count() every iteration. Just a thought.

[premiso]

Yep, you are right. I had my kid in my hands so yea. Here is a better version, given my sql guesses of your system are correct.

 

<?php
$query = "SELECT count(u.username) as membercnt, u.alliance FROM alliances a, users u WHERE a.draft=1 AND u.alliance = a.allianceno GROUP BY a.allianceno ORDER BY count(u.username) DESC LIMI 10 ";
$result = mysql_query($query) or die("Error: ".mysql_error());

$topTen = array();
$display = "";
while ($row = mysql_fetch_assoc($result)) {
    $topTen[] = $row["membercnt"];
    $display .= "Alliance: " . $row['alliance'] . " has " . $row['membercnt'] . "<br />";
}

echo $display;

?>

[mforan]

that error replys

 

Error: Invalid use of group function

[premiso]

You may want to group it by u.alliance instead of a.allianceno and see if that works.

[mforan]

erm, that went over my head a bit, its the first time ive seen complex sql like this. lol :/