Jump to content


Photo

problem with grouping entries


  • Please log in to reply
5 replies to this topic

#1 lJesterl

lJesterl
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts

Posted 10 September 2006 - 11:30 PM

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.


















IpaddressEntries
11.1.1.12
69.56.23.11
445.21.21.62



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

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 10 September 2006 - 11:56 PM

Try this SQL statement:

SELECT ipaddress, count(ipaddress) FROM users GROUP BY ipaddress

Or spice it up a bit with this:

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

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 lJesterl

lJesterl
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts

Posted 11 September 2006 - 12:25 AM

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>";
?>

#4 corbin

corbin
  • Staff Alumni
  • Advanced Member
  • 8,129 posts

Posted 11 September 2006 - 01:03 AM

Try

print_r($result);

instead of the echo thing...
Why doesn't anyone ever say hi, hey, or whad up world?

#5 lJesterl

lJesterl
  • Members
  • PipPipPip
  • Advanced Member
  • 52 posts

Posted 11 September 2006 - 02:09 AM

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.


<?
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]");

?>



Somehow I need this Part to output to $iplist

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




#6 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 11 September 2006 - 03:50 AM

Hi lJesterl

change the echo code bit from:

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>";
} 

to

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

Jeff




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users