?
Solved

query to compare two IP addresses and return username

Posted on 2006-04-13
11
Medium Priority
?
384 Views
Last Modified: 2013-12-12
Hi,

Im using the following code to bring up a list of IP addresses that have had a fialed login on my system:
$host = "localhost";
$user = "substan";
$pass = "XXXXXXX";
$dbname = "substan_template";
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error());
mysql_select_db($dbname) or die (mysql_errno().": ".mysql_error()); ;

  $query= "SELECT * FROM log_tables";
  $result = mysql_query($query) or die ("Sql error : " . mysql_error());
 // echo mysql_num_rows($result) . " number of records found<br>";
  while($row = mysql_fetch_array($result)) {
 
   echo "<br>" . "<b>log #</b>: " . $row['id'] . "<br><br><b>IP Address: </b>" . $row['ip_column'] . "<br><b>Date/Time: </b>" . $row['logged_datetime'] . "<br>"; //receipt display
   
;
     
  }


 

?>

what i want to do is add an additional field which compares each IP generated to those of people who are members of the website. users Ip addresses are taken into a 'users' table on signup in field called 'signup_ip'.

So basiaclly i need a field that checks to see if each IP matches any other in the userss table and if it does, i would like to print the username next to the current code i have above.

thanks a lot!
0
Comment
Question by:clonmelog
  • 5
  • 4
  • 2
11 Comments
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16446941
Hi clonmelog,

$sql = "SELECT * FROM users WHERE signup_ip = $ipaddress";
$result = mysql_query($sql, $connection);
if (mysql_num_rows($result) > 0)
{
  // Found
}
else
  print "IP address $ipaddress did not match any ip address in the user tabe";

Of course you would need to define $ipaddress.

Joe P
0
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 16447023
Hi clonmelog,

If you wanted it to display a list of matching user accounts for that IP address for each log entry, you could add another query in your while loop:

while($row = mysql_fetch_array($result)) {
 
    echo "<br>" . "<b>log #</b>: " . $row['id'] . "<br><br><b>IP Address: </b>" . $row['ip_column'] . "<br><b>Date/Time: </b>" . $row['logged_datetime'] . "<br><b>Matching User Accounts: </b>"; //receipt display
   
    $sql2 = "SELECT username FROM users WHERE ip_column = '" . $row['ip_column'] . "'";
    $result2 = mysql_query($sql2) or die(mysql_error());
 
    whille($row2 = mysql_fetch_assoc($result2)) {
        $matches[] = $row2['username'];
    }

    if (!empty(count($matches))) {
        echo implode(", ", $matches);
    }
    echo "<br>";    
}

Hope this helps!  ;)
0
 
LVL 2

Author Comment

by:clonmelog
ID: 16447028
Hmmm yes i guessed that would be something like the solution but my problem is that the vlaue of the IP us inside the loop in the vlaue  . $row['ip_column'] . and i want the data to be displayed in such a way that it gives a result for weather each Ip matched after each IP address is printed.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Tomeeboy
ID: 16447061
For better display, modify this part:

    if (!empty(count($matches))) {
        echo implode(", ", $matches);
    }


To this:

    if (!empty(count($matches))) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }
0
 
LVL 2

Author Comment

by:clonmelog
ID: 16447086
ive tried to fix this but i keep getting the following error:

Parse error: syntax error, unexpected T_STRING, expecting T_VARIABLE or '$' in /home/substan/public_html/admin_area/security_logs.php on line 131


this would be the lines:

   if (!empty(count($matches))) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }

ive made a few changes to the code to suit my own db table names etc... so the code reads..

<?
$host = "localhost";
$user = "substan";
$pass = "xxx";
$dbname = "substan_template";
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error());
mysql_select_db($dbname) or die (mysql_errno().": ".mysql_error()); ;

  $query= "SELECT * FROM log_tables";
  $result = mysql_query($query) or die ("Sql error : " . mysql_error());
 // echo mysql_num_rows($result) . " number of records found<br>";
  while($row = mysql_fetch_array($result)) {
 
    echo "<br>" . "<b>log #</b>: " . $row['id'] . "<br><br><b>IP Address: </b>" . $row['ip_column'] . "<br><b>Date/Time: </b>" . $row['logged_datetime'] . "<br><b>Matching User Accounts: </b>"; //receipt display
   
    $sql2 = "SELECT uname FROM users WHERE signup_ip = '" . $row['ip_column'] . "'";
    $result2 = mysql_query($sql2) or die(mysql_error());
 
    while($row2 = mysql_fetch_assoc($result2)) {
        $matches[] = $row2['uname'];
    }

     if (!empty(count($matches))) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }

 

?>  
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16447136
Again:  $sql2 = "SELECT uname FROM users WHERE signup_ip = '" . $row['ip_column'] . "'";
Could be:  $sql2 = "SELECT uname FROM users WHERE signup_ip = '$row[ip_column]'";
But I guess it is personal preference.
0
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 16447153
Sorry, you may not be able to have another function inside of empty().. Try this method instead:

if (isset($matches)) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }
0
 
LVL 2

Author Comment

by:clonmelog
ID: 16447409
tomeeboy, that got rid of my errors but the usernames diplayed are weird:

log #: 1

IP Address: 83.147.170.52
Date/Time: 2006-04-04 18:19:58
Matching User Accounts: fff
log #: 2

IP Address: 83.147.170.52
Date/Time: 2006-04-05 12:58:42
Matching User Accounts: fff, fff
log #: 3

IP Address: 83.147.170.52
Date/Time: 2006-04-05 12:59:32
Matching User Accounts: fff, fff, fff
log #: 4

IP Address: 83.147.170.52
Date/Time: 2006-04-05 13:00:15
Matching User Accounts: fff, fff, fff, fff
log #: 5

IP Address: 193.1.184.254
Date/Time: 2006-04-05 13:01:17
Matching User Accounts: fff, fff, fff, fff
log #: 6

IP Address: 83.167.254.16
Date/Time: 2006-04-05 16:58:26
Matching User Accounts: fff, fff, fff, fff
log #: 7

IP Address: 83.167.254.16
Date/Time: 2006-04-05 16:59:24
Matching User Accounts: fff, fff, fff, fff
log #: 8

IP Address: 83.147.170.52
Date/Time: 2006-04-05 16:59:39
Matching User Accounts: fff, fff, fff, fff, fff
log #: 9

IP Address: 83.147.170.52
Date/Time: 2006-04-05 17:16:08
Matching User Accounts: fff, fff, fff, fff, fff, fff
log #: 10

IP Address: 83.147.170.52
Date/Time: 2006-04-13 12:45:38
Matching User Accounts: fff, fff, fff, fff, fff, fff, fff

There is one user called "fff" in my db but obviously he couldnt have the different signup IP addesses as above.
0
 
LVL 15

Expert Comment

by:Tomeeboy
ID: 16447455
Sorry.. oversight on my part, since it's re-using the same array to hold the usernames.  After this part:

    if (!empty(count($matches))) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }

Add:

    unset($matches);

That should fix it.
0
 
LVL 2

Author Comment

by:clonmelog
ID: 16447678
add this to which line?>
0
 
LVL 15

Accepted Solution

by:
Tomeeboy earned 2000 total points
ID: 16447821
Right after the if statement I just posted (also, just realized I posted the bad code again in my last message, hopefully you didn't change that back.. heh):

    if (isset($matches)) {
        echo implode(", ", $matches);
    } else {
        echo "No matches";
    }
    unset($matches);
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question