kode php

Gabung dalam di php mysql

GABUNG digunakan untuk meminta data dari dua atau lebih tabel. Gabungan dalam digunakan jika perlu mengembalikan baris ketika setidaknya ada satu kecocokan di kedua tabel. Ini adalah contoh yang baik untuk memahami klausa gabungan dalam karena dalam contoh ini kami menggunakan klausa gabungan antara hanya dua tabel. Kami menggunakan tabel "emp" dan tabel "message" dan menemukan nama dari tabel emp dan pesan dari tabel pesan di mana emp_id di tabel emp dan emp_id di tabel pesan sama. Hasil yang diambil ditampilkan di browser seperti di bawah ini. Kode sumber lengkap tersedia di bawah ini untuk diunduh atau salin kode sesuai kebutuhan Anda

Meja. emp

Meja. pesan

Kode Sumber dari innerJoin. php

<?php
  $con = mysql_connect("localhost","root","root");
  if (!$con) {
  die('Could not connect: ' . mysql_error());
  }

  mysql_select_db("test", $con);
  $result = mysql_query("SELECT emp.name, message.messages
   FROM emp INNER JOIN message ON emp.emp_id = message.emp_id");
  echo "<table border='1'>
  <tr>
  <th>Name</th>
  <th>Message</th>
  </tr>";
  while ($row = mysql_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['messages'] . "</td>";
  echo "</tr>";
  }
  echo "</table>";

  mysql_close($con);
?>

Keluaran

Unduh Kode Sumber

PHP adalah bahasa skrip sisi server, yang digunakan untuk terhubung dengan basis data. Dengan ini, kita bisa mendapatkan data dari database menggunakan skrip PHP. Bahasa database yang dapat digunakan untuk berkomunikasi dengan PHP adalah MySQL. MySQL adalah bahasa kueri basis data yang digunakan untuk mengelola basis data

Persyaratan

Xampp server – server xampp digunakan untuk menyimpan database kita secara lokal. Kita akan mengakses data dari server xampp menggunakan PHP

Pada artikel ini, kami mengambil database detail siswa yang berisi dua tabel. Mereka adalah student_address dan student_marks

Struktur tabel

table1=alamat_siswa

table2=student_marks

Kami akan melakukan INNER JOIN, LEFT JOIN, RIGHT JOIN pada dua tabel ini

1. GABUNG DALAM

INNER JOIN adalah kata kunci yang memilih rekaman yang memiliki nilai yang cocok di kedua tabel

Sintaksis

SELECT column 1,column 2,...column n FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Contoh

Biarkan alamat_pelajar berisi detail ini

Dan tabel student_marks termasuk

Dengan menggunakan sid, kita dapat menggabungkan kedua tabel ini dengan menggunakan Inner join, karena sid adalah umum dalam dua tabel

Permintaan untuk menampilkan detail alamat_pelajar berdasarkan gabungan dalam –

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_

Hasil

Query untuk menampilkan detail student_marks berdasarkan inner join

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid

Hasil

2. GABUNG KIRI

Kata kunci LEFT JOIN digunakan untuk mengembalikan semua record dari tabel kiri (table1), dan record yang cocok dari tabel kanan (table2)

Sintaksis

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Permintaan untuk menampilkan semua tabel student_address berdasarkan id siswa menggunakan gabung kiri

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid_

Hasil

Permintaan untuk menampilkan semua tabel student_marks berdasarkan id siswa menggunakan join kiri

SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid_

Hasil

3. GABUNG KANAN

Kata kunci RIGHT JOIN digunakan untuk mengembalikan semua record dari tabel kanan (table2), dan record yang cocok dari tabel kiri (table1).

Sintaksis

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_0

Query untuk menampilkan semua tabel student_address berdasarkan student id menggunakan right join

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_1

Hasil

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_2

Permintaan untuk menampilkan semua tabel student_marks berdasarkan id siswa menggunakan right join

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_3

Hasil

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_4

Mendekati

Buat database bernama database dan buat tabel (student_address dan student_marks)
Sisipkan catatan ke dalam dua tabel menggunakan PHP
Tulis kueri SQL untuk melakukan semua gabungan menggunakan PHP
Amati hasilnya

Langkah

Mulai server xampp

Ketik “localhost/phpmyadmin” di browser Anda dan buat database bernama “database” lalu buat dua tabel bernama student_address dan student_marks

struktur tabel Student_address

Struktur tabel Student_marks

Masukkan catatan ke dalam tabel student_address menggunakan PHP (data1. php) Jalankan kode dengan mengetikkan “localhost/data1. php”

PHP

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_5

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_6

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;7 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;9STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad2 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad4STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad7 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad9STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid1

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid2 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid4STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid6

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid_7

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid8 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 980 STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 981SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;7STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 983STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad2STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 983STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad7STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 983________3______2________4______99

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;0

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;5SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;6SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;2SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;8 SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;9SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid8SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid1

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid_2

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid_3

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;8SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid6STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid9STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad STUDENT-ID : ----- NAME : gnanesh ----- ADDRESS : hyderabad0STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid9STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad STUDENT-ID : ----- NAME : gnanesh ----- ADDRESS : hyderabad4STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid9STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad STUDENT-ID : ----- NAME : gnanesh ----- ADDRESS : hyderabad8STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid9SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid2STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid6

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;1 SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;2SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid8SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid8SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 790

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;5STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 792 STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 793STUDENT-ID : 1 ----- NAME : sravan kumar ----- ADDRESS : kakumanu STUDENT-ID : 2 ----- NAME : bobby ----- ADDRESS : kakumanu STUDENT-ID : 3 ----- NAME : ojaswi ----- ADDRESS : hyderabad STUDENT-ID : 4 ----- NAME : rohith ----- ADDRESS : hyderabad0

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid2STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 796 STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 797

SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;5STUDENT-ID : 1 ----- SUBJECT 1 : 98 ----- SUBJECT 2 : 99 STUDENT-ID : 2 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 89 STUDENT-ID : 3 ----- SUBJECT 1 : 78 ----- SUBJECT 2 : 98 STUDENT-ID : 4 ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 98 STUDENT-ID : ----- SUBJECT 1 : 89 ----- SUBJECT 2 : 792 SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;00 SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;9SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid4 SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;9SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;04 SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;9SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid8SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;07

SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid_2

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid6

SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid8SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;11

SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;_12

Keluaran

Tulis kode PHP untuk menyisipkan detail di tabel student_marks. (data2. PHP)