PDO Connection

I need some advice on best practices with regards to PDO connections in PHP programs.

I am writing an API that accesses a MySQL database via PHP code. I've seen umpteen people insist that PDO is much better than mysqli and am quite willing to go that way. I think I have a pretty good handle on the techniques for writing the various PDO statements that read, insert, update and delete the data and actually have all of that working in my prototype. However, I am seriously perplexed on the right way to get and share a PDO connection within my code.

I think the big reason for my confusion is something many other people have noted: the existence of many tutorials on database access but the (almost?) complete absence of *good* tutorials. I'm not sure how seriously to take ANY of the instructions I'm seeing.

The other reason for my confusion is the sniping I see at StackOverflow on the issue of getting and using that PDO connection. Any answers anyone writes on this subject seems to be attacked by other helpers for one reason or another. Perhaps the most promising reply I've seen is the first one in this question; it has dozens of upvotes but is also attacked by some responders: https://stackoverflow.com/questions/11369360/how-to-properly-set-up-a-pdo-connection

Despite that, I'd be game to try that suggestion but it is so vague that I don't know what code to write. The responder only writes the code in a rather symbolic way and I'm having trouble turning it into real code.

Is this really the best way to go? If so, how do I write the actual code? Does anyone have a complete example of actual code that works this way that I could see?

I found a tutorial on this subject where the author claims to be the most upvoted responder to MySQL/PDO questions at stackoverflow but I'm not finding his (?) tutorial terribly helpful even though he claims it is the best one in existence. He is clearly not a native English speaker and his attempts at expressing complicated information in English are sometimes more confusing that helpful. https://phpdelusions.net/pdo Furthermore, he typically gives fragments out of context that also serve to confuse more than clarify his points.

If anyone can point me to an example of real code that conforms to best practices - or a tutorial that illustrates the same - I'd be very grateful.

 

Instead of strait PDO you could use a well developed ORM like Doctrine or Propel, there's a few others as well. There are pros and cons of using an ORM, but a big benefit is documentation. They do add a fairly large amount of code to a project. They have some often frustrating issues such as not being able to perform joins without foreign key relationships.

The one you linked to looks fairly comprehensive, but I agree the writer is not a native english speaker.

See if this one makes more sense: https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection

 

There's a LOT of bad advice on connecting to databases regardless of if you're using the outmoded mysql, or the "new" (If a decade can be called new) mysqli or PDO. Hence the 'attacks' as you put it on bad methodology. But really that's just the different attitudes and approaches people have to problems talking as there are a LOT of different ways of accomplishing the same thing. The big problem IMHO is when people just overthink the solutions by using variables for nothing, new independent classes instead of leveraging object mutability, and so forth.

I mean my own take on connecting to PDO (dumbed down from my ACTUAL approach)

class Database extends PDO {

	private static function exceptionHandler(Exception $e) {
		error_log('PDO Connection error in "Database" object. ' . $e->getMessage());
		die('Fatal error connection to database, the Administrator can check the PHP logs for more information.');
	} // Database::exceptionHandler
	
	public function __construct($settings) {
		set_exception_handler([__CLASS__, 'exceptionHandler']);
		parent::__construct(
			'mysql::host=localhost;dbname=test', // DSN
			'testingUser', // username
			'BSWsgywA4H76' // password
		);
		restore_exception_handler();
	} // Database::__construct (override from PDO)
	
} // Database

Code (markup):

With the above you just:

$db = new Database();

... and be done with it. Boom connected PDO object. I register the exception handler instead of using TRY/CATCH as sometimes that approach can still output certain error codes before PHP's "TRY" even has a chance to stop anything. Registering the handler catches everything.

There are a LOT of extra steps people take for security reasons -- some are pretty advanced stuff like forcing mono-connection, named queries (though I'm one of the few who do this), forced scoping, and so forth -- but for a basic implementation the above is pretty solid.