Page 1 of 1
IP Duplicate Search [RESOLVED]
Posted: Mon Jul 06, 2015 6:28 am
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...
Re: IP Duplicate Search
Posted: Mon Jul 06, 2015 12:20 pm
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
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 5:38 am
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.
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:05 am
by KyleMassacre
I'm not too sure, but what is HAVING() lol? I have never used it so have you tried without it?
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:10 am
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.
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:17 am
by KyleMassacre
Probably not ideal, but try using LIKE %$ip%
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:32 am
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
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:34 am
by KyleMassacre
Remove the > 1 at the end
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:37 am
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.
Re: IP Duplicate Search
Posted: Tue Jul 07, 2015 11:40 am
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%>";