php - SQL/PHP:在另一个表(多个表)上没有匹配项的联接
问题描述
我有四张桌子:
Personal_trainer(personaltrainerID, name, age, location)
Client(clientID, name, age, location)
Nutrition_Plan(NutritionplanID, personaltrainerID, clientID)
Training_Plan(TrainingplanID, personaltrainerID, clientID)
我试图表明,当私人教练创建营养/培训计划并将其分配给客户时,结果只会显示他们的特定客户,但他们在客户表中没有外键来识别教练(由于项目标准)
我想知道培训/营养计划的必要加入的 SQL 是什么。我已经尝试了很长一段时间,这是我的示例代码。
所需的输出是所有客户数据,前提是他们已被该特定培训师分配了培训或营养计划
在声明中,我遇到了 Bind 参数的问题,因此只有拥有客户端的用户才能看到他们的客户端。如果我使用指定的 ID,我可以获得退货!
<?php
//code to search for a item from the database
// user can enter any character to search for a value from the db
if (isset($_POST['search']))
{
$valueToSearch = $_POST['ValueToSearch'];
$query = "select * from client WHERE concat(`clientID`, `name`, `age`, `sex`, `weight`, `height`, `yearsExperience`, `goal`, `injuries`, 'email')like'%".$valueToSearch."%'";
$search_result = filterTable($query);
}
else {
$query = "select *
from client
where clientID in (select clientID from nutrition_plan where personaltrainerID=?)
or clientID in (select clientID from training_plan where personalTrainerID=?)";
$query->bind_param("i", $_POST[""]);
$search_result = filterTable($query);
}
//code to filter the db
function filterTable($query)
{
$connect = mysqli_connect("localhost:3308","root","","fypdatabase");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
<?php
while($row = mysqli_fetch_array($search_result))
{ //display the details from the db in the table with option to delete or update entry
?>
<tr>
<td><?php echo $row["clientID"]; ?></td>
<td><?php echo $row["name"]; ?></td>
<td><?php echo $row["age"]; ?></td>
<td><?php echo $row["sex"]; ?></td>
<td><?php echo $row["weight"]; ?></td>
<td><?php echo $row["height"]; ?></td>
<td><?php echo $row["yearsExperience"]; ?></td>
<td><?php echo $row["goal"]; ?></td>
<td><?php echo $row["injuries"]; ?></td>
<td><?php echo $row["email"]; ?></td>
<td>
<a href="?Delete=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Delete</a>
</td>
<td>
<a href="updateClient.php?Edit=<?php echo $row["clientID"]; ?>" onclick="return confirm('Are you sure?');">Update</a>
</td>
</tr>
<?php
解决方案
让我们看看我是否正确理解您的要求...
仅显示具有培训师 123 的营养或培训计划的客户:
select *
from client
where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)
or clientid in (select clientid from training_plan where personaltrainerid = 123);
向这些客户展示他们的所有计划(无论计划的培训师如何):
select *
from client
join
(
select 'nutrition' as kind, nutritionplanid as id, personaltrainerid, clientid
from nutrition_plan
union all
select 'training' as kind, trainingplanid as id, personaltrainerid, clientid
from training_plan
) plan using (clientid)
where clientid in (select clientid from nutrition_plan where personaltrainerid = 123)
or clientid in (select clientid from training_plan where personaltrainerid = 123);
推荐阅读
- python - 对 Sanic 应用程序进行异步单元测试会引发 RuntimeError:此事件循环已在运行
- python - json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0) 在发送 56 个请求后出现
- json - 如何使用 Vue 从 json 对象中获取父元素
- data-structures - 删除链表
- laravel - Laravel - 使用 Rule 类自定义错误消息
- android - 尽管传递了项目 ID,但仍获得 ResourceNotFoundException
- cypress - 如何单击柏树中的链接
- java - 将外部类的值设置为内部类中检索到的值
- jquery-ui - 如何在向上拖动时修复“可调整大小框下的这个移动框
- android - 未解决的依赖关系:找不到任何与 com.android.support:appcompat-v7:29.+ 匹配的版本