loading...

PHP and MySQL 11: connection to database

antelove19 profile image Falah Al Fitri ・3 min read

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. MySQLi1 procedural

Back to Home

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

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

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

    }

Close connection using mysqli::close6:

    mysqli_close($connection);

Source Code:

2. MySQLi1 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:

3. PDO7

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::__construct8:

        $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:

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  

Posted on by:

Discussion

markdown guide