Quick MySQL insert quesiton

[Xizor]

I have the following code:

$username_sqlsa fe = addslashes($dat a['username']);
$email_sqlsafe = addslashes($dat a['email']);
$name_sqlsafe = addslashes($dat a['name']);
$location_sqlsa fe = addslashes($dat a['location']);
$password_md5 = md5($data['password']);
$remoteaddr = $_SERVER['REMOTE_ADDR'];

// do insert
$sql = "INSERT INTO users (username,passw ord,email,regis tered,
name,location,l ast_ip)
VALUES ('$username_sql safe','$passwor d_md5','$email_ sqlsafe',NOW(),
'$name_sqlsafe' ,'$location_sql safe','$remotea ddr')";

$query = mysql_query($sq l,$_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]

Re: Quick MySQL insert quesiton

Xizor wrote:
[color=blue]
> I have the following code:
>
> $username_sqlsa fe = addslashes($dat a['username']);
> $email_sqlsafe = addslashes($dat a['email']);
> $name_sqlsafe = addslashes($dat a['name']);
> $location_sqlsa fe = addslashes($dat a['location']);
> $password_md5 = md5($data['password']);
> $remoteaddr = $_SERVER['REMOTE_ADDR'];
>
> // do insert
> $sql = "INSERT INTO users (username,passw ord,email,regis tered,
> name,location,l ast_ip)
> VALUES ('$username_sql safe','$passwor d_md5','$email_ sqlsafe',NOW(),
> '$name_sqlsafe' ,'$location_sql safe','$remotea ddr')";
>
> $query = mysql_query($sq l,$_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.[/color]

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

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

[Andy Hassall]

Re: Quick MySQL insert quesiton

On Sat, 09 Aug 2003 20:28:13 GMT, "Xizor" <nope@nope.co m> wrote:
[color=blue]
>I have the following code:
>
>$username_sqls afe = addslashes($dat a['username']);
>$email_sqlsa fe = addslashes($dat a['email']);
>$name_sqlsaf e = addslashes($dat a['name']);
>$location_sqls afe = addslashes($dat a['location']);
>$password_md 5 = md5($data['password']);
>$remoteaddr = $_SERVER['REMOTE_ADDR'];
>
>// do insert
>$sql = "INSERT INTO users (username,passw ord,email,regis tered,
>name,location, last_ip)
>VALUES ('$username_sql safe','$passwor d_md5','$email_ sqlsafe',NOW(),
>'$name_sqlsafe ','$location_sq lsafe','$remote addr')";
>
>$query = mysql_query($sq l,$_db);
>// end
>
>The username field has a UNIQUE index.
>
>Problem: when the username exists in the database $query comes out false.[/color]

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
}
}
[color=blue]
>But the PHP documentation says this shouldn't be the case, it should return
>true and then mysql_affected_ rows() will return 0.[/color]

No, that's for UPDATEs.

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

[Andy Hassall]

Re: Quick MySQL insert quesiton

On Sat, 09 Aug 2003 21:32:09 +0000, matty <matt+nntp@askm enoquestions.co .uk>
wrote:
[color=blue][color=green]
>> The username field has a UNIQUE index.[/color]
>
>If the username exists, then the query will *fail*.
>
>Anyway, you should be checking if the user exists first...[/color]

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

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