Connections

MySQL - Connection

MySQL Connection Using PHP Script

PHP provides mysqli_connect() function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success or FALSE on failure.

Syntax

connection mysqli_connect(server,user,passwd,new_link,client_flag);

No. Parameter & Description
1

server

Optional − The host name running the database server. If not specified, then the default value will be remotemysql.com:3306.
2

user

Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process.
3

passwd

Optional − The password of the user accessing the database. If not specified, then the default will be an empty password.
4

new_link

Optional − If a second call is made to mysqli_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.
5

client_flags

Optional − A combination of the following constants −

  • mysqli_CLIENT_SSL − Use SSL encryption.

  • mysqli_CLIENT_COMPRESS − Use compression protocol.

  • mysqli_CLIENT_IGNORE_SPACE − Allow space after function names.

  • mysqli_CLIENT_INTERACTIVE − Allow interactive timeout seconds of inactivity before closing the connection.

You can disconnect from the MySQL database anytime using another PHP function mysqli_close(). This function takes a single parameter, which is a connection returned by the mysqli_connect() function.

Syntax

bool mysqli_close ( resource $link_identifier );

If a resource is not specified, then the last opened database is closed. This function returns true if it closes the connection successfully otherwise it returns false.

Example

Try the following example to connect to a MySQL server −

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = 'remotemysql.com:3306';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully';
         mysqli_close($conn);
      ?>
   </body>
</html>

MySQL - Create Database

Create a Database using PHP Script

PHP uses mysqli_query function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

bool mysqli_query( sql, connection );

Sr.No. Parameter & Description
1

sql

Required - SQL query to create or delete a MySQL database
2

connection

Optional - if not specified, then the last opened connection by mysqli_connect will be used.

Example

The following example to create a database −

<html>
   <head>
      <title>Creating MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'remotemysql.com:3036';
         $dbuser = 'your_username;
         $dbpass = 'your_password';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'CREATE DATABASE TUTORIALS';
         $retval = mysqli_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create database: ' . mysqli_error());
         }
         echo "Database TUTORIALS created successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>

Drop MySQL Database

Drop Database using PHP Script

PHP uses mysqli_query function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

bool mysqli_query( sql, connection );

No Parameter & Description
1

sql

Required − SQL query to create or delete a MySQL database
2

connection

Optional − if not specified, then the last opened connection by mysqli_connect will be used.

Example

Try the following example to delete a database −

<html>
   <head>
      <title>Deleting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'remotemysql.com:3036';
         $dbuser = 'your_username;
         $dbpass = 'your_password';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'DROP DATABASE TUTORIALS';
         $retval = mysqli_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete database: ' . mysqli_error());
         }
         echo "Database TUTORIALS deleted successfully\n";
         mysqli_close($conn);
      ?>
   </body>
</html>

WARNING − While deleting a database using the PHP script, it does not prompt you for any confirmation. So be careful while deleting a MySQL database.

Selecting MySQL Database

Selecting a MySQL Database Using PHP Script

PHP provides function mysqli_select_db to select a database. It returns TRUE on success or FALSE on failure.

Syntax

bool mysqli_select_db( db_name, connection );

Sr.No. Parameter & Description
1

db_name

Required − MySQL Database name to be selected
2

connection

Optional − if not specified, then the last opened connection by mysqli_connect will be used.

Example

Here is an example showing you how to select a database.

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'remotemysql.com:3036';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysqli_error());
         }
         echo 'Connected successfully';
         mysqli_select_db( 'TUTORIALS' );
         
         mysqli_close($conn);
      ?>
   </body>
</html>