catch postgres error in PHP

Question

Hello

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

Answer 1

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

Answer 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.