PHP MYSQL连接两个表并回显数据

Question

我有两张桌子

表personal_info包含以下信息

+--------+--------+--------+--------+
|   id   | fname  | lname  | email  |
+--------+--------+--------+--------+
|        |        |        |        |
+--------+--------+--------+--------+

表other_info包含以下信息

+--------+-----------------+--------------+----------------+
|   id   | university_name | course_name  | business_name  |
+--------+-----------------+--------------+----------------+
|        |                 |              |                |
+--------+-----------------+--------------+----------------+

现在，我想使用PHP连接这些表，并从两个表中获取信息。我决定这样做

<?php
include ('config.php');
$con = mysqli_connect($host, $user, $pass, $db) or die ('Cannot Connect : '.mysqli_error());
$sql = "select * from personal_info, other_info "; // Here i want to join the two tables to echo results
$result = mysqli_query($con,$sql)  or die("Error: ".mysqli_error($con));
while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ){

    echo "$row['fname'] ."".$row['lname'] ."".$row['university_name'] ."".$row['course_name'] ."".$row['businessname'] ";
}


?>

如何连接表并获取数据？

Answer 1

您需要先在other_info表中设置一个外键，以便可以通过该键进行连接。

那么像这样的select应该可以工作

select fname, lname, university_name, course_name, business_name
from personal_info t1
inner join other_info t2 on (t1.id = t2.personal_id)

t2.personal_id是表other_info中的外键列

Answer 2

<?php
include ('config.php');
$con = mysqli_connect($host, $user, $pass, $db) or die ('Cannot Connect : '.mysqli_error());
$sql = "SELECT personal_info.id,personal_info.fname,personal_info.lname,personal_info.email, other_info.university_name,other_info.course_name,other_info.business_name FROM personal_info FULL OUTER JOIN other_info ON personal_info.id=other_info.id;"; 
$result = mysqli_query($con,$sql)  or die("Error: ".mysqli_error($con));
while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ){

    echo "$row['fname'] ."".$row['lname'] ."".$row['university_name'] ."".$row['course_name'] ."".$row['businessname'] ";
}


?>

Answer 3

personal_info和other_info表之间没有关系。通过它，不可能知道与特定记录相关的其他信息。

所以，你可以做一件事。在other_info表中创建一列personal_info_id，它将存储personal_info表的id。

像这样：

personal_info

id | fname | lname | email
1   UserFN  UserLN  user@email
2   User2FN User2LN user2@email

other_info

id | personal_info_id | university_name | course_name | business_name
1       1                 UniV               PHP           ABC
2       2                 UniVY              C++           XYZ

现在，您可以使用other_info的personal_info_id和personal_info的id来连接两个表

SELECT pfo.*, ofo.* 
FROM personal_info pfo, other_info ofo 
WHERE pfo.id = ofo.personal_info_id;

已更新代码

<?php
include ('config.php');
$con = mysqli_connect($host, $user, $pass, $db) or die ('Cannot Connect : '.mysqli_error());
$sql = "SELECT pfo.*, ofo.* FROM personal_info pfo, other_info ofo WHERE pfo.id = ofo.personal_info_id;";
$result = mysqli_query($con,$sql)  or die("Error: ".mysqli_error($con));
while( $row = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ){
  echo $row['fname']." / ".$row['lname']." / ".$row['email']." / ".$row['university_name']." / ".$row['course_name']." / ".$row['business_name'];
}?>