[RESOLVED] Check User Existence

Hi guys,

Problem's this: I can't work with sql in php...
So, I was wondering if anyone could help me out with a simple(?) user existence script.

DB: subscription
Table: users
Field: username

I need something like a function which returns true or false
This is the code that I already have, but it isn't working

Code:

---

<?
        function querydb($query, $host, $user, $pass, $db)
                {
                        $connid = @mysql_connect($host, $user, $pass) or die("Connection to the server failed");
                        @mysql_select_db($db) or die ("Connection to database failed");
                        $retval = @mysql_query($query) or die ("Query excecution failed");
                        return $retval;
                }
               
        function checkuser($username)
                {
                        $username = strtolower($username);
                        $q = "SELECT * FROM users WHERE username = '$username'";
                        $ret = querydb($q, 'localhost', 'root', '', 'subscription');
                        while ($row = mysql_fetch_assoc($ret)) {
                                $myret = $row["username"];
                        }
                        if ($myret != ''){
                                //echo "Username is unavailable";
                                echo $myret;
                        }
                        else{
                                //echo "Username is available";
                                echo $myret;
                        }
                }
?>

---

Thanks for any help

Wouldnt it be that if $row contained anything other then a nullstring it would exist?

Don't call mysql_connect each time you execute a query, its pointless. Call it once at the start of your script and use the connection handle it returns for future queries.

PHP Code:

---

$conn = mysql_connect($host, $user, $pass); 


---

Use is_resource to validate the results of MySQL API calls:

PHP Code:

---

if (!is_resource($conn)) {
  die('Connection to database failed');
} 


---

Use SELECT DISTINCT in your query as there is only ever going to be one user with the same username (hopefully - you should make the username field an index as well):

PHP Code:

---

$resultset = mysql_query(
  "select distinct password from users where username = '$username'"
);

if (count($resultset)) {
  // user exists; validate their user's password
  $row = mysql_fetch_assoc($resultset);
  if(md5($password) == $row['password'])) {
    // great success
  }
  else {
    // incorrect password
  }
}
else {
  // user does not exist - show an error
} 


---

Note that you should never use die() in a production situation; it is clumsy and unprofessional. Instead, you should use a graceful error handling system in its place.

Quote:

---

Originally Posted by RobDog888

Wouldnt it be that if $row contained anything other then a nullstring it would exist?

---

yeah, you're right
i was trying to turn it around to see if it'd make any difference, apparently not.
anyway, i would have found out sooner or later, but thanks.

@penegate:
it's not working yet:

PHP Code:

---

<?
    $conn = mysql_connect('localhost', 'root', '');
    if (!is_resource($conn)) {
        die('Connection to database failed');
    }  
        
    function checkuser($username)
        {
            mysql_select_db('subscription');
            $username = strtolower($username);
            $q = "SELECT distinct id FROM users WHERE username = '$username'";
            $resultset = mysql_query($q);
            $row = mysql_fetch_assoc($resultset);
            if (is_array($row) == TRUE) {
                echo "Username unavailable";
            }
            else {
              echo "Username available";
            }  
        }
?>

---

$resultset always gives me Resource ID #3 (if it exists or not)
$row gives me 'Array ' when the user exists
i tried changing the if statement to accept 'Array ', but it didn't work

ok forget it;
figured i could use is_array so i did that
working code in previous post

Is there any reason not to do it like so (as I have done in a project I am working on?)

PHP Code:

---

$res = mysql_query("SELECT DISTINCT `mcs_users`.* FROM `mcs_users` WHERE `mcs_users`.`name` = '$name' AND `mcs_users`.`password` = SHA1('$password')");

if (!$res || mysql_num_rows($res) == 0)
{
   // login failed
}
else
{
   // store user info in session
} 


---

Guess that would work too,
but as both work, what does it matter?

Quote:

---

Originally Posted by TheBigB

Guess that would work too,
but as both work, what does it matter?

---

I was just wondering if there was something insecure or iffy about the method I used, since I don't have that much experience with database/web design.

It's OK. However, I can't tell, from looking at the code, whether you've escaped the variables in the SQL or not. That is bad; unescaped data is a SQL injection vulnerability. You should use parameterised queries to avoid this. On PHP 4, look into MDB2; on PHP 5, use PDO, or mysqli if you're only ever going to connect to a MySQL source.