SQL内连接不显示数据
16lz
2021-01-22
I am new to php so please could someone take a look at this php code and explain/edit why the inner joins do not display the information required in query2.
我是php的新手所以请有人看看这个PHP代码并解释/编辑为什么内部联接不显示query2中所需的信息。
<?php
{
mysql_connect("localhost" , "" , "") or die (mysql_error());
mysql_select_db("") or die(mysql_error());
$pid=intval($_SESSION["Patient_id"]); $query = "SELECT Appointment_id, Doctor_id, Patient_id, Appointment_time, Appointment_date FROM Appointment where Patient_id=$pid";
//executes query on the database
$result = mysql_query ($query) or die ("didn't query");
//this selects the results as rows
$num = mysql_num_rows ($result);
//if there is only 1 result returned than the data is ok
if ($num == 1) {}
{
$row=mysql_fetch_array($result);
$_SESSION['Appointment_date'] = $row['Appointment_date'];
$_SESSION['Appointment_time'] = $row['Appointment_time'];
}
$query2 = "SELECT Doctor_id FROM Appointment INNER JOIN Doctor ON Appointment.Doctor_id=Doctor.Doctor_id";
//executes query on the database
$result = mysql_query ($query) or die ("didn't query");
//this selects the results as rows
$num = mysql_num_rows ($result);
//if there is only 1 result returned than the data is ok
if ($num == 1) {}
{
$row=mysql_fetch_array($result);
$_SESSION['Doctor_id'] = $row['Doctor_id'];
$_SESSION['Name'] = $row['Name'];
$_SESSION['Room'] = $row['Room'];
}
}
?>
I am requesting Doctor_id, Name and Room from the Doctor table to display the information in the Appointment table
我要求Doctor表中的Doctor_id,Name和Room在约会表中显示信息
The tables are linked together. Doctor_id is the primary key in the Doctor table and the foreign key in the Appointment table.
表格链接在一起。 Doctor_id是Doctor表中的主键和Appointment表中的外键。
I need to display the Name and Room in the Appointment table.
我需要在约会表中显示名称和房间。
appointment.php code
<!DOCTYPE html>
<?php
session_start();
?>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="apple-mobile-web-app-capable" content="yes" />
<meta name="apple-mobile-web-app-status-bar-style" content="black" />
<title>
</title>
<link rel="stylesheet" href="https://ajax.aspnetcdn.com/ajax/jquery.mobile/1.2.0/jquery.mobile-1.2.0.min.css" />
<link rel="stylesheet" href="my.css" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js">
</script>
<script src="https://ajax.aspnetcdn.com/ajax/jquery.mobile/1.2.0/jquery.mobile-1.2.0.min.js">
</script>
<script src="my.js">
</script>
<!-- User-generated css -->
<style>
</style>
<!-- User-generated js -->
<script>
try {
$(function() {
});
} catch (error) {
console.error("Your javascript has an error: " + error);
}
</script>
</head>
<body>
<!-- Home -->
<div data-role="page" id="page1">
<div data-theme="a" data-role="header">
<a data-role="button" data-theme="d" href="login.html" data-icon="arrow-l" data-iconpos="left" class="ui-btn-left">
Back
</a>
<a data-role="button" href="index.html" data-icon="home" data-iconpos="right" data-theme="d"class="ui-btn-right">
Home
</a>
<h3>
Book appointment
</h3>
</div>
<div data-role="content">
<h3>
Select date/time:
</h3>
<br />
<?php
{
mysql_connect("localhost" , "" , "") or die (mysql_error());
mysql_select_db("") or die(mysql_error());
$pid=intval($_SESSION["Patient_id"]); $query = "SELECT Appointment_id, Doctor_id, Patient_id, Appointment_time, Appointment_date FROM Appointment where Patient_id=$pid";
//executes query on the database
$result = mysql_query ($query) or die ("didn't query");
//this selects the results as rows
$num = mysql_num_rows ($result);
//if there is only 1 result returned than the data is ok
if ($num == 1) {}
{
$row=mysql_fetch_array($result);
$_SESSION['Appointment_date'] = $row['Appointment_date'];
$_SESSION['Appointment_time'] = $row['Appointment_time'];
}
$query2 = "SELECT t1.*, t2.Name, t2.Room FROM Appointment AS t1
INNER JOIN Doctor AS t2
ON t1.Doctor_id=t2.Doctor_id";
//executes query on the database
$result = mysql_query ($query) or die ("didn't query");
//this selects the results as rows
$num = mysql_num_rows ($result);
//if there is only 1 result returned than the data is ok
if ($num == 1) {
$row = mysql_fetch_array($result, MYSQL_ASSOC);
print_r($row);['Doctor_id'] = $row['Doctor_id'];
print_r($row);['Name'] = $row['Name'];
print_r($row);['Room'] = $row['Room'];
} else {
// we have more than one row
}
}
?>
<strong>Dates available</strong>
<select id="Availability" name="Availability">
<option value="0">--Select date--</option>
<option value="1"><?php echo $_SESSION['Appointment_date'];?></option>
</select>
<br />
<br />
<strong>Times available</strong>
<select id="Availability" name="Availability">
<option value="0">--Select time--</option>
<option value="2"><?php echo $_SESSION['Appointment_time'];?></option>>
</select>
<br />
<br />
<strong>Doctor Name</strong>
<select id="Availability" name="Availability">
<option value="0">--Name--</option>
<option value="2"><?php echo $_SESSION['Name'];?></option>>
</select>
<br />
<br />
<strong>Doctor Room</strong>
<select id="Availability" name="Availability">
<option value="0">--Room--</option>
<option value="2"><?php echo $_SESSION['Room'];?></option>>
</select>
<br />
<br />
<label for="textarea1">
Message GP
</label>
<textarea name="" id="textarea1" placeholder="">
</textarea>
</div>
</div>
</body>
</html>
2 个解决方案
#1
0
ERROR 1052 (23000): Column 'doctor_id' in field list is ambiguous.
Your query2 should say:
你的query2应该说:
"SELECT Doctor.Doctor_id FROM Appointment INNER JOIN Doctor ON Appointment.Doctor_id=Doctor.Doctor_id";
or
"SELECT Appointment.Doctor_id FROM Appointment INNER JOIN Doctor ON Appointment.Doctor_id=Doctor.Doctor_id";
depending on what you want to query.
取决于您要查询的内容。
Best,
更多相关文章
- Mysql_案例1：查询出每个部门工资最高的员工信息
- MVC框架——学生信息管理系统（多表，多事务如何处理，一个用户如何共
- MySQL表格查询基本语句2
- 求问vs窗体应用程序用gridview连接mysql未能获取数据库对象的列
- 如何在bing地图中添加信息框到一个航点
- 高德地图api接口poi检索示例----并在信息框显示经纬度
- Sina weibo新浪微博 API返回信息详解
- 可编辑的jquery表格插件
- 如何在详细信息标记的结束事件上添加CSS转换？