PHP User already exist check

Question

$sql = 'SELECT * FROM tbluser, tblfile WHERE tbluser.Username = "'.$user.'"'; 
if ($result = mysql_query($sql)) {
if(mysql_num_rows($result)) {
    echo'User already exists';
    exit;
}
else
{
mysql_query('INSERT INTO tbluser
(`Username`, Password, BossID) VALUES  ( "'.$user.'","'.$passwd.'","'.$boss.'")')or die(mysql_error());


$sqlq = 'SELECT * FROM tbluser, tblfile WHERE tbluser.Username = "'.$user.'"'; 
$res = mysql_query($sqlq);
while($row = mysql_fetch_array($res)){
mysql_query('INSERT INTO tbluser_file
(`FileID`, UserID) VALUES  ( "'.$row['FileID'].'","'.$row['UserID'].'")')or die(mysql_error());
}
echo "Admin Added!";

}
}

I would like to check if a user already exists in my database. If he exists I want to exit, if he doesn't exist, i would like to execute above code (under the ELSE statement)

thanks in advance

At a quick glance, your code should do what you want it to. What problems are you encountering, if any? — Saladin Akara
– Saladin Akara, Commented Mar 23, 2012 at 9:52
I would like to make it work, but it doesn't. Any suggestions? — user1287949
– user1287949, Commented Mar 23, 2012 at 9:52

VolkerK · Accepted Answer · 2012-03-23 10:10:14Z

If you create a unique index for the column Username an insert with the same value for Username as an other record already in the table will cause a specfic error code. Your script can simply try to insert the new record and handle the specific error code, something like

define('ER_DUP_KEY', 1022);

// pre-condition: $user, $passwd, $boss have been prepared for usage within an sql statement
$query = sprintf("
    INSERT INTO
        tbluser
        (`Username`, `Password`, `BossID`)
    VALUES 
        ('%s', '%s', '%s')
", $user, $passwd, $boss);

if ( !mysql_query($query) ) {
    switch(mysql_errno()) {
        case ER_DUP_KEY:
          // handle duplicate user name
          break;
        default:
          // handle other errors
    }
}

If you would use two queries (SELECT+INSERT) you had to handle race conditions, e.g. by locking the table.

edit: I missed the implicit join FROM tbluser, tblfile.
In that case you might be interested in INSERT ... ON DUPLICATE KEY UPDATE, esp. the

If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

part. I.e.:

create a unique index for Username
do the INSERT INTO tbluser query with an ON DUPLICATE KEY UPDATE clause using LAST_INSERT_ID(expr)
fetch the insert id, see http://docs.php.net/function.mysql-insert-id
do the second insert using the insert_id from the previsous query

user319198 · Accepted Answer · 2012-03-23 09:57:07Z

0

Try below:

Make sure query is returning rows or it's ok.

change if(mysql_num_rows($result)) to if(mysql_num_rows($result) > 0)

$sql = 'SELECT * FROM tbluser, tblfile WHERE tbluser.Username = "'.$user.'"'; 
if ($result = mysql_query($sql)) {
if(mysql_num_rows($result) > 0) {
    echo'User already exists';
    exit;
}

answered Mar 23, 2012 at 9:57

user319198

2 Comments

user319198 Over a year ago

any result from query. Did you tried to run query in console ?

user1287949 Over a year ago

yea the query worked fine. Solved the problem myself.. I just deleted tblfile from my sql and it worked just fine. $sql = 'SELECT * FROM tbluser WHERE Username = "'.$user.'"';

Raptor · Accepted Answer · 2013-04-05 11:03:45Z

0

$sql = 'SELECT COUNT(*) as COUNTUSER FROM tbluser, tblfile WHERE tbluser.Username = "'.$user.'"';
while ($myRow= mysql_fetch_array($sql)) { 
    $counterUser=$myRow[COUNTUSER]; 
}
if ($counterUser>0)
{
    echo 'User already exists';
    exit;
}

edited Apr 5, 2013 at 11:03

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

answered Mar 23, 2012 at 10:00

user1283565

313 bronze badges

1 Comment

Raptor Over a year ago

should be $myRow['COUNTUSER']

Collectives™ on Stack Overflow

PHP User already exist check

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related