PHP and MySQL 11: connection to database
Tags: php, mysql, mysqli, pdo
Published: 2019-12-22 04:54:51

___

<center>Happy Coding</center>
___

## `Home` <a name="home"> [`Previous`](https://dev.to/antelove19/php-and-mysql-10-connection-112n)

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](#procedural) 
  2. [MySQLi object-oriented](#object)
  3. [PDO](#pdo)

First, create a PHP file with standar name, like **_index.php_**, then write the needed variables:

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

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

Add **`$database`** variable:

```php
    $database = "testing";
```

Then, we will create a database "testing":
```php
    CREATE DATABASE `testing`;
```

Create table "users":
```php
    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:
```php
    ALTER TABLE `users`
      ADD PRIMARY KEY (`id`);
```

Add auto increment:
```php
    ALTER TABLE `users`
      MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
    COMMIT;
```
___


### 1. MySQLi[^1] procedural <a name="procedural"></a>

[`Back to Home`](#home)

Create connection using **mysqli::__construct**[^2], add **`$database`**:

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

Check connection, if there is errors, call **mysqli_connect_error()**[^4] function inside **exit()**[^3] function and use **var_dump()**[^5] function with argument of the `$connection` for get the result:

```php
    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**[^6]:

```php
    mysqli_close($connection);
```

Source Code:

* [repl.it/antelove19/MySQLi/procedural/connection/database](https://repl.it/@antelove19/MySQLi-procedural-11-connection-database)
___


### 2. MySQLi[^1] object-oriented <a name="object"></a>

[`Back to Home`](#home)

Create connection and add **`$database`**:

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

Check connection:

```php
    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:

```php
    $connection->close();
```

Source Code:

* [repl.it/antelove19/MySQLi/object-oriented/connection/database](https://repl.it/@antelove19/MySQLi-object-oriented-11-connection-database)
___


### 3. PDO[^7] <a name="pdo"></a>

[`Back to Home`](#home)

Especially for PDO, we will use `try catch`

```php
    try
    {
```

Set dsn and attribute [option],  add **`$database`**:
```php
        $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**[^8]:
```php
        $connection = new PDO( $dsn, $username, $password, $setAttribute );
```

Check connection:
```php
        if ( $connection ) 
        {

            echo "Connnected succesfully to server";

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

        }

    }
```

Get and print error message, if exist:

```php
    catch ( PDOException $err )
    {

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

    }
```

Close connection with set `$connection` equal to null:

```php
    $connection = null;
```

Source Code:

* [repl.it/antelove19/PDO/connection/database](https://repl.it/@antelove19/PDO-11-connection-database)
___


## [`Back to Home`](#home) | [`Next#`](#)


___

<center>Thank for reading :)</center>
___


[^1]: **php.net**, "MySQL Improved Extension", accessed on date 21 december 2019 and from [_https://www.php.net/manual/en/book.mysqli.php_] (https://www.php.net/manual/en/mysqli.construct.php)

[^2]: **php.net**, "mysqli::__construct", accessed on date 21 december 2019 and from [_https://www.php.net/manual/en/mysqli.construct.php_] (https://www.php.net/manual/en/mysqli.construct.php)

[^3]: **php.net**, "exit", accessed on date 21 december 2019 and from [_https://www.php.net/manual/en/function.exit.php_] (https://www.php.net/manual/en/function.exit.php)

[^4]: **php.net**, "mysqli_connect_error", accessed on date 21 december 2019 and from [_https://www.php.net/manual/en/mysqli.connect-error.php_] (https://www.php.net/manual/en/mysqli.connect-error.php)

[^5]: **php.net**, "var_dump", accessed on date 21 december 2019 and from [_https://www.php.net/manual/en/function.var-dump.php_] (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_] (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_] (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_] (https://www.php.net/manual/en/pdo.construct.php)