I wrote this code to show how to use an inner join to fetch records from two tables in the database using PHP and MySQL. The code is very easy and easy to understand for beginners in PHP and MySQL programming.
I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me in the following email address for further details. If you want to advertise on my website kindly contact me also in my email address also. Thank you.
My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
My telephone number at home here in Bacolod City, Negros Occidental Philippines is +63 (034) 4335675.
Here in Bacolod City, Negros Occidental I also accepting computer repair, web development using WordPress, Computer Networking and Arduino Project development at a very affordable price.
My personal website is http://www.jakerpomperada.com
My programming website is http://www.jakerpomperada.blogspot.com
I am also a book author you can purchase my books on computer programming and information technology in the following links below.
https://www.mindshaperspublishing.com/
https://www.unlimitedbooksph.com/
Sample Program Output
Program Listing
inner_join.sql
SELECT tbl_info.employee_id,tbl_info.name,tbl_info.position,tbl_contacts.telephone,tbl_contacts.mobile,tbl_contacts.email FROM tbl_info INNER JOIN tbl_contacts ON tbl_info.employee_id = tbl_contacts.employee_id
display.php
<html>
<title> Display Records </title>
<body>
<style type="text/css">
body {
font-family: arial;
size: 12px
}
</style>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'employees';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);
if(! $conn ) {
die('Could not connect: ' . mysqli_error());
}
echo 'Connected successfully</br>';
$sql = 'SELECT tbl_info.employee_id,tbl_info.name,tbl_info.position,tbl_contacts.telephone,tbl_contacts.mobile,tbl_contacts.email FROM tbl_info INNER JOIN tbl_contacts ON tbl_info.employee_id = tbl_contacts.employee_id ';
if($result = mysqli_query($conn, $sql)) {
if(mysqli_num_rows($result) > 0) {
echo "<table>";
echo "<tr>";
echo "<th>EMPLOYEE ID</th>";
echo "<th>NAME</th>";
echo "<th> JOB POSITION</th>";
echo "<th>TELEPHONE</th>";
echo "<th>MOBILE</th>";
echo "<th>EMAIL</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['employee_id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td> " . $row['position'] . "</td>";
echo "<td>" . $row['telephone'] . "</td>";
echo "<td>" . $row['mobile'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_free_result($result);
} else {
echo "No records matching your query were found.";
}
} else {
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
mysqli_close($conn);
?>
</body>
</html>
employees.sql
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.5-10.1.38-MariaDB : Database - employees
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`employees` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `employees`;
/*Table structure for table `tbl_contacts` */
DROP TABLE IF EXISTS `tbl_contacts`;
CREATE TABLE `tbl_contacts` (
`id_contact` int(15) NOT NULL AUTO_INCREMENT,
`employee_id` varchar(50) DEFAULT NULL,
`telephone` varchar(100) DEFAULT NULL,
`mobile` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id_contact`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_contacts` */
insert into `tbl_contacts`(`id_contact`,`employee_id`,`telephone`,`mobile`,`email`) values (1,'ABC1878','4335081','09173084360','jakerpomperada@gmail.com'),(2,'ABC1878','4335081','09173084360','jakerpomperada@gmail.com'),(3,'XYZ4545','546565656','56565656565','larry_emol@gmail.com');
/*Table structure for table `tbl_info` */
DROP TABLE IF EXISTS `tbl_info`;
CREATE TABLE `tbl_info` (
`id_info` int(15) NOT NULL AUTO_INCREMENT,
`employee_id` varchar(50) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id_info`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_info` */
insert into `tbl_info`(`id_info`,`employee_id`,`name`,`position`) values (3,'ABC1878','JAKE','PROGRAMMER'),(4,'ABC1878','JAKE POMPERADA','WEB DEVELOPER'),(5,'XYZ4545','LARRY EMOL','VISUAL BASIC PROGRAMMER');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
No comments:
Post a Comment