PDO database authentication with error message

[Jay88]

Here is my code, I am trying to create a function that connects to my local SQL DB using PDO instead of mysqli

It looks like I can connect, the issue is that...I can't get my error message to show. 

"Could not connect to the database"

will not come up, when I know that my user name and password are incorrect. 

I try changing the root name to test to get the error but it doesn't show up ....I can't see my mistake 

 

 

<?php
//load test ...un comment exit test db con  
//exit ('test db con'); 

}


class DB {
    
    protected static $con; 
    
    private function _construct() {
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=testdb', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false ); 
            
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
    }
    
    public static function getConnection(){
        
        //If this instance was not beem started, start it.
        if(!self::$con){
            new DB();
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
}

?>

 

Edited October 12, 2021 by Jay88

[Barand]

try

class DB {
    
    protected static $con; 
    
    
    public static function getConnection(){
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=testdb', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false ); 
            
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
}

$conn = DB::getConnection();

 

[requinix]

46 minutes ago, Jay88 said:

"Could not connect to the database"

Not the most helpful message, is it?

You get that when a PDOException is thrown. It's available as the $e variable and very likely has some useful information inside of it...

[Jay88]

 

I change it to this and I still don't get an error, any ideas?

 

<?php


class DB {
	
	protected static $con; 
	
	private function _construct() {
		
		$servername = "localhost";
		$database = "db1"; 
        $username = "root";
        $password = "password";

        try {
            $con = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
            // set the PDO error mode to exception
            $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Connected successfully";
			
        } catch(PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
        }

	}
	
	public static function getConnection(){
		
		//If this instance was not beem started, start it.
		if(!self::$con){
			new DB();
		}
		//Returns Writeable db connection
		return self::$con; 
	}
	
}

?>

 

[Jay88]

barand 

 

I also tried this but didn't quiet work 

 

class DB {
    
    protected static $con; 
    
    
    public static function getConnection(){
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=db1', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false ); 
            
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
        
    }
    
	
	public static function getConnection(){
		
		//If this instance was not beem started, start it.
		if(!self::$con){
			new DB();
		}
		//Returns Writeable db connection
		return self::$con; 
	}
}

 

[requinix]

Take the code Barand provided and apply the change you made using $e to it.

[Jay88]

requinix, I did, but it didn't work

FYI; I am running Xammp Apache and the latest version of PHP and MySQL

Edited October 12, 2021 by Jay88

[requinix]

Okay. What's the code you have now?

[Jay88]

My code... I think my issue is on the static public function and how is being called 

 

I am calling this from another file 

$con = DB::getConnection(); 

 

<?php   

class DB {
    
    protected static $con; 
    
    
    public static function getConnection(){
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=db1', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false );
            echo "Connected"; 
            
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
    public static function getConnection(){
        
        //If this instance was not beem started, start it.
        if(!self::$con){
            new DB();
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
}


?>

 

Edited October 12, 2021 by Jay88

[requinix]

Could be. Or it could be that you added the new "getConnection" without removing the old one.

[Jay88]

 

 

so the difference betweent my code and his code 

 

Is that I had it as 

private function _construct() 

in my original code. is that wrong on my part ???

 

 

<?php   

class DB {
    
    protected static $con; 
    
    private function _construct() {
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=db1', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false );
            echo "Connected"; 
            
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
    public static function getConnection(){
        
        //If this instance was not beem started, start it.
        if(!self::$con){
            new DB();
        }
        //Returns Writeable db connection
        return self::$con; 
    }
    
}


?>

 

Edited October 12, 2021 by Jay88

[requinix]

I'll skip the question about what's wrong and try again.

Take Barand's code. It's good code that demonstrates the singleton pattern, and is based on what you started with. Then do the thing with $e you tried that added getMessage() to the error output.

With those in place you will still get the error that you had before, but now it will include a message from $e that should point you towards what the underlying problem is.

[Strider64]

Here's my PDO connection class that have used over the years. Maybe this will help?

use mysql_xdevapi\Exception;
use PDO;
use PDOException;

class Database {

    private PDO $_connection;
    // Store the single instance.
    private static ?Database $_instance = null; // Don't initialize before it is called:

    // Get an instance of the Database.
    // @return Database:
    protected static function getInstance(): Database
    {
        if (!self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    public static function pdo(): PDO
    {
        $db = static::getInstance();
        return $db->getConnection();
    }

    // Constructor - Build the PDO Connection:
    public function __construct() {
        try {
        $db_options = [
            /* important! use actual prepared statements (default: emulate prepared statements) */
            PDO::ATTR_EMULATE_PREPARES => false
            /* throw exceptions on errors (default: stay silent) */
        , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            /* fetch associative arrays (default: mixed arrays)    */
        , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ];
        $this->_connection = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
        } catch (PDOException $e) {
            //echo $e;
            //echo "<pre>" . print_r($e->errorInfo, 1) . "</pre>";
            if ($e->errorInfo[1] === 1045) {
                echo "User has the wrong credentials " . $e->errorInfo[1] . "<br>";
                return false;
            }

            throw $e; // If PDO Exception error can't handle it throw it to Exception:

        } catch (Exception $e) {
            echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
        }
        return true;
    }

    // Empty clone magic method to prevent duplication:
    private function __clone() {

    }

    // Get the PDO connection:
    protected function getConnection(): PDO
    {
        return $this->_connection;
    }

}

 

to use simply do

$stmt = Database::pdo()->prepare($sql);

 

I just want to add this is just to add a PDO connection string to connect the Database Table, I would never send that error message on a production server.

Edited October 13, 2021 by Strider64

[mac_gyver]

7 hours ago, Jay88 said:

It looks like I can connect,

how do you know that?

one possible reason for not seeing your connection error message, for the case of intentionally invalid connection credentials, is that your php code is not being executed, perhaps due to being directly opened as a file in the browser instead of being requested via a url on your web server.

another possibility is that where you are trying to create the connection is inside of some html markup where any output from your code/php won't be seen unless you look at the 'view source' of the page in your browser (you should do any initialization like this, or any main php business logic, above the start of the html document so that you won't have an issue with things not being seen on a web page.)

[ginerjm]

And for those that don't need to use Classes, here is my dinosaur procedural code:

function PDOConnect($l_dbname=NULL, $l_msg=null, $l_options=null)
{
	if ($l_options == null)
	{	// set my default options
	  $l_options = array(PDO::ATTR_EMULATE_PREPARES => false,
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
			PDO::MYSQL_ATTR_FOUND_ROWS => true,
			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
	}
	if ($l_dbname == null)
		$host="mysql:host=localhost;charset=utf8";
	else
		$host="mysql:host=localhost;dbname=$l_dbname;charset=utf8";
	$uid = "user";
	$pswd = "pswd";
	try
	{
		$pdo = new PDO($host, $uid, $pswd, $l_options);
	}
	catch (PDOException $e)
	{
		if (strtoupper($l_msg) == "SHOWMSG")
			echo "Fatal Error<br>Failed to connect to database server.  PDO Error msg is:<br>".$e->getMessage();
		else
			echo "Fatal Error<br>Possible bad dbname?<br>Failed to connect to database server.  
			Sensitive error msg may be viewed with additional parm to call to PDOConnect(dbname,'showmsg')";
		return false;
	}
	if (!$pdo)
		return false;
	else	// all worked - return handle to pdo connection.
		return $pdo;
}

 

[Jay88]

The way i keep the code, this works 

 

class DB {
        
    protected static $con; 
    
    public static function getConnection(){
        
        try{
            self::$con = new PDO( 'mysql: host= localhost; dbname=db1', 'root', 'password');
            self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
            self::$con->setAttribute( PDO::ATTR_PERSISTENT, false );
                        
        } catch (PDOException $e) {
            echo "Could not connect to database."; exit; 
        }
        //Returns Writeable db connection
        return self::$con; 
    }
       
}

and I call it with this 

$con = DB::getConnection(); 

 

 

[Barand]

14 minutes ago, Jay88 said:

this works 

Good - that's what I told you to use 16 hours and many posts ago