php check if a value is in a sql database

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

//define variable to check the number of rows where the sql query is true to find out if the username or password are already in use
$EmailCheck = mysql_query("SELECT `Email` FROM `Customer` WHERE `Email` = '$RegCusEmail'");
$UserCheck = mysql_query("SELECT `Username` FROM `Customer` WHERE `Username` = '$RegCusUser'");

//checks if emails in use
if (mysql_num_rows($EmailCheck) != 0)
{
  echo '<p id="error">The Email address you have entered is already in use.<p>';
}

//checks if username is in use
elseif (mysql_num_rows($UserCheck) != 0)
{
  echo '<p id="error">The username you have entered is already in use.<p>';
}

The above code is supposed to search a database for the email and username of the person registering and if they already exist tell the user to use a different one but when I use it I get this error?

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in (file directory) on line 56 
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in (file directory) on line 62

Like 6 kazjillion other Q&A's on this site, with the exact same error messagE: your queries have failed, you blindly assume they succeed, and now that they have failed, you have no error handling to clean up the mess. — Marc B, Jan 18 '13 at 15:43
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — Waleed Khan, Jan 18 '13 at 15:45
ive checked the query in phpmyadmin and it worked is that a valid way to test if they work or do i need to fix them if so how? — , Jan 18 '13 at 15:47
@user1990909 You do connect to the database right? I'm not seeing where you connected and/or selected the proper db. I know this is probably only a snippet so you might have omitted it here but I was just checking to see if you did indeed connect to the db in your actual code. — War10ck, Jan 18 '13 at 15:49

score 1 · Answer 1 · answered Jan 18 '13 at 15:56

1) Use PDO:

//connection
$host =     "localhost";
$user =     "someuser";
$pass =     "somepass";
$database = "db_name";

$db = new PDO("mysql:host=$host;dbname=$database", $user, $pass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")) or die("Connection error!");

$query = 'SELECT Customer.Email FROM Customer WHERE Customer.Email = :RegCusEmail';
$params = array(':RegCusEmail' => $RegCusEmail);

$result = db_query($query,$params);

if($result == false)
{
  //do stuff..
}


function db_query($q,$p = array())
{
    global $db;

    if($stmt = $db->prepare($query))
    {

        if(count($p) > 0)
        {
            foreach ($p as $key => $value) {
                $stmt->bindParam($key,$value);
            }
        }

        $stmt->execute();
        $resulSet = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if(count($resulSet) > 0)
        {
            return true; // exists
        }
        else
        {
            return false; // does not exist
        }

    }

}

2) validate E-mail address & E-mail server

function isValidEmail ($email)
{
    if ($isemail=filter_var($email, FILTER_VALIDATE_EMAIL))
    {
        if(list($email,$domain) = explode('@',$email))
        {
            if(!getmxrr ($domain,$mxhosts)) 
            { 
                return FALSE; 
            }
            else 
            { 
                return TRUE; 
            }
        }
        else
        {
            return FALSE;
        }
    }
    else
    { 
    return FALSE; 
    } 

}

score 0 · Answer 2 · answered Jan 18 '13 at 15:45

0

It seems you you might have forgotten to connect to database, or details in database may not be correct. When ever you encounter query error, it is good to sort of debug by ending your query die (mysql_error()) as an error handling method like this:

mysql_query("SELECT Email FROM Customer WHERE Email = '$RegCusEmail'") or die (mysql_error());

to get a proper error.

answered Jan 18 '13 at 15:45

Thanks commented it out earlier and forgot about it. – Jan 18 '13 at 15:54
@user1990909 are you checking the query with `mysql_error()` ? – Jan 18 '13 at 15:55

score 0 · Answer 3 · answered Jan 18 '13 at 15:47

0

If you're on 5.2 or greater, its as easy as

 function checkEmail( $EmailCheck ){
    return filter_var( $EmailCheck, FILTER_VALIDATE_EMAIL );
  }

add error handling obviously..

answered Jan 18 '13 at 15:47

Rachel Gallen

27,043
20
72
79

score 0 · Answer 4 · answered Jan 18 '13 at 15:49

0

Do this check against the result instead:

if ( ! is_resource( $EmailCheck ) || mysql_num_rows( $EmailCheck ) != 0 )

But check mysql_error(), you might have an error in your query!

answered Jan 18 '13 at 15:49

Rudolf

1,826
2
21
32

php check if a value is in a sql database

4 Answers4