How to fetch data from MySQL database in Php

In this tutorial, I will show you how to extract data from multiple tables using PHP.
Let us start this by First creating a database “tutorialdb”,
Then add tables to the database. I am using MySQL to create two tables “categoriestbl” & “producttbl”. After that, I will be using PHP to issue a SQL Join command to get data from both tables at once.

Overview

To continue in this tutorial we will be needing files mentioned below

  • Index.php

Database Schema

CREATE TABLE IF NOT EXISTS `categoriestbl` (
`categry_id` int(5) NOT NULL AUTO_INCREMENT,
`categry_name` varchar(40) NOT NULL,
PRIMARY KEY (`categry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;–
— Dumping data for table `categoriestbl`
–INSERT INTO `categoriestbl` (`categry_id`, `categry_name`) VALUES
(1, ‘levis’),
(2, ‘bluefly’),
(3, ‘KnowladgeCotton’);– ———————————————————-
— Table structure for table `producttbl`
–CREATE TABLE IF NOT EXISTS `producttbl` (
`prod_id` int(5) NOT NULL AUTO_INCREMENT,
`prod_name` varchar(20) NOT NULL,
`categry_id` int(5) NOT NULL,
PRIMARY KEY (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;–
— Dumping data for table `producttbl`
–INSERT INTO `producttbl` (`prod_id`, `prod_name`, `categry_id`) VALUES
(11, ‘denims’, 1),
(12, ‘skirts’, 2),
(20, ‘cotton wear’, 3);

Index.php

This file contains the style sheet as well as the server-side that needs to get data from the database. First, it will connect to the database using Mysqli connect query with the configurations specified while creating the mysqli object.

Then, it issues a SQL query which looks for category id of the both tables and gets the data matching to category id of the fields. Those data are taken into an array and printed inside a table.

Codes:

<html>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Tutorial-16</title>
<style>
table,td,th
{
padding:10px;
border-collapse:collapse;
font-family:Georgia, “Times New Roman”, Times, serif;
border:solid #ddd 2px;
}
</style>
</head>
<body>
<table align=”center” border=”1″ width=”100%”>
<tr>
<th>product id</th>
<th>product name</th>
<th>category name</th>
</tr>
<?php
$mysqli = new mysqli(“localhost”, “root”, “”, “tutorialdb”);
# check connection

if ($mysqli->connect_errno) {
echo “<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>”;
exit();
}

$response=$mysqli->query(“SELECT c.* , p.* FROM categoriestbl c,producttbl p WHERE c.categry_id=p.categry_id”);
while($row=$response->fetch_array(MYSQLI_ASSOC))
{
?>
<tr>
<td><p><?php echo $row[‘prod_id’]; ?></p></td>
<td><p><?php echo $row[‘prod_name’]; ?></p></td>
<td><p><?php echo $row[‘categry_name’]; ?></p></td>
</tr>
<?php
}
?>
</table>
</body>
</html>

Bonus:

You can download the complete working demo source code here.

I hope the tutorial helped you learn to use SQL commands to join multiple tables in PHP.
I would love to solve your problem if any so don’t forget to comment. Also, appreciate if you shared the post with your friends.

Download the complete source code.

Grab the the free working demo Intantly!

You have Successfully Suscribed!

Leave a Reply

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