Returning True or False from a Database Query

Risingstar

Hey there,
What I'm doing right now is completely new to me. This is territory I haven't even touched with PHP yet.

What I'm trying to do is check with the MySQL database to see if the username I'm presenting it with exists. If it exists, it says "Username Unvailable" and if it doesn't exist it says "Username Available".

Keep in mind this is NOT in-context. This is just a test script to prepare me for an upcoming project I'm about to take on.

<?php
	    // Connect to the MySQL
    $link = mysql_connect('localhost', 'spowpow1_Sean', '<password>');
    if (!$link)
            {
            die('Not connected : ' . mysql_error());
            }

// select test as the current db
$db_selected = mysql_select_db('spowpow1_test', $link);
if (!$db_selected)
        {
        die ("Database not selected : " . mysql_error());
        }

  function userExists($userName){
    return mysql_query("SELECT FROM people2 WHERE userName = '" . $userName . "'");
  }

if (userExists('LOLOL') == TRUE) {
	echo("Username Available");
}
else {
	echo("Username Unavailable");
}
mysql_close($link);

?>

Right now I'm trying to return a mysql_query, which isn't exactly working out. The Username "LOLOL" does NOT exist in the table.

Any help would be greatly appreciated.

Thanks!

JVassie

Hey there,

In my login system, i use this function to check whether a username is already taken:

function usernameTaken($username){
   global $conn;
   if(!get_magic_quotes_gpc()){
      $username = addslashes($username);
   }
   $q = "select username from users where username = '$username'";
   $result = mysql_query($q,$conn);
   return (mysql_numrows($result) > 0);
}

Where users is the tablename, and $conn contains my database connection details.

You can then just an IF statement to determine whether that username is taken.

Hope that helps
James

laserlight

What I'm trying to do is check with the MySQL database to see if the username I'm presenting it with exists. If it exists, it says "Username Unvailable" and if it doesn't exist it says "Username Available".

If you just want to check to see if the username is a duplicate, then you have the right idea, just that your implementation is not correct. JVassie's implementation is not correct either, though it comes close.

A more correct example would be:

<?php
// Connect to the MySQL
$link = mysql_connect('localhost', 'spowpow1_Sean', '<password>')
    or die('Not connected : ' . mysql_error());

// select test as the current db
$db_selected = mysql_select_db('spowpow1_test', $link)
    or die('Database not selected : ' . mysql_error($link));

$username = 'LOLOL';
$query = sprintf("SELECT COUNT(*) FROM people2 WHERE userName = '%s'",
                 mysql_real_escape_string($username, $link));

$result = mysql_query($query, $link)
    or die(mysql_error($link));

if (mysql_result($result, 0) == 0) {
    echo 'Username Available';
}
else {
    echo 'Username Unavailable';
}

mysql_close($link);

?>

However, if you actually want to insert the new user's records, then you should simply declare the username column UNIQUE. Then attempt to insert, and catch the duplicate error if there is one.

illzz

laserlight, I was just wondering if you can point out what's wrong with JVassie's implementation?

Is it because there is no validation when getting the result? Which can possibly result in a test of

(false > 0)

Or is it something else?

Risingstar

I ended up doing this which was simple and easy:

if (mysql_num_rows(mysql_query("SELECT * FROM users2 WHERE userName = '" . $_POST['userName'] . "'",$link)) == 1) {
  echo("Username Unavailable");
  exit();
  }
  else
    {
    echo("Username Available");
    }

Thanks for all your suggestions, though. I got this idea while looking at laserlight's code.

illzz

If you are going to do it that way, you may want to change

(... == 1)

(... >= 1)
// OR
(... > 0)

Just in case somehow you happen to return more than one matching row.

You may want to do some error catching like laserlight did, in case you come across an error at some point...
It may add a little more code now, but it will be very helpful if anything goes wrong...

Also, you will want to do some validation and/or cleanup on any user input before you go ahead and put it into your query.

Risingstar

illzz wrote:
If you are going to do it that way, you may want to change
(... == 1)
to
(... >= 1)
// OR
(... > 0)
Just in case somehow you happen to return more than one matching row.

You may want to do some error catching like laserlight did, in case you come across an error at some point...
It may add a little more code now, but it will be very helpful if anything goes wrong...

Also, you will want to do some validation and/or cleanup on any user input before you go ahead and put it into your query.

I've got more validation than you can shake a stick at in my actual code :p.

I've just added your suggestion and I'm writing an error-catcher right now.

illzz

I just saw the $_POST variable being put right into the SQL query and thought that I should mention that... 🙂