catch postgres error in PHP

Question

I have table in postgressql with primare key (pk_user). If i insert wrong parametrs.

postgres call exception:( ERROR: duplicate key value violates unique constraint "pk_user" )

this is ok, but i like catch this error and transform it to user interface ( this username is used)

my php script:

$sql="INSERT INTO user (....) VALUES (....)"
@$result=pg_query($dbconn,$sql);
if(!$result) {
 $error= pg_last_error($dbconn);
 if($error==='ERROR: duplicate key value violates unique constraint "pk_user"')
   $outputmesage="this username is used";
....
}
else {
.....
}

but construction if($error==='ERROR: duplicate key value violates unique constraint "pk_user"') is wrong. I don know what write this. Function strcmp(str1,str2) is wrong too.

P.s: I'm sorry for my bad english

My comment is a bit irrelevant, but shouldn't you first check if user name is already used and then try to insert it? I do not like (maybe it's personal) the approach of doing something that I know it might be wrong and the see from the side-effects the cause. — Dimitrios Mistriotis
– Dimitrios Mistriotis, Commented Jul 12, 2010 at 15:51
dimitris mistriotis: No, this is the wrong way. Even if you check it first, you have no guarantee someone will not ask for the same username just between your check and insert (unless in a transaction - but that restricts concurrency and is needless there as just a single query suffices). Apart from the correctness of Torenaga's approach, it is also more effective, because it always needs just a single query, whereas you make two queries most of the time. — Ondřej Bouda
– Ondřej Bouda, Commented Jan 8, 2013 at 16:55

Milen A. Radev · Accepted Answer · 2010-07-12 19:14:52Z

2

You can use pg_result_error_field but then you have to use pg_send_query and pg_get_result (instead of pg_query and its better alternative pg_query_params):

pg_send_query($dbconn,$sql);
$res = pg_get_result($dbconn);

# check for "UNIQUE VIOLATION"
if(pg_result_error_field($res,PGSQL_DIAG_SQLSTATE) == '23505') {
    ...

Another way is to use PDO, set the error handling to "ERRMODE_EXCEPTION", handle exceptions and look into the exception's code property (which again should contain the SQLSTATE error code.

edited Jul 12, 2010 at 19:14

answered Jul 12, 2010 at 15:34

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Lukaszczyk · Accepted Answer · 2010-07-12 14:47:34Z

0

before insert make select for this user name and it solve the problem.

but if you try to minimize the number of queries to db you can stay with your solution, just change the condition to sth like this:

if(strstr($error,"duplicate key value")) {
   $outputmesage="this username is used"; 
}

hope it'll be helpfull

answered Jul 12, 2010 at 14:47

Adam Lukaszczyk

4,8863 gold badges24 silver badges22 bronze badges

Collectives™ on Stack Overflow

catch postgres error in PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related