catching errors from Postgresql to PHP

Question

I want to catch and show the error(in a way that i choose) of a query on the web page using php . So instead of the code below

$result=pg_query($connection,$query);

if($result){
    //success

}
else{
    echo pg_last_error($connection);
}

can i use a method like error code matching or something else to achieve things like

if(error equals duplicate value error){
 echo "this value already exists";
}
else if(error equals different type error){
 echo "You should enter wrong type for column blabla"
}

Note I am using postgresql

@AleksG I don't want to use pg_last_error function . I want to write error message manually for corresponding error — woryzower
– woryzower, Commented Sep 10, 2012 at 10:03

Daniel Vérité · Accepted Answer · 2019-08-19 15:38:47Z

17

It's possible to retrieve the desirable standard SQLSTATE errcode, but there's a trick: the query has to be sent through the asynchronous pg_send_query() instead of the synchronous pg_query(). This is because pg_query() returns false on error instead of the resource that is required to peek at the error details.

When pg_get_result() is called after pg_send_query, it will block anyway until the query is done, so it does not really complicate things compared to the synchronous case. And it returns a result which can be fully exploited for precise error handling.

Example:

if (pg_send_query($db, $query)) {
  $res=pg_get_result($db);
  if ($res) {
    $state = pg_result_error_field($res, PGSQL_DIAG_SQLSTATE);
    if ($state==0) {
      // success
    }
    else {
      // some error happened
      if ($state=="23505") { // unique_violation
        // process specific error
      }
      else {
       // process other errors
      }
    }
  }  
}

Also, if the argument passed to pg_query might contain several SQL statements (separated by semicolons), the above example should be extended to retrieve all results in a loop, as mentioned in the comment by @user1760150. Compared to pg_query which returns only the last result, pg_get_result in a loop gives access to the results of each statement of the combined query.

edited Aug 19, 2019 at 15:38

answered Sep 10, 2012 at 12:08

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

1 Comment

user1760150 Over a year ago

It should be noted that this behavior does differ from pg_query when sending multiple sql statements at once. pg_query will have the results of the last sql statement, where as pg_get_result will return the result of each query in the order of execution. If you want to perfectly mirror the behavior of pg_query, you need to add a while loop to capture the last result.

j0k · Accepted Answer · 2012-09-10 09:58:52Z

6

You should parse the return of pg_last_error to know the type of error. So I would go to sth like this :

$result = pg_query($connection,$query);

if($result)
{
  //success
}
else
{
  $error = pg_last_error($connection);

  // you need to adapt this regex
  if (preg_match('/duplicate/i', $error))
  {
    echo "this value already exists";
  }
  // you need to adapt this regex
  elseif(preg_match('/different type/i', $error))
  {
    echo "You should enter wrong type for column blabla"
  }
  else
  {
    // different error
  }
}

answered Sep 10, 2012 at 9:58

j0k

22.8k28 gold badges81 silver badges90 bronze badges

5 Comments

woryzower Over a year ago

Logical.But Isn't there more efficient or built-in way like if(errorcode==errorcodeof(duplicate_key) --> do sth

j0k Over a year ago

Well maybe, but have a look a this comment on php.net. The guy perform the deadlock detected by doing a stripos on the error. But I agree, if you can retrieve the code error directly, it could more efficient.

Craig Ringer Over a year ago

Yick. Is that safe in the face of I18N and translations? Can't you get the SQLSTATE from PHP and use that instead? That'd be the most correct thing to do.

woryzower Over a year ago

@CraigRinger i couldn't find a way to get sqlstate from php for postgresql

Aleks G Over a year ago

Most of my data logic is done in stored functions in postgres. I do error checking there and raise exceptions if invalid data is detected. With these exceptions, I define the codes myself.

Community · Accepted Answer · 2023-11-17 20:29:45Z

2

You can get access to SQLSTATE through the two main drivers.

https://www.php.net/manual/en/function.pg-result-error-field.php

http://www.php.net/manual/en/pdo.errorcode.php

edited Nov 17, 2023 at 20:29

CommunityBot

11 silver badge

answered Sep 10, 2012 at 11:27

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Collectives™ on Stack Overflow

catching errors from Postgresql to PHP

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related