problem with grouping entries

[lJesterl]

I have been using PHP for a few months and im slowly learning things. My newest task is trying to use the GROUP BY command but I keep failing even by using tutorials lsited on the web. Basicly this is what im trying to accomplish.

I am trying to select ipaddress from users and group duplicate ipaddress's.

I run a gaming ladder and people making extra accounts is a big problem. Right now the ips list like this.

Ipaddress
11.1.1.1     
11.1.1.1
69.56.23.1
445.21.21.6
445.21.21.6

etc, etc.

I want something like this.

[table]
[tr]
[td]Ipaddress[/td]
[td]Entries[/td]
[/tr]
[tr]
[td]11.1.1.1[/td]
[td]2[/td]
[/tr]
[tr]
[td]69.56.23.1[/td]
[td]1[/td]
[/tr]
[tr]
[td]445.21.21.6[/td]
[td]2[/td]
[/tr]
[/table]


I am not sure how to accomplish this. Any direction would be helpfull.

[HuggieBear]

Try this SQL statement:

[code]
SELECT ipaddress, count(ipaddress) FROM users GROUP BY ipaddress
[/code]

Or spice it up a bit with this:

[code]
SELECT ipaddress AS "IP Address", count(ipaddress) AS "Entries" FROM users GROUP BY ipaddress ORDER BY "IP Address"
[/code]

The key thing to remember when using 'GROUP BY' is that you must be using a 'group' function.  Something like count(), max(), min(), avg() etc.  Not just normal columns.

Regards
Rich

[lJesterl]

Hello and thanks for your reply. I guess where I use different torurials to list data from a php table I am unsure how to do it from scratch. I always use the same code from the tutorial and it is hard to combine it with what you provided. Here is the code I have tried on my own and When I view page it comes up with Resource ID #3

<?php
$host="localhost"; // Host name
$username="asdf"; // Mysql username
$password="asdf"; // Mysql password
$db_name="asdf"; // Database name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT ipaddress, count(ipaddress) FROM users GROUP BY ipaddress";
$result=mysql_query($sql);

echo"$result<br>";
?>

[corbin]

Try

print_r($result);

instead of the echo thing...

[lJesterl]

[color=black]I'm almost there. I ahve my page where I need it but now the only problem is that I need the list its returned position inside my theme. Right now its listing at the very top and then starting the theme.

Here is my code.[/color]

[color=red]<?
if(!IsSet($indexloaded)){
header("Location: ./index.php");
}

$checkaccess=mysql_query("SELECT * FROM staff1 WHERE id='$admn[id]'");
$ca=mysql_fetch_array($checkaccess);
if((!$ca[gmode]) AND (!$ca[mastera])){
if(!$ca[eteam]){
include("$dir[func]/error.php");
display_error("You are not allowed to View Player IP's.<br>");
}
}

/*
//CHECK ADMIN ACCESS LEVEL
if($admn[access] < 15){
include("$dir[func]/error.php");
display_error("You are not allowed to perform this function.<br>");
}
*/

$query = "SELECT ip, count(ip) AS entries FROM users GROUP BY ip ORDER BY ip ASC";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);

for ($i=0; $i <$num_results; $i++)
{

$row = mysql_fetch_array($result);
echo"<tr>";
echo"<td>";
echo ($row["ip"]);
echo"</td>";
echo"<td>";
echo ($row["entries"]); 
echo"</td>";
echo"</tr>";
}




$tablehead=table_head("show","600","","left");
$tablefoot=table_foot("show");

$out[body] = $out[body]."
<center>
<br>
<br>
$tablehead
<table width='100%'>
<tr>
<td><b>IP Address</b></td>
<td><b>Number of Entries</b></td>
</tr>
$iplist
</table>
<br>
<center>
<a href=adminmain.php><u>Back To Admin Main</u><a/>
$tablefoot";


include("$dir[curtheme]");

?>[/color]


[color=black]Somehow I need this Part to output to $iplist[/color]

[color=red]echo"<tr>";
echo"<td>";
echo ($row["ip"]);
echo"</td>";
echo"<td>";
echo ($row["entries"]); 
echo"</td>";
echo"</tr>";[/color]

[jefkin]

Hi lJesterl

change the echo code bit from:

[code]
for ($i=0; $i <$num_results; $i++)
{

$row = mysql_fetch_array($result);
echo"<tr>";
echo"<td>";
echo ($row["ip"]);
echo"</td>";
echo"<td>";
echo ($row["entries"]);
echo"</td>";
echo"</tr>";
}
[/code]

to

[code]
for ($i=0; $i <$num_results; $i++)
{
  $row    = mysql_fetch_array($result);
  $iplist .= "<tr><td>{$row['ip']}</td><td>{$row["entries"]}</td></tr>";
}
[/code]

Jeff