DEV Community

Falah Al Fitri
Falah Al Fitri

Posted on

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 = "";
Enter fullscreen mode Exit fullscreen mode

Add $database variable:

    $database = "testing";
Enter fullscreen mode Exit fullscreen mode

Then, we will create a database "testing":

    CREATE DATABASE `testing`;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Add primary key:

    ALTER TABLE `users`
      ADD PRIMARY KEY (`id`);
Enter fullscreen mode Exit fullscreen mode

Add auto increment:

    ALTER TABLE `users`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
    COMMIT;
Enter fullscreen mode Exit fullscreen mode

1. MySQLi1 procedural

Back to Home

Create connection using mysqli::__construct2, add $database:

    $connection = mysqli_connect( $hostname, $username, $password, $database );
Enter fullscreen mode Exit fullscreen mode

Check connection, if there is errors, call mysqli_connect_error()3 function inside exit()4 function and use var_dump()5 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>";

    }
Enter fullscreen mode Exit fullscreen mode

Close connection using mysqli::close6:

    mysqli_close($connection);
Enter fullscreen mode Exit fullscreen mode

Source Code:

2. MySQLi1 object-oriented

Back to Home

Create connection and add $database:

    $connection = new mysqli( $hostname, $username, $password, $database );
Enter fullscreen mode Exit fullscreen mode

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>";

    }
Enter fullscreen mode Exit fullscreen mode

Close connection:

    $connection->close();
Enter fullscreen mode Exit fullscreen mode

Source Code:

3. PDO7

Back to Home

Especially for PDO, we will use try catch

    try
    {
Enter fullscreen mode Exit fullscreen mode

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
        );
Enter fullscreen mode Exit fullscreen mode

Create connection using PDO::__construct8:

        $connection = new PDO( $dsn, $username, $password, $setAttribute );
Enter fullscreen mode Exit fullscreen mode

Check connection:

        if ( $connection ) 
        {

            echo "Connnected succesfully to server";

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

        }

    }
Enter fullscreen mode Exit fullscreen mode

Get and print error message, if exist:

    catch ( PDOException $err )
    {

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

    }
Enter fullscreen mode Exit fullscreen mode

Close connection with set $connection equal to null:

    $connection = null;
Enter fullscreen mode Exit fullscreen mode

Source Code:

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  

Top comments (0)