How do I capture SQL Errors

[Guest]

Hi,

I'm new to Dreamweaver (but I have an IT background).

I'm developing a php/mySQL application.

My question is how can I gracefully capture SQL errors such as unique index or primary key violations?

Currently if I try to add a row which would create a duplicate entry in a unique index, I just get a blank screen which says something like "Duplicate entry 'x' for key 'PRIMARY'.

Thanks in advance,

Chuck B

[David_Powers]

KCTex wrote:

I'm new to Dreamweaver (but I have an IT background).

I'm developing a php/mySQL application.

Dreamweaver's server behaviors are very basic. They're primarily intended as a learning tool, or for quick prototyping. Moreover, they use the PHP MySQL extension, which is no longer being developed, and is not recommended for new applications. These days, it's recommended that you use MySQLi or PDO. See http://docs.php.net/manual/en/mysqli.overview.php.

My question is how can I gracefully capture SQL errors such as unique index or primary key violations?

With PHP conditional logic. The following example shows how to adapt the default Dreamweaver Insert Record server behavior to handle a unique index violation:

$insertSQL = sprintf("INSERT INTO users (first_name, family_name, username, pwd, admin_priv)
VALUES (%s, %s, %s, %s, %s)",
         GetSQLValueString($_POST['first_name'], "text"),
         GetSQLValueString($_POST['family_name'], "text"),
         GetSQLValueString($_POST['username'], "text"),
         GetSQLValueString($_POST['pwd'], "text"),
         GetSQLValueString($_POST['admin_priv'], "text"));
    mysql_select_db($database_connAdmin, $connAdmin);
    $Result1 = mysql_query($insertSQL, $connAdmin);
    if (!$Result1 && mysql_errno() == 1062) {
      $error['username'] = $_POST['username'] . ' is already in use.
        Please choose a different username.';
    } elseif (mysql_error()) {
      $error['dbError'] = 'Sorry, there was a problem with the database.
        Please try later.';
    } else {
      $insertGoTo = "list_users.php";
      if (isset($_SERVER['QUERY_STRING'])) {
       $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
        $insertGoTo .= $_SERVER['QUERY_STRING'];
      }
      header(sprintf("Location: %s", $insertGoTo));
    }

The $error array can then be used to display an error message in the page.

This example comes from Chapter 15 of my book, "The Essential Guide to Dreamweaver CS4 with CSS, Ajax, and PHP", which goes into considerable detail showing how to customize the code generated by Dreamweaver. My more recent "Adobe Dreamweaver CS5 with PHP: Training from the Source" takes a different approach, by using components from the Zend Framework instead of the Dreamweaver server behaviors.

If you have an IT background, you might find the PHP Manual and/or the Zend Framework documentation sufficient without the need for a book.

[Guest]

David,

Thanks for your reply.

I was able to take your sample code and after a lot of "googleing" and brutal force testing, I was to get my update page to redirect to an error page in the case of an error.

Thanks again.

BTW: I clicked on "helpful" rather than "answered", but I consider this question answered.

Thanks again.

Chuck B.

[Guest]

Reply from David Powers answered my question. (I accidentally marked it as helpful instead of answered.)