How do I connect to my database via PHP?

PHP (Personal Home Pages) programming language started as a scripting language, but with the development of the Internet and over the years it has evolved into a full specifications (Full Stack) language with a key development axis of object orientation (Object Oriented). 

The implications of object-orientation can be seen in all parts created with the language such as MySQL. So we have PDO (PHP Data Objects) interface for MySQL and of course the already old MySQLi interface is also object oriented, but also supports legacy - Procedural type of connections. Supporting an older interface type (simple MySQL connection) has ceased since 2012, so we recommend the use of more new standards as MySQLi and PDO.

PDO and MySQLi comparison

PDO library is purely object-oriented and because the future of PHP is close to object oriented approaches, definitely the use of PDO is a guaranteed long term solution. It also supports connectivity with 12 different database management systems as opposed to MySQLi approach which works only with MySQL. 

MySQLi library (i means improved) as mentioned above has a Procedural Interface (procedural API), so it makes easier the transition from legacy simple MySQL connections to MySQLi connections, secondly, the procedural interface is away from the philosophy of the future, so sooner or later you may need to change again the implementation of the procedural approaches.

Both PDO and MySQLi libraries support Prepared Statements that help protect your application from SQL injections and are particularly important for the security of your web application.

Create MySQL connection

Below are described three ways to connect to MySQL. Two concern the MySQLi (Object Oriented and Procedural) and the third PDO.

1. Object Oriented MySQLi

   $host = "localhost";
   $username = "username";
   $password = "password";
   
   // Connection creation
   $connection = new mysqli($host, $username, $password);
   
   // Check connection
   if ($connection->connect_error) {
       die("Connection failed: " . $connection->connect_error);
   }
   echo "Connected successfully";
   $connection->close(); // Close connection
   ?>

2. Procedural MySQLi

   $host = "localhost";
   $username = "username";
   $password = "password";
   
   // Connection creation
   $connection = mysqli_connect($host, $username, $password);
   
   // Check connection
   if (!$connection) {
       die("Connection failed: " . mysqli_connect_error());
   }
   echo "Connected successfully";
   mysqli_close($connection); // Close connection
   ?>

3. PDO

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

It is worth noting that at the end of each script we close the connection to MySQL to release the resources that it binds.