使用php从多个数据库表中执行搜索

Question

我已经注册了一个办公室，在它下面有多个治疗、设施和课程(分别从treatment_type、office_facility和课程表中选择治疗、设施和课程的值)。对于每个办公室选择的每个项目，我都创建了一个单独的表，因为它具有多对多的关系。这些表包括：

register_office (在注册office时使用)

id  officename  city
1    Office-1    xyz
2    Office-2    xyz
3    Office-3    xyz

course_for_office (当办公室选择课程时使用，officeid是在注册office时创建的id )

id  officecourse  officeid
1     C1           1
2     C2           2

facility_for_office (当办公室选择一个设施时使用，officeid是在注册office时创建的id )

id  officefacility  officeid
1     F1             1
2     F2             3

treatment_for_office (当office选择一种待遇时使用，officeid是注册office时创建的id )

id  officetreatment  officeid
1     T1              1
2     T2              2

现在在前端，我有3个从数据库表(treatment_type，office_facility，course)填充的下拉列表。当用户选择我想要的每个值时，该用户应该是able to see the list of offices，以及它们的所有详细信息(例如officename、city等值将来自register_office表)

目前，我用来搜索的代码是

<?php
$con=mysqli_connect("localhost","root","","db");// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$officetreatment = mysqli_real_escape_string($con, $_POST['officetreatment']);
$officecourse = mysqli_real_escape_string($con, $_POST['officecourse']);
$officefacility = mysqli_real_escape_string($con, $_POST['officefacility']);

$sql1 = "SELECT course_for_office.officecourse,
                facility_for_office.officefacility,
                treatment_for_office.officetreatment
                register_office.id
        FROM course_for_office INNER JOIN facility_for_office INNER JOIN treatment_for_office INNER JOIN
        ON course_for_office.officeid = facility_for_office.officeid = treatment_for_office.officeid = register_office.id
        WHERE officecourse LIKE '%$officecourse%' officetreatment LIKE '%$officetreatment%' officefacility LIKE '%$officefacility%'";


$result = mysqli_query($con, $sql1);

if (mysqli_num_rows($result) > 0) 
    {
        while($row = mysqli_fetch_assoc($result)) {
        echo  $row["officeid"];
    }
} else {
    echo "0 results";
}
mysqli_close($con);
?>

问题是，每当我尝试搜索时，即使数据库中存在该值，我也会得到0结果。

Answer 1

您只需要更改您的mysql查询，尝试这个，它将工作...

SELECT cfo.officecourse,ffo.officefacility,tfo.officetreatment,ro.id,ro.officename
FROM register_office as ro
LEFT JOIN course_for_office as cfo ON ro.id = cfo.officeid
LEFT JOIN facility_for_office as ffo ON ro.id = ffo.officeid
LEFT JOIN treatment_for_office as tfo ON ro.id = tfo.officeid
WHERE cfo.officecourse LIKE '%$officecourse%'  
OR  tfo.officetreatment LIKE '%$officetreatment%' 
OR ffo.officefacility LIKE '%$officefacility%'