no insert, but no mysql_error either!?

TobyRT

Hi,

I've had a script running (quite happily) for a few days now and I've finally put it live onto the site for users to register. In many cases it seems to work fine, but in others I'm having some weirdness.

The query has worked flawlessly - the only error that can possibly (I thought) come up is when a user tries to re-register using an already registered email address - the email field is unique, so obviously returns an error when trying to add a duplicate email.

As such, I wrote the query so that if an error (the only error!) occurs, it says "this email address already exists, please contact us if you wish to renew..." and so on.

What's happening instead is that users who DON'T exist are getting this error message. OK, you think, there's another SQL error coming in somewhere, replace your custom error message with

mysql_query($query) or die (mysql_error());

This I duly did. I deliberately entered a duplicate email address - no error.

I deliberately changed one of the fields in the INSERT query to a field that didn't exist - again, no error!

Do you have any idea what might be causing this and how I can fix it please?

[edit] I've just realised how intermittent it is - I spoke to someone who registered and had this problem - they tried again a few minutes later, entering the same details, but this time it worked. I'm going to say "please refresh the page" in the error message (since I've found this works) but it would be good to stop it from happening...

Many thanks!

veridicus

I would check for an existing email address with a select statement, not relying on the insert to fail.

I would also check mysql_error after every query instead of relying on "or die". Maybe mysql_query is returning a "true" value while there is still some error.

TobyRT

Thanks Veridicus - good thought on the select statment

What do you mean about or die() though? All my queries run as outlined above - could you give an example of what you mean please?

Sxooter

Actually, either way you do it is fine, it's just that your error handling needs a bit of expanding.

If you try to insert a record and get a duplicate key error, you need to be checking for THAT error, because there are other possible failure modes you could get back.

Letting the database catch it is fine, and even if you run the select first, there's always the off chance that some other user inserted the same email address between your select and your insert, so you need to check for the error anyway.

veridicus:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

If MySQL is returning anything other than false, there's no error.

TobyRT

Could you advise how I check for that error specifically please?

I didn't realise you could do it like that!

Sxooter

Parse the string you get back for the message that tells you why it failed. If you don't get an error message you recognize, then just dump the mysql error message to the user and / or log it somewhere.

TobyRT

That's the weird thing though - I'm not getting ANY error message back, even when I deliberately add a duplicate entry

Sxooter

That's cause you're supposed to put the link resource in as an argument to mysql_error():

$error = mysql_error($conn);

Note that you can use mysql_errno($conn) to get the error number, which means no parsing as such

TobyRT

so that would be:

$query = "insert into details (email) values ('$email')";
$insert = mysql_query($query, $conn) or die (mysql_error($query, $conn));

Sxooter

TobyRT wrote:

so that would be:

$query = "insert into details (email) values ('$email')";
$insert = mysql_query($query, $conn) or die (mysql_error($query, $conn));

Ummmmm. No. Look up the php docs. It'd be:

or die(mysql_error($conn));