DEV Community

Falah Al Fitri
Falah Al Fitri

Posted on

PHP and MySQL 11: connection to database

#php #mysql #mysqli #pdo
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)