Connect PHP to MYSQL and fetch data

We learned PHP basics and PHPMYADMIN tool to use MYSQL in previous chapters. We also learned how SQL statements are formed. Now it’s time to mix both of them. So in this Connect PHP to MYSQL and fetch data tutorial we are going to create a PHP file that connects the company database created in last tutorial and fetch information from it and pass information into it.

Follow the steps given below.

1. 1. First open your browser and type localhost\phpmyadmin in the address bar. Login and see if the company database and its tables created last time exist. Remain logged in because you will need it further.
   2. Create a new folder named Connect in the htdocs folder which is in the xampp folder located in C drive.
   3. Open a new notepad++ document and save it as index.php in the newly created Connect folder which is in the htdocs folder.
   4. Write the following code in index.php file:

<?php
 //create connection
 $connect=mysqli_connect('localhost','root','12345','company');
	
//check connection
 if(mysqli_connect_errno($connect))
 {
	echo 'Failed to connect to database: '.mysqli_connect_error();
}
else
	echo 'Connected Successfully!!';

1. 1. • In the above code we are connecting the .php page to the company database.
      • To create a connection mysqli_connect() method is used.
      • Parameters passed to the mysqli_connect() functions are the hostname, phpmyadmin username, phpmyadmin password and the database name.
      • This connection string is stored in a variable named $connect.
      • Next we first have to check whether we are connected to database successfully or not.
      • For that we use if—else conditional statement.
      • The function mysqli_connect_errno($connect) checks if there is any error in the connection string. If there is any error, it will return true otherwise false.
      • If there is any error, it will be displayed by the echo statement in if block otherwise Connected Successfully!! Message will be displayed.
      • The function mysqli_connect_error() function is used to display system error message.
      • Just open the browser and type localhost/connect in its address bar.
      • You will get the following output:

fig 1

1. 1. • Now just make some mistake, for example write a wrong password and now see the output:

fig 2

1. 1. • You will get an error message saying Access denied for user ‘root’@’localhost’
      • So now correct the password and try the queries.
   2. Now once you are connected successfully, comment the following statements in the code:

else
		echo 'Connected Successfully';

1. 1. Write the following query to insert a record in employees table of company database:

mysqli_query($connect,"insert into employees (first_name,last_name,department,email) values('Jeff','Dole','Programming','[email protected]')");

1. 1. • Here, mysqli_query() method is used to provide the query statement.
      • In this method, the first parameter is the connection variable and the second parameter is the insert query.
      • When you reload the page you will see nothing, but an entry of the record must have been done in the employees table.
      • So just open and see the employees table, you will get the new record there.
      • The output is shown below:

fig 3

1. 1. • If you reload the page again, the record will be inserted one more time. I mean the record will be inserted the number of times you reload the page.
   2. As we have inserted data in the table through .php file, we can grab and display it using a select query.
      • Write the following code in index.php file.

$result=mysqli_query($connect,"select * from employees");

while($row=mysqli_fetch_array($result))
{
         echo $row['first_name'].' '.$row['last_name'].'<br/>';
 }

1. 1. • In the above code, mysqli_query() method is used to provide query.
      • The first parameter is the variable containing connection string and second is the query.
      • All the results are fetched and stored in the variable $result.
      • While loop is used to display the retrieved data.
      • The first name and last name are displaced. The output of the above code is as follows:

fig 4

1. 1. We learned to fetch the data, now let us learn how to present it neatly in a table format. We will design an HTML table.
      Follow the steps:
      • Comment the while loop shown below:

while($row=mysqli_fetch_array($result))
{
         echo $row['first_name'].' '.$row['last_name'].'<br/>';
 }

1. 1. • Now write the following code outside the PHP tag. Because browser could not recognize html code inside php tag:

<h1>Employees</h1>
	<table width="500" cellpadding=5celspacing=5 border=1>
	<tr>
	<th>ID#</th>
	<th>First Name</th>
	<th>Last Name</th>
	<th>Department</th>
	<th>Email</th>
	</tr>
	<?php while($row=mysqli_fetch_array($result)):?>
	<tr>
	<td><?php echo $row['id'];?></td>
	<td><?php echo $row['first_name'];?></td>
	<td><?php echo $row['last_name'];?></td>
	<td><?php echo $row['department'];?></td>
	<td><?php echo $row['email'];?></td>
	</tr>
	<?php endwhile;?> 
	</table>

1. 1. • Here, we want to display the employees information in a table format.
      • Hence we have just created a table in HTML.
      • To print all the rows of employees table while loop is used. It is enclosed in php tag since it is a php code.
      • You can see a colon (:) instead of semicolon in the statement

        <?php while($row=mysqli_fetch_array($result)):?>

        after the closing parenthesis of while statement, this indicate that the while loop is not completed; it includes more statements inside it. It ends at the statement

        <?php endwhile;?>

        .

      • Inside the while loop there is a HTML row that displays the data of emolyees table.
      • Since echo statement is a php code it is enclosed in php tag.
      • The output is shown below:

fig 5

1. 1. • If another record is added later in the employees table, it will also be displayed in the table format shown above.

This is how web applications are built. Customer relation programs and many different programs are built using this technology.

1. 1. Now let us get data from the 2 tables, products and categories and display it in the table format.
      • Write the following code for it:

  <h1>Products</h1>
	<?php $result=mysqli_query($connect,"select products.name,categories.name as 'category',products.id as prod_id from products left join categories on products.category=categories.id");?>
	<table width="500" cellpadding=5celspacing=5 border=1>
	<tr>
	<th>ID#</th>
	<th>Product</th>
	<th>Category</th>
	</tr>
	<?php while($row=mysqli_fetch_array($result)):?>
	<tr>
	<td><?php echo $row['prod_id'];?></td>
	<td><?php echo $row['name'];?></td>
	<td><?php echo $row['category'];?></td>
	</tr>
	<?php endwhile;?> 
	</table>

1. 1. • Here we have a heading Products and a select query is written after that.
      • Let us understand the query.

<?php $result=mysqli_query($connect,"select products.name,categories.name as 'category',products.id as prod_id from products left join categories on products.category=categories.id");?>

1. 1. • In the above query, left join is used for products and categories tables.
      • In the query, name and id from products table and name from categories table is selected.
      • The name from categories table is given a caption category and id from products table is named as prod_id. We have renamed id, because we have same column name id in employees, products as well as categories table, so it can conflict while displaying.
      • The match is done on category of products table and id of categories table.
      • Next the values from the table are displayed using the while loop as explained in the example of displaying employees table values.
      • The output is shown below:

fig 6

1. • In this way we connect to the database, insert data, fetch data and can do all sorts of database operation using MYSQL and PHP.

Thus we finished learning how to connect to MYSQL database using PHP and work with it in this Connect PHP to MYSQL and fetch data tutorial.

About the Author

Tutor @ Eduonix

Administrator

View All Posts