PHP Connect to MySQL Main Tips

PHP Connect to MySQL Main Tips

• Two main methods to work with the MySQL database: MySQLi extension and PDO (PHP Data Objects).
• Both methods have their advantages.
• This post demonstrates three different examples how to connect to a database: MySQLi (object-oriented), MySQLi (procedural) and PDO.

---

MySQLi Installation

MySQLi extension usually installs automatically with PHP5 MySQL package. This works on Linux and Windows operating systems.

For installation details, go to: http://php.net/manual/en/mysqli.installation.php

---

PDO Installation

For installation details, go to: http://php.net/manual/en/pdo.installation.php

---

Open a Connection to Database

Before starting to modify the MySQL database, we need to establish the connection with a server.

This example shows how to make a connection using MySQLi Object-Oriented method.

Example (MySQLi Object-Oriented)

1. <?php
2. $servername = "localhost";
3. $username = "name_of_the_user";
4. $password = "users_password";
6. // Create connection
7. $conn = new mysqli($servername, $username, $password);
8. // Check connection
9. if ($conn->connect_error)
10. {
11. die("Failed to connect: " . $conn->connect_error);
12. }
13. echo "Connected successfully";
14. ?>

 Try live on Hosting

This example displays different MySQLi method.

Example (MySQLi Procedural)

1. <?php
2. $servername = "localhost";
3. $username = "name_of_the_user";
4. $password = "users_password";
6. // Create connection
7. $conn = mysqli_connect($servername, $username, $password);
9. // Check connection
10. if (!$conn)
11. {
12. die("Failed to connect: " . mysqli_connect_error());
13. }
14. echo "Connected successfully";
15. ?>

 Try live on Hosting

There is a database (myDatabase) specified in the PDO example below. This is because PDO requires a database for the connection. Examples above do not require a database name for connecting to the server (it will be needed when accessing the database itself).

Example (PDO)

1. <?php
2. $servername = "localhost";
3. $username = "name_of_the_user";
4. $password = "users_password";
6. try
7. {
8. $conn = new PDO("mysql:host=$servername; dbname=myDatabase", $username, $password);
9. $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
10. echo "Connected successfully";
11. }
12. catch(PDOException $e)
13. {
14. echo "Failed to connect: " . $e->getMessage();
15. }
16. ?>

 Try live on Hosting

Note: One of the benefit of using PDO is the “try-catch” blocks. It can catch any errors in the “try” block and execute error handling code in “catch” block.

---

Close the Connection

The connection closes automatically when the script stops running. If you want to close the connection earlier, use the following syntax:

Example (MySQLi Object-Oriented)

1. $conn->close();

 Try live on Hosting

Example (MySQLi Procedural)

1. mysqli_close($conn);

 Try live on Hosting

Example (PDO)

1. $conn = null;