Jump to content

PDO database authentication with error message


Jay88
 Share

Recommended Posts

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 by Jay88
Link to comment
Share on other sites

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();

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

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; 
	}
	
}

?>

 

Link to comment
Share on other sites

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; 
	}
}

 

Link to comment
Share on other sites

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 by Jay88
Link to comment
Share on other sites

 

 

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 by Jay88
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Strider64
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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;
}

 

Link to comment
Share on other sites

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(); 

 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.