By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,216 Members | 1,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,216 IT Pros & Developers. It's quick & easy.

Quick MySQL insert quesiton

P: n/a
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.
Jul 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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...
Jul 16 '05 #2

P: n/a
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)
Jul 16 '05 #3

P: n/a
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)
Jul 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.