In this post i am explain about to display the two mysql table data in single table using mysql inner join query . The two tables are personalinfo, salaryinfo in that two tables i am using common id is called as a empid using this i am selected two different table datas in single view.
I am displaying the data name, contactnumber from personallinfo table and then esi, pf, salary from salaryinfo table and then i am displaying in a single table using empid as a common id.
CREATE TABLE `emp`.`personalinfo` (
`empid` int(6) not null,
`name` VARCHAR( 150 ) NOT NULL ,
`contactnumber` INT( 200 ) NOT NULL ,
`mail` VARCHAR( 200 ) NOT NULL
) ENGINE = MYISAM ;
`empid` int(6) not null,
`salary` varchar(5) not null,
`esi` varchar(4) not null,
`pf` varchar(4) not null
) ENGINE = MYISM ;
<head>
<title>Display more then one table data in single table using joinquery</title>
</head>
<body bgcolor="#85B80E">
<?php
$con=mysqli_connect("localhost","root","","emp");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT personalinfo.name, personalinfo.contactnumber, salaryinfo.esi, salaryinfo.pf, salaryinfo.salary FROM personalinfo INNER JOIN salaryinfo ON personalinfo.empid=salaryinfo.empid");
echo " <center><h1>JOIN QUERY </h1></center>";
echo "<table border='1' align='center' cellpadding='15' bgcolor='#FFFFFF'>
<tr>
<th>NAME</th>
<th>CONTACT NUMBER</th>
<th>ESI</th>
<th>PF</th>
<th>SALARY</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['contactnumber'] . "</td>";
echo "<td>" . $row['esi'] . "</td>";
echo "<td>" . $row['pf'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
<table bgcolor="#FFFFFF"
</body>
</html>
I am displaying the data name, contactnumber from personallinfo table and then esi, pf, salary from salaryinfo table and then i am displaying in a single table using empid as a common id.
Create Database:
create database emp;
Create Table: personalinfoCREATE TABLE `emp`.`personalinfo` (
`empid` int(6) not null,
`name` VARCHAR( 150 ) NOT NULL ,
`contactnumber` INT( 200 ) NOT NULL ,
`mail` VARCHAR( 200 ) NOT NULL
) ENGINE = MYISAM ;
Create Table: salaryinfo
CREATE TABLE `emp`.`salaryinfo` (`empid` int(6) not null,
`salary` varchar(5) not null,
`esi` varchar(4) not null,
`pf` varchar(4) not null
) ENGINE = MYISM ;
index.php
<html><head>
<title>Display more then one table data in single table using joinquery</title>
</head>
<body bgcolor="#85B80E">
<?php
$con=mysqli_connect("localhost","root","","emp");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT personalinfo.name, personalinfo.contactnumber, salaryinfo.esi, salaryinfo.pf, salaryinfo.salary FROM personalinfo INNER JOIN salaryinfo ON personalinfo.empid=salaryinfo.empid");
echo " <center><h1>JOIN QUERY </h1></center>";
echo "<table border='1' align='center' cellpadding='15' bgcolor='#FFFFFF'>
<tr>
<th>NAME</th>
<th>CONTACT NUMBER</th>
<th>ESI</th>
<th>PF</th>
<th>SALARY</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['contactnumber'] . "</td>";
echo "<td>" . $row['esi'] . "</td>";
echo "<td>" . $row['pf'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
<table bgcolor="#FFFFFF"
</body>
</html>
0 Comments