How to connect MySQL database using PHP

  •  
  •  
  •  
  •  
  •  
  •  

PHP is a well-known most popular scripting language, by which we can build website. But, to create a dynamic website one most important thing we required is database such as MySQL. Database is required to store data and we will fetch these data when we need them. But, before storing or retrieving data into/from database we need to connect MySQL database using PHP script. So, in this tutorial I will show you how to connect MySQL database using PHP.

How to connect MySQL database using PHP

Objectives –

  1. Make a connection with the MySQL database.
  2. Perform a simple query and fetch data from a table.
  3. Print the data into a tabular form.
  4. Close the connection with the database.

 

Make a connection with the MySQL –

If the MySQL database is in the same server as the local server then the MySQL server address will be localhost or 127.0.0.1. Apart from MySQL server address we will need database username, password and a database name. If, in your system, WampServer is already installed, then by default MySQL database is also installed in your system and you can access it through a browser by the url http://localhost/phpmyadmin. By default, database username is root and password is blank means nothing is set.

Now create a file called db.php or whatever you want to name it and write the following contents in it.

<?php
//Change data according to your
define('HOSTNAME','localhost');
define('DB_USERNAME','root');
define('DB_PASSWORD','');
define('DB_NAME', 'your_database_name');

//Returns a resource of the connetion
$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME);
echo "Database conection established!";

// If any error occurred, print the actual error message
if(mysqli_connect_errno($con)) echo "The reason of error: " .mysqli_connect_error();
?>

The mysqli_connect() function connects to the specified database and returns a resource which will stored in the $con variable only after a successful connection. This $con variable will be used in future. If there is any error or failed during connection an error message will be displayed in the page with proper error information. Otherwise it will display “Database connection established”.

 

Perform a simple query, fetch data from a table and print the data as a tabular form –

If all the things are going well, then the next step is to perform a simple query which will fetch all data from a table.

<?php
$query = "select * from test order by first_name";
$result = mysqli_query($con, $query);

if(mysqli_num_rows($result) > 0) {
 ?>
 <table border="1">
 <?php
 while($row = mysqli_fetch_object($result)) {
 ?>
   <tr>
      <td><?php echo $row->first_name; ?></td>
      <td><?php echo $row->last_name; ?></td>
      <td><?php echo $row->address; ?></td>
   </tr>
 <?php
 }
 ?>
 </table>
 <?php
} 
?>

The above query will returned all the rows of the table “test” in ascending order of the column “first_name”. The function mysqli_query() returns a resource that contains the results of the query, called the result set. Before display data we will first check, has mysqli_query() returned any row(s) or not from the table and we will check this by the use of php function mysqli_num_rows(). This function returns the number of row returned or zero if nothing. If mysqli_num_rows() returned value greater than zero then only subsequent while loop will run otherwise not. The mysql_fetch_object() function returns the results row by row. A simple and convenient way to access all the rows are with a while loop.

 

Close the connection with the database —

At last you should close the opened database connection. If you do not explicitly close the connection, PHP will implicitly close the connection but it is best practice to close it explicitly.

 

Create the “test” table into the database and insert some data —

To create “test” table in your database and to insert some data into it you should run the following scripts:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `address` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;



INSERT INTO `test` (`first_name`, `last_name`, `address`) VALUES
('Mitrajit', 'Samanta', 'Kharagpur'),
('Sachin', 'Tendulkar', 'Mumbai'),
('Sourav', 'Ganguly', 'Kolkata');

Here is the full code —

<?php
define('HOSTNAME','localhost');
define('DB_USERNAME','root');
define('DB_PASSWORD','');
define('DB_NAME', 'your_database_name');

//Returns a resource of the connetion
$con = mysqli_connect(HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME);
echo "Database conection established!";

// If any error occurred, print the actual error message
if(mysqli_connect_errno($con)) echo "The reason of error: " .mysqli_connect_error();
?>




<?php
$query = "select * from test order by first_name";
$result = mysqli_query($con, $query);
if(mysqli_num_rows($result) > 0) {
  ?>
  <table border="1">
  <?php
  while($row = mysqli_fetch_object($result)) {
  ?>
    <tr>
      <td><?php echo $row->first_name; ?></td>
      <td><?php echo $row->last_name; ?></td>
      <td><?php echo $row->address; ?></td>
    </tr>
  <?php
  }
  ?>
  </table>
  <?php
} 
?>

<?php mysqli_close($con);  ?>

If you enjoy this article and think it is useful then please share this article with others.

About Mitrajit

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove you are a human *