Jump to content

problem with grouping entries


lJesterl

Recommended Posts

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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>";
?>
Link to comment
Share on other sites

[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]

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.