Quick MySQL insert quesiton

[Xizor]

I have the following code:

$username_sqlsafe = addslashes($data['username']);
$email_sqlsafe = addslashes($data['email']);
$name_sqlsafe = addslashes($data['name']);
$location_sqlsafe = addslashes($data['location']);
$password_md5 = md5($data['password']);
$remoteaddr = $_SERVER['REMOTE_ADDR'];

// do insert
$sql = "INSERT INTO users (username,password,email,registered,
name,location,last_ip)
VALUES ('$username_sqlsafe','$password_md5','$email_sqlsa fe',NOW(),
'$name_sqlsafe','$location_sqlsafe','$remoteaddr') ";

$query = mysql_query($sql,$_db);
// end

The username field has a UNIQUE index.

Problem: when the username exists in the database $query comes out false.
But the PHP documentation says this shouldn't be the case, it should return
true and then mysql_affected_rows() will return 0. And yes, this code works
when the username isn't in the database. I'm just confused, I doubt the
documentation is wrong, but I don't see what I could be doing if it works
fine when the username isn't in the db.

Thanks.

[matty]

Xizor wrote:

I have the following code:

$username_sqlsafe = addslashes($data['username']);
$email_sqlsafe = addslashes($data['email']);
$name_sqlsafe = addslashes($data['name']);
$location_sqlsafe = addslashes($data['location']);
$password_md5 = md5($data['password']);
$remoteaddr = $_SERVER['REMOTE_ADDR'];

// do insert
$sql = "INSERT INTO users (username,password,email,registered,
name,location,last_ip)
VALUES ('$username_sqlsafe','$password_md5','$email_sqlsa fe',NOW(),
'$name_sqlsafe','$location_sqlsafe','$remoteaddr') ";

$query = mysql_query($sql,$_db);
// end

The username field has a UNIQUE index.

Problem: when the username exists in the database $query comes out false.
But the PHP documentation says this shouldn't be the case, it should
return true and then mysql_affected_rows() will return 0. And yes, this
code works when the username isn't in the database. I'm just confused, I
doubt the documentation is wrong, but I don't see what I could be doing if
it works fine when the username isn't in the db.

Thanks.

If the username exists, then the query will *fail*.

Anyway, you should be checking if the user exists first...

[Andy Hassall]

On Sat, 09 Aug 2003 20:28:13 GMT, "Xizor" <no**@nope.com> wrote:

I have the following code:

$username_sqlsafe = addslashes($data['username']);
$email_sqlsafe = addslashes($data['email']);
$name_sqlsafe = addslashes($data['name']);
$location_sqlsafe = addslashes($data['location']);
$password_md5 = md5($data['password']);
$remoteaddr = $_SERVER['REMOTE_ADDR'];

// do insert
$sql = "INSERT INTO users (username,password,email,registered,
name,location,last_ip)
VALUES ('$username_sqlsafe','$password_md5','$email_sqlsa fe',NOW(),
'$name_sqlsafe','$location_sqlsafe','$remoteaddr' )";

$query = mysql_query($sql,$_db);
// end

The username field has a UNIQUE index.

Problem: when the username exists in the database $query comes out false.
This is correct. The query will fail with a key violation. Look at
mysql_error() and mysql_errno(). Find the error code for unique key violation,
then when you do your error checking:

if ($!query) { // an error occurred
if (mysql_errno() == the unique key violation code) {
// handle 'user already exists' condition
}
else
{
// handle UNEXPECTED error
}
}
But the PHP documentation says this shouldn't be the case, it should return
true and then mysql_affected_rows() will return 0.

No, that's for UPDATEs.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Andy Hassall]

On Sat, 09 Aug 2003 21:32:09 +0000, matty <ma*******@askmenoquestions.co.uk>
wrote:

The username field has a UNIQUE index.

If the username exists, then the query will *fail*.

Anyway, you should be checking if the user exists first...

No you shouldn't, you should let the database do that for you, else you have a
race condition.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)