Page 1 of 1

Getting Field from MySQL

Posted: Wed Aug 07, 2002 3:04 am
by Takuma
I have made a IP banning system and I stored all the banned IP addresses in the MySQL database (table called "banned_ip"). Here's the code I used:-

Code: Select all

@mysql_connect("localhost","username","password") or die "Failed to establish connection with MySQL server";
@mysql_select_db("database") or die "Failed to select a database.";

$sql = "SELECT ip FROM banned_ip";
$temp = mysql_query($sql);
while($result = mysql_fetch_array($temp))
{
  if($result == $REMOTE_ADDR)
  {
    echo "You're banned from the system";
    mysql_close();
    mysql_free_result($result);
    exit();
  }
}
and I timed this script and also timed the following code:-

Code: Select all

$sql = "SELECT ip FROM banned_ip WHERE ip = '$REMOTE_ADDR'";
$temp = mysql_query($sql);
if($result == $REMOTE_ADDR)
{
  echo "You're banned from the system";
  mysql_close();
  mysql_free_result($result);
  exit();
}
I have manage to work out that the bottom code takes less time to execute. But I did not have much banned IP addresses in the database. Will this change if there is a lot of banned IP addresses in the database?

Posted: Wed Aug 07, 2002 3:12 am
by DesignerSMS
The bottom script is definitely the best in terms of efficiency as mySQL is built to search through large lists very quickly.

The first search method will get much slower the more IP addresses you have in the table whereas the 2nd will not be affected too heavily when more IP addresses are added as they should get indexed (you did create an index on that field didn't you - either PRIMARY KEY or a standard INDEX).

For the most efficient searching, your table should look something like this:

Code: Select all

CREATE TABLE banned_ip (
  ip     char(15)      NOT NULL,
  PRIMARY KEY (ip)
);
Hope this helps.

:: Kondro ::

Just as I thought!

Posted: Wed Aug 07, 2002 10:04 am
by Takuma
Thanks Kondro.