PHP and MySQL 11: connection to database

---

Happy Coding

---

Home Previous

In this post, we will learn about how to create connection to database between PHP as server-side with MySQL as database-engine.

There are 3 ways that will we use:

1. MySQLi procedural
2. MySQLi object-oriented
3. PDO

First, create a PHP file with standar name, like index.php, then write the needed variables:

    $hostname = "localhost";
    $port     = "3306";

    $username = "root";
    $password = "";

Add $database variable:

    $database = "testing";

Then, we will create a database "testing":

    CREATE DATABASE `testing`;

Create table "users":

    CREATE TABLE `users` (
      `id` int(11) NOT NULL,
      `firstname` varchar(50) NOT NULL,
      `lastname` varchar(50) NOT NULL,
      `description` varchar(250) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Add primary key:

    ALTER TABLE `users`
      ADD PRIMARY KEY (`id`);

Add auto increment:

    ALTER TABLE `users`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
    COMMIT;

---

1. MySQLi¹ procedural

Back to Home

Create connection using mysqli::__construct², add $database:

    $connection = mysqli_connect( $hostname, $username, $password, $database );

Check connection, if there is errors, call mysqli_connect_error()³ function inside exit()⁴ function and use var_dump()⁵ function with argument of the $connection for get the result:

    if ( ! $connection ) 
    {

        exit( "Connection failed: " . mysqli_connect_error() );

    }
    else
    {

        echo "Connnected succesfully to server";

        echo "<pre>";
        var_dump($connection);
        echo "</pre>";

    }

Close connection using mysqli::close⁶:

    mysqli_close($connection);

Source Code:

• repl.it/antelove19/MySQLi/procedural/connection/database ___

2. MySQLi¹ object-oriented

Back to Home

Create connection and add $database:

    $connection = new mysqli( $hostname, $username, $password, $database );

Check connection:

    if ( $connection->connect_error ) 
    {

        exit( "Connection failed: {$connection->connect_error}" );

    }
    else
    {

        echo "Connnected succesfully to server";

        echo "<pre>";
        var_dump($connection);
        echo "</pre>";

    }

Close connection:

    $connection->close();

Source Code:

• repl.it/antelove19/MySQLi/object-oriented/connection/database ___

3. PDO⁷

Back to Home

Especially for PDO, we will use try catch

    try
    {

Set dsn and attribute [option], add $database:

        $dsn = "mysql:host=$hostname;port=$port;dbname=$database";

        $setAttribute = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
        );

Create connection using PDO::__construct⁸:

        $connection = new PDO( $dsn, $username, $password, $setAttribute );

Check connection:

        if ( $connection ) 
        {

            echo "Connnected succesfully to server";

            echo "<pre>";
            var_dump($connection);
            echo "</pre>";

        }

    }

Get and print error message, if exist:

    catch ( PDOException $err )
    {

        echo "Connection failed: " . $err->getMessage();

    }

Close connection with set $connection equal to null:

    $connection = null;

Source Code:

• repl.it/antelove19/PDO/connection/database ___

Back to Home | Next#

---

Thank for reading :)

---

---

1. php.net, "MySQL Improved Extension", accessed on date 21 december 2019 and from https://www.php.net/manual/en/book.mysqli.php ↩

2. php.net, "mysqli::__construct", accessed on date 21 december 2019 and from https://www.php.net/manual/en/mysqli.construct.php ↩

3. php.net, "mysqli_connect_error", accessed on date 21 december 2019 and from https://www.php.net/manual/en/mysqli.connect-error.php ↩

4. php.net, "exit", accessed on date 21 december 2019 and from https://www.php.net/manual/en/function.exit.php ↩

5. php.net, "var_dump", accessed on date 21 december 2019 and from https://www.php.net/manual/en/function.var-dump.php ↩

6. php.net, "mysqli::close", accessed on date 21 december 2019 and from https://www.php.net/manual/en/mysqli.close.php ↩

7. php.net, "PHP Data Objects", accessed on date 21 december 2019 and from https://www.php.net/manual/en/book.pdo.php  ↩

8. php.net, "PDO::__construct", accessed on date 21 december 2019 and from https://www.php.net/manual/en/pdo.construct.php  ↩