'Elegant' messages from MySql

bobcox

Sorry if this is covered elsewhere but I've searched and read and I either don't understand or the threads don't cover what I'm looking for.

I would like to show user friendly responses when a data entry is incorrect eg:

Entering usernames into a table where the username field is set as 'unique'

On entering duplicate name. message reads:

"Duplicate entry 'username' for key 2"

How can I make this say eg:
"Sorry that username is already taken, please try another"

Any help greatly appreciated

etully

One way to do it is to perform an IF statement on the message to check to see which error MySQL is reporting. For example, you might say:

IF the error message STARTS WITH the string, "Duplicate Entry" THEN print "Sorry that username is already taken, please try another"

Another way to do it is to not put the responsibility of field validation on MySQL.

For example:
select id from users where username='$username'
Count the number of rows returned with mysql_numrows command
If There is one row, then print "Sorry that username is already taken, please try another"
else write username

The reason I like to do the validation in PHP instead of relying on MySQL is that there are some situations such as these: You decide that no swear words are allowed in usernames, You decide that if a user selects "Texas" as their state and also has the word "cowboy" in their username, then they cannot select more than "4" as their number of automobiles. Since MySQL can't return errors for those situations, then you might as well do all the validation at the PHP level.

Piranha

I think you mean the error report you get if you use mysql_query($sql) or die(mysql_error()). You should use that on your development server, but never in the production server. mysql_error is just a notification to show what is wrong and used for development, when it is finished and ready for production you should not use those errors anymore.

For your real question there is not only one answer. One way, and the best way if you ask me, is is to check if the username exist when you try to add another, and if it does then tell so. Program to cover every possible way the users can do something wrong or get information from your system.

Another way is to have a standard message if something is wrong. This can (and should) be used along the first solution since you may get into troubles you can't control, for example the database may shut down. You can of course also develop your own way of knowing what the problem is using this way, but I don't recommend it.

function database_error()
{
    echo "There currently are problems with the database. Please try again in a few seconds."
    return FALSE;
}
// normal code here, whatever you want
// below code to do a database query
$sql = "SELECT column FROM table";
$result = mysql_query($sql) or die(database_error());

Piranha

etully wrote:
The reason I like to do the validation in PHP instead of relying on MySQL is that there are some situations such as these: You decide that no swear words are allowed in usernames, You decide that if a user selects "Texas" as their state and also has the word "cowboy" in their username, then they cannot select more than "4" as their number of automobiles. Since MySQL can't return errors for those situations, then you might as well do all the validation at the PHP level.

Actually it is possible to do those kind of checks with triggers. But the main problem is that PHP won't know what the error is, just that it is impossible to add a new car. That's why the check should be in PHP. But it should be in the RDBMS as well. What if someone (or you) are programming a new application that should use an existing database. If something is forgotten then it might cause trouble with database integrity. If it is checked in the database as well the database integrity will stay the same.

etully

If something is forgotten then it might cause trouble with database

That is true... however to address that problem, I would use a centrally located class / method / function that handles the write and the validation which again leaves the validation at the PHP level.

Troubleshooting is hard enough (especially on someone else's code) without having triggers at the database level.

Call me a luddite but I would prefer for the programming logic to happen at the programming level instead of at the database level. Maybe it's just a personal preference or maybe it's just the experiences I've run into over the years but something about triggers makes my Spidey Sense tingle.

So bobcox (from Scrubs?), you can do it either way - everyone ends up developing their own style.

Piranha

etully wrote:
Call me a luddite but I would prefer for the programming logic to happen at the programming level instead of at the database level. Maybe it's just a personal preference or maybe it's just the experiences I've run into over the years but something about triggers makes my Spidey Sense tingle.

So bobcox (from Scrubs?), you can do it either way - everyone ends up developing their own style.

I think you misunderstood me. I don't mean that you should not check it in PHP. I mean that to do it "correct" you should check it in both the database layer and in PHP. Of course, if you are the sole programmer and noone else will take over that code then there is no reason to check it with triggers. But if many persons are involved, and especially if they have responsibility for different layers, it should be checked in basically all layers.

bobcox

Thanks gentlemen - soory for the delay in responding

I don't get the finer points of your replies, but I understand the gist - that I need to create a query to check whether unique data exits and to arrange appropriate direction from the result

I'll have a go - looks like my Dreamweaver 'insert record' command needs to be taken apart!