IP Duplicate Search [RESOLVED]

Need help with an engine or coding not on the list? Need help with a game or the website and forums here? Direct all questions here.
Post Reply
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

IP Duplicate Search [RESOLVED]

Post by Epiales »

Hey all...

Been working on some code to find all the duplicate IP's in the system. I have spent an entire day almost non stop and can't get the thing figured out. Here is what I am trying:

Code: Select all

$sql = "SELECT reg_ip, COUNT(*) as count, username FROM users WHERE reg_ip = '$ip' GROUP BY reg_ip HAVING COUNT(*) > 1";
$user_query = mysqli_query($db_lqls, $sql);
$numrows = mysqli_num_rows($user_query);
while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) { 
 echo "<td>".$row['reg_ip']."</td>";
}
 
I've done something similar to this before, but can't get it this time.

Basically I have a search form that I"ve created to do an IP search from the database; Admin enters an IP address. I need it to give me all the duplicates in it if it finds that it's a duplicate IP. I was just going to try and get the IP's first and then try to add usernames to match them.

My search form is search IP, and then I turn that from the POST searchip to the $ip to use in the code above: WHERE reg_ip = $ip.....

When I echo it, it shows the IP that is duplicated, but it doesn't show all the rows of all the other IP's that are duplicated. Like I have 4 users with the same IP in the database, but it only shows one IP instead of displaying all four rows.... Any Ideas?

Ultimately I need it to do a search from the form and then grab the POST form name and then show every username that has the same IP address in the database...
Last edited by Epiales on Tue Jul 07, 2015 12:33 pm, edited 1 time in total.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
KyleMassacre
Posts: 573
Joined: Wed Nov 27, 2013 12:42 pm

Re: IP Duplicate Search

Post by KyleMassacre »

I believe it may be your GROUP BY parameter. If you change the GROUP BY to something unique it should grab and display all rows or just remove the GROUP BY
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: IP Duplicate Search

Post by Epiales »

KyleMassacre wrote:I believe it may be your GROUP BY parameter. If you change the GROUP BY to something unique it should grab and display all rows or just remove the GROUP BY
kk, I tried without the group by and it just shows the one... grrrrr lol... Thx for the reply :)

Also tried using the search post in the group by like this, as you suggested a unique...

Code: Select all

$sql = "SELECT reg_ip, COUNT(*) as count, username FROM users WHERE reg_ip = '$ip' GROUP BY '$ip' HAVING COUNT(*) > 1";
But still doesn't work...just shows the one.

Also, figured I'd post what I have again:

Code: Select all

<?php if (isset($_POST['search'])) {

    
    $word = protect($_POST['search']);
    $word = protect($word);
      
$ip = $word;
        
if(filter_var($ip, FILTER_VALIDATE_IP)) {  
    
$sql = "SELECT reg_ip, COUNT(reg_ip) as count, username FROM users WHERE reg_ip = '$ip' GROUP BY 'reg_ip' HAVING COUNT(*) > 1";

$user_query = mysqli_query($db_lqls, $sql);
$numrows = mysqli_num_rows($user_query);
while ($row = mysqli_fetch_array($user_query, MYSQLI_ASSOC)) {

     
    echo "<td>".$row['reg_ip']."</td>"; 
    echo "<td>".$row['username']."</td>"; ?>

?>
I am not sure why it's not working, as it's in a loop and should loop through the database and find all the users with same IP and list them...Instead it just displays the first user and username that has the searched IP entry. :( :( :(
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
KyleMassacre
Posts: 573
Joined: Wed Nov 27, 2013 12:42 pm

Re: IP Duplicate Search

Post by KyleMassacre »

I'm not too sure, but what is HAVING() lol? I have never used it so have you tried without it?
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: IP Duplicate Search

Post by Epiales »

KyleMassacre wrote:I'm not too sure, but what is HAVING() lol? I have never used it so have you tried without it?
Yeah, it's legitimate use in the context that I have it. And yah, I've tried with and without and all different ways. I personally don't see that my code is wrong, so I can't find out why it's not working properly.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
KyleMassacre
Posts: 573
Joined: Wed Nov 27, 2013 12:42 pm

Re: IP Duplicate Search

Post by KyleMassacre »

Probably not ideal, but try using LIKE %$ip%
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: IP Duplicate Search

Post by Epiales »

KyleMassacre wrote:Probably not ideal, but try using LIKE %$ip%
I tried this:

Code: Select all

$sql = "SELECT reg_ip, username FROM users WHERE reg_ip LIKE %$ip%> 1";
I get this:

Code: Select all

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 44

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 45

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 64

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 65
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
KyleMassacre
Posts: 573
Joined: Wed Nov 27, 2013 12:42 pm

Re: IP Duplicate Search

Post by KyleMassacre »

Remove the > 1 at the end
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: IP Duplicate Search

Post by Epiales »

KyleMassacre wrote:Remove the > 1 at the end
yah.. lol... It just does the same thing. It shows the ONE user and user IP that's first in the database but no others.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Re: IP Duplicate Search

Post by Epiales »

Actually, my bad. I had put the original code back and then removed the > 1 when you asked.... so I did it u're way it shows the same as abaove

Code: Select all

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 44

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 45

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 64

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\_atesting\admin\do_ip_search.php on line 65
What I put after removing the > 1 :

Code: Select all

$sql = "SELECT reg_ip, username FROM users WHERE reg_ip LIKE %$ip%>";
Nothing fancy, but a work in progress!

http://gameplaytoday.net
Post Reply

Return to “Advanced Help and Support”