How to display mysql error while using PDO in a connection class?

Question

I am trying to write a small class so I can use it to connect to my databases But I am havinf an issue that PDO is not displaying an error.

What I am trying to do is to display mysql error when the query fails so I know what is the error and fix it.

In my call I have 4 methods where i need to catch mysql errors.

startConnection()
getOneResult()
processQuery()
getDataSet()

This is my current class Can some one please show me how to display the mysql error. Note I tried to use try catch to catch the error but that did not work for me.

Thanks for your help

<?php

class connection {

    private $connString;
    private $userName;
    private $passCode;
    private $server;
    private $pdo;
    private $errorMessage;

    private $pdo_opt = array (
                            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                            );



    function __construct($dbName, $serverName = 'localhost'){

        //sets credentials
        $this->setConnectionCredentials($dbName, $serverName);

        //start the connect
        $this->startConnection();

    }

    function startConnection(){


            $this->pdo = new PDO($this->connString, $this->userName, $this->passCode, $this->pdo_opt);

            if( ! $this->pdo){

                $this->errorMessage  = 'Failed to connect to database. Please try to refresh this page in 1 minute. ';
                $this->errorMessage .= 'However, if you continue to see this message please contact your system administrator.';
                echo $this->getError();

            }

    }


    //this will close the PDO connection
    public function endConnection(){

        $this->pdo->close;
    }

    //return a dataset with the results
    public function getDataSet($query, $data = NULL)
    {
        $cmd = $this->pdo->prepare( $query );

        $cmd->execute($data);
        return $cmd->fetchAll();
    }


    //return a dataset with the results
    public function processQuery($query, $data = NULL)
    {
        $cmd = $this->pdo->prepare( $query );

        return $cmd->execute($data);
    }


    public function getOneResult($query, $data = NULL){
        $cmd = $this->pdo->prepare( $query );
        $cmd->execute($data);
        return $cmd->fetchColumn();
    }

    public function getError(){
        if($this->errorMessage != '')
            return $this->errorMessage;
        else
            return true;  //no errors found

    }

    //this where you need to set new server credentials with a new case statment
    function setConnectionCredentials($dbName, $serv){

        switch($serv){

            case 'NAME':
                $this->connString   = 'mysql:host='.$serv.';dbname='.$dbName.';charset=utf8';
                $this->userName     = 'user';
                $this->passCode     = 'password';
            break;

            default:
                $this->connString   = 'mysql:host=localhost;dbname=rdi_cms;charset=utf8';
                $this->userName     = 'user';
                $this->passCode     = 'pass!';
            break;

            }

    }



}


?>

could you paste how you used try/catch and what Exception type you caught in it? Also, it's important where you placed the try/catch block(s)? — Alexey
– Alexey, Commented Mar 25, 2013 at 18:30

Your Common Sense · Accepted Answer · 2013-03-25 19:19:56Z

2

You shouldn't necessarily display an error message, but only raise it. Displaying mysql error messages makes your site less secure and looks mean.

So, the only your concern have to be

to make PDO theow exceptions on SQL errors.
to set up PHP to make error messages displayed on a development server and logged on a live one

Thus, try this code at the top of your scripts

ini_set('display_errors',1);
error_reporting(E_ALL);

and then run one of these functions with intentional mysql error. Your script should be halted displaying an error message.

Update:
Well, I tried to run your code and these 2 magic lines of code from above helped me other way:

Notice: Undefined variable: pdo_opt in D:\SERVER\htdocs\0.php on line 37

means variable was misspelled (You have to use $this->pdo_opt instead) - so, this was a reason why PDO didn't trow exceptions.

Here is a lesson, why error_reporting should be always E_ALL

edited Mar 25, 2013 at 19:19

answered Mar 25, 2013 at 19:01

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

3 Comments

Jaylen Over a year ago

Thanks again sir. The reason why I am trying to display error is for debugging reason thats all. I will use ini_set so I can look at my error. I will update this question if i have not further questions.

Jaylen Over a year ago

The error that you spotted in my class way the reason why I was not seeing the errors :) So I do see errors now. Since you lead me to the correct direction in writing this class to begin with can you please give me some feedback on the class and how can i improve it? thanks again for your feedback :)

Your Common Sense Over a year ago

@Mike I wish I had someone to review my own class too :) But people seldom tend to review other people's codes. So, I content myself with a rule of thumb saying "The best reviewer is an everyday real life use". Just try to use your class for some real life application and see if it fits. Though I have some notes at glance. 1. You need a set of methods to encapsulate original PDO methods, like rowCount(),insertId(),fetch() to be used within a loop for the large datasets and so on. 2.Your setConnectionCredentials seems unreliable. why not to make it accept an array with all possible credentials?

Hayden · Accepted Answer · 2013-03-25 18:49:58Z

0

I would get rid of or not use the methods getError(), getOneResult(), processQuery() and getDataSet(). Use the class to just manage your PDO connections. Then wrap your PDO statements in a try...catch.

try
{
    $conn = new connection( "mydatabase" );
    $db = $conn->getPdoInstance();
    $stmt = $db->prepare( "SELECT * FROM `something`" );
    $stmt->execute();
}
catch( PDOException $e )
{
   // Catch the error message and do whatever you need to do with it
}

edited Mar 25, 2013 at 18:49

answered Mar 25, 2013 at 18:37

Hayden

2,1222 gold badges14 silver badges20 bronze badges

5 Comments

Your Common Sense Over a year ago

getOneResult() method can reduce amount of useless code by a factor of four. Please, do not object people who want to make their code less bloated.

Hayden Over a year ago

getOneResult() doesn't even do what it says it does. "getOneResult" suggests it gets one row but it doesn't, it could potentially return multiple results but only one column.

Your Common Sense Over a year ago

*sigh* Are you certainly sure it will? You have good experience with PDO?

Hayden Over a year ago

Okay, so it would only fetch the first result but there's also no room for specifying which column it should fetch, not to mention if the result does query more than one row, it's only going to return the first row but what you may need is in another row. There's better ways of doing it and it seems a lot of PDO functionality is being limited.

Your Common Sense Over a year ago

Well, when there are more fields or rows - just don't use this function. Use one you find appropriate for the particular result set. When you need just single scalar value - why not to use a function to get a single scalar value? "There's better ways" doesn't mean "there's only one way everyone have to follow". though if you can show one of these better ways and prove it's indeed better - no doubt the OP will be grateful.

Collectives™ on Stack Overflow

How to display mysql error while using PDO in a connection class?

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related