Categories
mysql PHP

Membuat MySQLi Join Table Sederhana

mysqli join table

Kembali lagi dengan kami, pada kesempatan kali ini kami akan share bagaimana membuat Mysql Join table. Join tabel digunakan dalam sebuah system untuk menggabungkan dua data table yang ada pada database.

Langsung saja mari kita masuk kedalam pembahasan intinya.

1. Buat database dengan nama db_join kemudian masukkan struktur database dibawah ini.

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 25, 2019 at 05:23 PM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `db_join`
--

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `stud_id` int(11) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `teach_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`stud_id`, `firstname`, `lastname`, `teach_id`) VALUES
(1, 'Arman', 'Basir', 1),
(2, 'Irwan', 'Basullung', 2),
(3, 'Dede', 'Ladores', 1),
(4, 'Abi', 'Manruccu', 1);

-- --------------------------------------------------------

--
-- Table structure for table `teacher`
--

CREATE TABLE `teacher` (
  `teach_id` int(11) NOT NULL,
  `teacher_firstname` varchar(50) NOT NULL,
  `teacher_lastname` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `teacher`
--

INSERT INTO `teacher` (`teach_id`, `teacher_firstname`, `teacher_lastname`) VALUES
(1, 'Man Pak', 'Ilman Pak'),
(2, 'Rohani', 'Muktar');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `student`
--
ALTER TABLE `student`
  ADD PRIMARY KEY (`stud_id`);

--
-- Indexes for table `teacher`
--
ALTER TABLE `teacher`
  ADD PRIMARY KEY (`teach_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
  MODIFY `stud_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `teacher`
--
ALTER TABLE `teacher`
  MODIFY `teach_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

2. Buat file dengan nama conn.php ini berfungsi sebagai koneksi database.

<?php
	$conn=mysqli_connect("localhost", "root", "", "db_join");

	if(!$conn){
		die(mysqli_error());
	}
?>

3. Buat file dengan nama index.php file ini berfungsi sebagai form utama.

<?php require'conn.php'?>
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta charset="UTF-8" name="viewport" content="width=device-width, initial-scale=1"/>
		<link rel="stylesheet" type="text/css" href="css/bootstrap.css"/>
	</head>
<body>
	<nav class="navbar navbar-default">
		<div class="container-fluid">
			<a class="navbar-brand" href="https://jasawebsite.ooo">jasawebsite</a>
		</div>
	</nav>
	<div class="col-md-3"></div>
	<div class="col-md-6 well">
		<h3 class="text-primary">PHP - MySQL Join</h3>
		<hr style="border-top:1px dotted #ccc;"/>
		<div class="col-md-6">
			<center><h4>STUDENT</h4></center>
			<table class="table table-bordered">
				<thead>
					<tr>
						<th>Firstname</th>
						<th>Lastname</th>
					</tr>
				</thead>
				<tbody>
					<?php
						$query=mysqli_query($conn, "SELECT * FROM `student`") or die(mysqli_error());
						while($fetch=mysqli_fetch_array($query)){
					?>
					<tr>
						<td><?php echo $fetch['firstname']?></td>
						<td><?php echo $fetch['lastname']?></td>
					</tr>
					<?php
						}
					?>
				</tbody>
			</table>
		</div>
		<div class="col-md-6">
			<center><h4>TEACHER</h4></center>
			<table class="table table-bordered">
				<thead>
					<tr>
						<th>Firstname</th>
						<th>Lastname</th>
					</tr>
				</thead>
				<tbody>
					<?php
						$query=mysqli_query($conn, "SELECT * FROM `teacher`") or die(mysqli_error());
						while($fetch=mysqli_fetch_array($query)){
					?>
					<tr>
						<td><?php echo $fetch['teacher_firstname']?></td>
						<td><?php echo $fetch['teacher_lastname']?></td>
					</tr>
					<?php
						}
					?>
				</tbody>
			</table>
		</div>
		<br style="clear:both;"/>
		<div class="col-md-2"></div>
		<div class="col-md-8">
			<form method="POST" action="">
				<center><button class="btn btn-primary" name="join">Join Table</button></center>
			</form>
			<br />
			<?php include'join.php'?>
		</div>
	</div>
</body>	
</html>

4. Terakhir buat file dengan nama join.php file ini berisi fungsi untuk menggabungkan (join) table.

<?php
	if(ISSET($_POST['join'])){
?>
<table class="table table-bordered">
	<thead>
		<tr>
			<th>Firstname</th>
			<th>Lastname</th>
			<th>Teacher</th>
		</tr>
	</thead>
	<tbody>
		<?php
			$query=mysqli_query($conn, "SELECT * FROM `student` LEFT JOIN `teacher` ON student.teach_id = teacher.teach_id") or die(mysqli_error());
			while($fetch=mysqli_fetch_array($query)){
		?>
		<tr>
			<td><?php echo $fetch['firstname']?></td>
			<td><?php echo $fetch['lastname']?></td>
			<td><?php echo $fetch['teacher_firstname']." ".$fetch['teacher_lastname']?></td>
		</tr>
		<?php
			}
		?>
	</tbody>
</table>
<?php
	}
?>

Kalian bisa mendownload filenya langsung pada link button dibawah ini.

Sekian share kali ini, silahkan tinggalkan komentar dan mulai berdiskusi dengan kami.

Leave a Reply

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