Use PHP or SQL for error check

Question

Give the following table:

CREATE TABLE User (
Email VARCHAR(256) PRIMARY KEY,
Name VARCHAR(256),
);

I am trying to insert date into the table. To check for duplication, should I use SQL to select email from user where email = $email and check the number or rows return is 1 and if it is 1, I just use php to print error message OR Should I just try to insert the data into table and use the following to print error?

mysql_query('INSERT INTO ...');
if (mysql_errno() == 1062) {
    print 'no way!';
}

Which is a better way?

you should use mysql_error message – Pragnesh Chauhan Oct 14 '12 at 07:07 — Pragnesh Chauhan, Oct 14 '12 at 07:07

score 0 · Answer 1 · edited Oct 14 '12 at 07:13

0

You can go for a query like this :

$sql = "INSERT INTO `table` VALUES ('$email','$Name')"
      ." WHERE NOT EXISTS (SELECT * FROM `table` WHERE `email`='$email')";

mysql_query($sql) or die("There's a duplicate.");`

edited Oct 14 '12 at 07:13

Frederick Marcoux

2,195
1
26
57

answered Oct 14 '12 at 07:08

venkatKA

2,399
1
18
22

Hi Sir, thanks for the sql code. Didn't really think of this way. – mikey Oct 14 '12 at 07:19
-1 for use of die() in the example: it is almost never acceptable to just drop out of a script. There are also many reasons why the database should error, not just a duplicate record. – HorusKol Oct 14 '12 at 07:25

score 0 · Answer 2 · answered Oct 14 '12 at 07:14

0

Generally it's better to let the DBMS do the checking, because the functionality is already there and tested. You just need to handle the error messages.

If you insist on using your own code to do the checking, be prepared for many hours of brainstorming (given the complexity of the problem solved).

answered Oct 14 '12 at 07:14

iCantSeeSharp

3,880
4
42
65

Hi sir, thanks for the tips. But if the table now consists of Email VARCHAR(256) PRIMARY KEY, Name VARCHAR(256) NOT NULL, if the email is duplicated and name is null, the mysql will returns the last known error, how do I print both error messages? – mikey Oct 14 '12 at 07:18
You can have a general error message or print the last error message and let the user fix the input 1 step at a time. – iCantSeeSharp Oct 14 '12 at 07:23
A good PHP/MySQL application applies verification and validation of data at all levels - you should pick up on the NULL data value when validating your POST data (or whatever source) before you even pass the variable as part of the query. – HorusKol Oct 14 '12 at 07:33
But doesnt this defeat the purpose of having NOT NULL in my table? – mikey Oct 14 '12 at 07:37

Use PHP or SQL for error check

2 Answers2