En este artículo, uniremos dos tablas usando PHP y las mostraremos en la página web.
Introducción :
PHP es un lenguaje de secuencias de comandos del lado del servidor, que se utiliza para conectarse con bases de datos. Usando esto, podemos obtener datos de la base de datos usando scripts PHP. El lenguaje de base de datos que se puede usar para comunicarse con PHP es MySQL. MySQL es un lenguaje de consulta de base de datos que se utiliza para administrar bases de datos.
Requisitos:
Servidor Xampp: el servidor xampp se utiliza para almacenar nuestra base de datos localmente. Vamos a acceder a los datos del servidor xampp usando PHP.
En este artículo, estamos tomando la base de datos de detalles de los estudiantes que contiene dos tablas. Son dirección_del_estudiante y marcas_del_estudiante.
Estructura de las tablas:
table1=dirección_del_estudiante
table2=estudiante_notas.
Vamos a realizar INNER JOIN, LEFT JOIN, RIGHT JOIN en estas dos tablas.
1. UNIÓN INTERNA:
INNER JOIN es una palabra clave que selecciona registros que tienen valores coincidentes en ambas tablas.
Sintaxis:
SELECT column 1,column 2,...column n FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Ejemplo :
Deje que student_address contenga estos detalles
Y la tabla student_marks incluye
Al usar sid, podemos unir estas dos tablas usando un Inner join, ya que sid es común en dos tablas.
- Consulta para mostrar los detalles de student_address en función de la unión interna:
SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid;
Resultado :
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 : hyderabad
- Consulta para mostrar los detalles de student_marks en función de la combinación interna.
SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid
Resultado :
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
2. UNIRSE A LA IZQUIERDA:
La palabra clave LEFT JOIN se utiliza para devolver todos los registros de la tabla izquierda (tabla 1) y los registros coincidentes de la tabla derecha (tabla 2).
Sintaxis:
SELECT column1,column2,...columnn FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
- Consulta para mostrar toda la tabla student_address basada en la identificación del estudiante usando la combinación izquierda
SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid
Resultado :
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 : hyderabad STUDENT-ID : ----- NAME : gnanesh ----- ADDRESS : hyderabad
- Consulta para mostrar toda la tabla student_marks basada en la identificación del estudiante usando la combinación izquierda
SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid
Resultado :
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 : 79
3. UNIÓN DERECHA:
La palabra clave RIGHT JOIN se utiliza para devolver todos los registros de la tabla derecha (tabla2) y los registros coincidentes de la tabla izquierda (tabla1).
Sintaxis:
SELECT column1,column2,...columnn FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
- Consulta para mostrar toda la tabla student_address basada en la identificación del estudiante usando la unión derecha
SELECT * from student_address RIGHT JOIN student_marks on student_address.sid=student_marks.sid
Resultado :
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 : hyderabad STUDENT-ID : 7 ----- NAME : ----- ADDRESS :
- Consulta para mostrar toda la tabla student_marks basada en la identificación del estudiante usando la unión derecha
SELECT * from student_marks RIGHT JOIN student_address on student_address.sid=student_marks.sid
Resultado :
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 : 5 ----- SUBJECT 1 : ----- SUBJECT 2 :
Acercarse :
- Cree una base de datos llamada base de datos y cree tablas (dirección_estudiante y marcas_estudiante)
- Insertar registros en dos tablas usando PHP
- Escriba una consulta SQL para realizar todas las uniones usando PHP
- Observa los resultados.
Pasos:
- Iniciar servidor xampp
- Escriba «localhost/phpmyadmin» en su navegador y cree una base de datos llamada «base de datos», luego cree dos tablas llamadas dirección_del_estudiante y marcas_del_estudiante
Estructura de la tabla Student_address:
Estructura de la tabla Student_marks:
- Inserte los registros en la tabla student_address usando PHP (data1.php) Ejecute el código escribiendo «localhost/data1.php»
PHP
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql = "INSERT INTO student_address VALUES (1,'sravan kumar','kakumanu');";
$sql .= "INSERT INTO student_address VALUES (2,'bobby','kakumanu');";
$sql .= "INSERT INTO student_address VALUES (3,'ojaswi','hyderabad');";
$sql .= "INSERT INTO student_address VALUES (4,'rohith','hyderabad');";
$sql .= "INSERT INTO student_address VALUES (5,'gnanesh','hyderabad');";
if ($conn->multi_query($sql) === TRUE) {
echo "data stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Producción :
Escriba código PHP para insertar detalles en la tabla student_marks. (datos2.PHP)
PHP
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql = "INSERT INTO student_marks VALUES (1,98,99);";
$sql .= "INSERT INTO student_marks VALUES (2,78,89);";
$sql .= "INSERT INTO student_marks VALUES (3,78,98);";
$sql .= "INSERT INTO student_marks VALUES (4,89,98);";
$sql .= "INSERT INTO student_marks VALUES (7,89,79);";
if ($conn->multi_query($sql) === TRUE) {
echo "data stored successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Producción :
Escriba «localhost/data2.php» para ver el resultado
Escribir código PHP para realizar unión interna (form.php)
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "inner join on student_address: ";
echo "<br>";
echo "<br>";
//sql query to display all student_address table based on student id using inner join
$sql = "SELECT * from student_address INNER JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "inner join on student_marks: ";
echo "<br>";
echo "<br>";
//sql query to display all student_marks table based on student id using inner join
$sql1 = "SELECT * from student_marks INNER JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
//close the connection
$conn->close();
?>
</body>
</html>
Producción :
Escriba «localhost/form.php» en su navegador.
Escribir código para realizar la combinación derecha (form1.php)
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "right join on student_address: ";
echo "<br>";
echo "<br>";
//sql query to display all student_address table based on student id using right join
$sql = "SELECT * from student_address RIGHT JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "right join on student_marks: ";
echo "<br>";
echo "<br>";
//sql query to display all student_marks table based on student id using right join
$sql1 = "SELECT * from student_marks RIGHT JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
//close the connection
$conn->close();
?>
</body>
</html>
Producción :
Escriba «localhost/form1.php» en su navegador.
Escriba el código PHP para realizar la combinación izquierda (form2.php)
PHP
<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "database";
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
echo "left join on student_address: ";
echo "<br>";
echo "<br>";
//sql query to display all student_address table based on student id using left join
$sql = "SELECT * from student_address LEFT JOIN student_marks on student_address.sid=student_marks.sid";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
echo " STUDENT-ID : ". $row['sid'], " ----- NAME : ". $row['sname'] ," ----- ADDRESS : ". $row['saddress'] ;
echo "<br>";
}
echo "<br>";
echo "left join on student_marks: ";
echo "<br>";
echo "<br>";
//sql query to display all student_marks table based on student id using left join
$sql1 = "SELECT * from student_marks LEFT JOIN student_address on student_address.sid=student_marks.sid";
$result1 = $conn->query($sql1);
//display data on web page
while($row = mysqli_fetch_array($result1)){
echo " STUDENT-ID : ". $row['sid'], " ----- SUBJECT 1 : ". $row['subject1'] ," ----- SUBJECT 2 : ". $row['subject2'] ;
echo "<br>";
}
//close the connection
$conn->close();
?>
</body>
</html>
Producción :
escriba localhost/form2.php en el navegador
Publicación traducida automáticamente
Artículo escrito por sravankumar8128 y traducido por Barcelona Geeks. The original can be accessed here. Licence: CCBY-SA