Checking if value exists in database. Getting error.

[cloudll]

Hello,

 

I have googled quite a few examples on how to check if a value already exists in a database, and most of them suggest the below, or very similar methods.

 

I'm using the following code to attempt to check if the users ip address matches an ip address stored in my database.

$user_ip = $_SERVER['REMOTE_ADDR'];

$sql=mysql_query("SELECT FROM banned (id, ip) WHERE ip=$user_ip");
  if(mysql_num_rows($sql)>=1)
    {
  echo "BANNED";
    }
  else
    {
  echo "OKAY";
    }

However I'm getting this error.

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\oakFarm\test.php on line 11

 

Line 11 is: if(mysql_num_rows($sql)>=1)

 

Could anyone explain to me what I'm doing wrong. Thank you.

[CroNiX]

Your query failed, but you have no code in place to check for it. Never a good idea to just assume everything is fine. You need to check for errors.

$sql=mysql_query("SELECT FROM banned (id, ip) WHERE ip=$user_ip"); //will return FALSE if failed query
if ( ! $sql)
{
  //query failed, handle it, display mysql error, etc.
}

But I bet the problem is you don't have quotes around $user_ip in your query.

 

As a side note, you'd be a lot better off using PDO or MySQLi database extensions. The original MySQL extension is deprecated and will be removed from PHP very soon, so you'd have to rewrite all of your DB code. Best to use current extensions.

[Psycho]

The issue is that the query is failing. You need to check for errors. The query isn't selecting anything. It would look something like this

SELECT COUNT(*) FROM banned WHERE ip=$user_ip"

But, if your intent is to prevent a user from inserting a duplicate value. If that is the case, that is the wrong way to approach the problem. The reason is "race conditions". The process of checking if a value exists and, if not, inserting the new record takes some amount of time between those two processes. Yes, it is typically a very small amount of time (milliseconds) but it can and does happen.

 

You instead need to put a unique constraint on the database. Then you just need to perform the INSERT query. If the new value would create a duplicate the query will fail.

Edited February 5, 2015 by Psycho

[Barand]

your sql syntax is screwed up. The select query should look like

SELECT id, ip FROM banned WHERE ip='$user_ip'