I watched the linked to video and the next one in the series. The Author of that code has a basic understanding, but no actual experience.
Besides unconditionally echoing the raw database error in the catch {} block, which gives hackers the file system path, which often contains the hosting account name, and the database username, he doesn't understand how PDO uses exceptions, and wasn't observant enough to notice that the error information being output by his try/catch block for a connection error and php's handling of the exceptions, for the case where he misspelled ->execute(), was different. He clearly stated that in order to get the try/catch block to work for the connection, that the setAttribute() statement was necessary. This is incorrect. The connection will always throw an exception on an error, and his try/catch block will always work. The setAttribute() statement that he showed, caused the rest of the PDO database statements to throw an exception on an error. So, removing the try/catch block for the connection, as I suggested, in addition to simplifying the code, will let php catch and handle the connection error exactly the same as for the rest of the database statements, where you can control what happens with the actual error information by simply changing php's display_errors and log_errors settings. The only time you should have a try/catch block for database errors, is if the error means something to your application, and you need to handle it in your code, such as someone submitting duplicate data.
The Author also seems to think that using a loop to retrieve data from a query that will match only a single row and using a return statement inside of that loop is good coding. Using this logic will easily result in only retrieving the first row from a query that intends to match one or more rows. If a query is expected to match only a single row, just fetch the data from that row. If a query is expected to match one or more rows, use a loop or just fetch all the data at once using the fetchAll() method.
Can you please point to good example?
The following class extends the PDO class and adds a general purpose non-prepared/prepared query method.
// extend pdo class with a generic non-prepared/prepared query method
// by extending the pdo class, all the PDO base methods are available without having to write wrapper methods in the class
class _pdo extends pdo
{
public function __construct($dsn,$username=null,$password=null,$driver_options=null)
{
parent::__construct($dsn, $username, $password, $driver_options);
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
$this->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // turn off emulated prepared queries, run real prepared queries
$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
}
// general purpose query method. accepts an optional array of input parameters and returns a pdostatement object
public function query($sql,$params=array())
{
if($params){
// prepared query
$stmt = parent::prepare($sql);
$stmt->execute($params);
// note: to execute a prepared query more than once, you would call the ->execute() method on the returned stmt object, supplying an array of new input values for each new execution
} else {
// non-prepared query
$stmt = parent::query($sql);
}
// return the pdo statement object
return $stmt;
}
}
// example usage, based on your starting code
$db_servername = "localhost";
$db_username = "root";
$db_password = "";
$db_name = "miles_away_travel";
$db_charset = "utf8mb4";
//set dsn
$dsn = "mysql:host=$db_servername;dbname=$db_name;charset=$db_charset";
//create an instance of the user defined _pdo class, which extends the PDO class
$pdo = new _pdo($dsn,$db_username,$db_password);
// at this point $pdo is an instance of the extended PDO class. you can use the general purpose non-prepared/prepared query() method from the user written class or any of the PDO methods.
Any class that is dependent on having a database connection should use "dependency injection" (a web search will tell you what this means) to get access to the database connection.
BTW - you should NOT use the root account and a blank password for your application's database connection. You should create a specific user that only has the necessary permissions that your application needs.