Connecting php to MySQL

Introduction

There are various ways of connecting to MySQL database using php.

  1. MySQLi Object-oriented
  2. MySQLi Procedural
  3. PDO

Whenever there are multiple way of doing same thing. We get confuse becasue now we have to make a right selection. This is pretty sure that every method must have their own advantage and dis-advanteges.

You can refer to this article where I discussed about the advantage and dis-advantages of all these 3 available methods.

How databse connection works

In simple words database connection is method of sending commands of the database Server and receiving the response from the databse server.

A Database connection is a facility in computer science that allows client software to talk to database server software, whether on the same machine or not. A connection is required to send commands and receive answers. Wikipedia

To connect with the database we need three things.

  • Server Name
  • Database User Name
  • Database Password
  1. Server Name : In a server/machine php is installed and you want to make request to mysql then you need to specify the servername of MySQL server.
  2. Database User Name : Specify the username which is available in the database server.
  3. Database Password : Specify the password crosspoding to the database user.

Default username is root and there is no password for the root user.

If MySQL is installed in the same server/machine in which php is installed then you can use servername as localhost.

MySQLi Object-oriented

Code for creting database using php in MySQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

    <?php
        $servername = "localhost";
        $username = "username";
        $password = "password";

        // Create connection
        $conn = new mysqli($servername, $username, $password);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        // Create database
        $sql = "CREATE DATABASE myDB";
        if ($conn->query($sql) === TRUE) {
            echo "Database created successfully";
        } else {
            echo "Error creating database: " . $conn->error;
        }

        $conn->close();
    ?>

MySQLi Procedural

Code for creting database using php in MySQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

    <?php
        $servername = "localhost";
        $username = "username";
        $password = "password";

        // Create connection
        $conn = mysqli_connect($servername, $username, $password);
        // Check connection
        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        }

        // Create database
        $sql = "CREATE DATABASE myDB";
        if (mysqli_query($conn, $sql)) {
            echo "Database created successfully";
        } else {
            echo "Error creating database: " . mysqli_error($conn);
        }

        mysqli_close($conn);
    ?>

PDO

Code for creting database using php in MySQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

    <?php
        $servername = "localhost";
        $username = "username";
        $password = "password";

        try {
            $conn = new PDO("mysql:host=$servername", $username, $password);
            // set the PDO error mode to exception
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = "CREATE DATABASE myDBPDO";
            // use exec() because no results are returned
            $conn->exec($sql);
            echo "Database created successfully<br>";
            }
        catch(PDOException $e)
            {
            echo $sql . "<br>" . $e->getMessage();
            }

        $conn = null;
    ?>

You feedback can help you in getting better content from us. Do share you feedback