Mysql Query - Need Help!! - PHPBuilder Forums

Mysql Query - Need Help!!

savj14

I am hosting a forum and am trying to restrict users to one user account. I want to do a quick search to see if multiple IP exist.

With phpMyadmin I can run a Query without a problem and it will list all IP's with a column that has a count for each IP Address. So I can see at the top of the list if someone has more than 1 IP address.

Here is my query:

SELECT COUNT( * ) AS `Rows` , `ip_address`, `name`
FROM `ibf_members`
GROUP BY `ip_address` 
ORDER BY `Rows` DESC 
LIMIT 0 , 30

I want to create a Php file on my server so I can quickly do this. I'd like it to List only IP Address with counts of 2 or greater and the Username. I cannot seem to do this for some reason.

Here is what I have so far:

<?
$dbhost = 'host';
$dbuser = 'user';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db('database') or die('Could not select database');

// Performing SQL query
$result = mysql_query("SELECT ip_address FROM ibf_members");
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("IP Address: %s  Name: %s", $row[0], $row[1]);  

}

//$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Closing connection
mysql_close;
?>

It will list all IP Address but that is all.

rulian

$result = mysql_query("
SELECT COUNT( * ) AS Rows , ip_address, name
FROM ibf_members
WHERE Rows > 2
GROUP BY ip_address
ORDER BY Rows DESC
");
while ($row = mysql_fetch_assoc($result)) {
echo "IP Address: ".$row['ip_address']." Name: ".$row['name'];

}

try putting in your original query with a where clause

mikell

If you want to keep someone from registering multiple accounts, you should consider using email verification. Make them give their email address when they register and have your script send them an email with a "confirm registration" link in it. That way the number of possible times someone can register is limited to how many email addresses they own. Its not 100% effective, but you won't end up blocking legit users. By only allowing 1 user per IP, you're taking that risk. For example, my roomate and I share an IP address. If we both wanted to register on your site then one of us would be blocked.

There are two names associated with 22.22.22.22...you are trying to print
out the name, but there can be N names associated with an IP right? I mean,
you're not worried about "jenn" registering twice at 22.22.22.22 are you? I'm assuming "name" is really the "username", which is usually unique...ie, you
don't let "jenn" sign up as "jenn" twice, regarless of her ip address.

Anyhow, this is how I would print it out:

total, ip_address, (names)

2, 22.22.22.22, (jenn, jake)

If you want to use that format, here is the query:

SELECT COUNT(ip_address) as total, ip_address, GROUP_CONCAT(name) as names
FROM ibf_members
GROUP BY ip_address
HAVING total > 1
ORDER BY total;

Run that in phpmyadmin and see if it gives you the output you want. If it does,
then put it in php like this:

$result = mysql_query("
SELECT COUNT(ip_address) as total, ip_address, GROUP_CONCAT(name) as names 
FROM ibf_members 
GROUP BY ip_address 
HAVING total > 1 
ORDER BY total;
");
while($row = mysql_fetch_assoc($result)) {
  echo "Total:".$row['total']." IP Address:".$row['ip_address']." Names:".$row['names'];
}

Sxooter

Well, crap, I hadn't read the post right before mine until now. Everything he said++

rulian wrote:
$result = mysql_query("
SELECT COUNT( * ) AS Rows , ip_address, name
FROM ibf_members
WHERE Rows > 2
GROUP BY ip_address
ORDER BY Rows DESC
");
while ($row = mysql_fetch_assoc($result)) {
echo "IP Address: ".$row['ip_address']." Name: ".$row['name'];

}
try putting in your original query with a where clause

Does that query actually work in MySQL? Keep in mind a where clause fires before a select list, so theoretically the count(*) as Rows doesn't exist when the where clause says Rows > 2. The proper way to do this is the having clause:

SELECT COUNT( ) AS Rows , ip_address, name
FROM ibf_members
GROUP BY ip_address
having count( ) > 2
ORDER BY Rows DESC

However, there's more. selecting a field that's not in the group by, and not being aggregated, nor being a primary key means you could get any value, and it might change from one iteration to the next. So, the name in there could be steve one minute and stan the next. If it's always the same for a given IP, then you're safe, but no other db than mysql will let that query work, just fyi. If it changes, by IP, you might want to consider grouping by it, since you could have two people behind an NAT firewall with different usernames, and they should likely be considered separately.