Getting Field from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Getting Field from MySQL

Post 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?
User avatar
DesignerSMS
Forum Newbie
Posts: 17
Joined: Tue Aug 06, 2002 12:16 am
Location: Gold Coast, Australia

Post 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 ::
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Just as I thought!

Post by Takuma »

Thanks Kondro.
Post Reply