我有三个表。
用户
________________________________________________
|User_ID | User Name | user contact information|
------------------------------------------------
学校
________________________________________________________________
|School_ID | School name | school address | school contact info|
----------------------------------------------------------------
图片
___________________________________________
| Picture ID | User_ID | School_ID|file path|
-------------------------------------------
每个school_id,我想显示以下信息。
学校名称,学校地址,用户名,用户联系信息以及所有具有匹配学校ID的图片。
php中的脚本将是什么。我已经在此网站上研究了几天,并尝试了许多代码,但是到目前为止,什么都没有。
这是我使用的代码。
<?php
$sql="SELECT Schoolname, School_contact_info, email, description, address, website, file, userName FROM school, picture, user WHERE Location_ID=Picture_ID ORDER BY Location_ID desc";
$result_set=mysql_query($sql);
while($row=mysql_fetch_array($result_set))
{
?>
<div href=<?php $row['Location_ID'] ?>>
<td><?php echo $row['Location_ID'] ?></td>
<td><?php echo $row['name2'] ?></td>
<td><?php echo $row['email'] ?></td>
<div><td><?php echo $row['description'] ?></td></div>
</div>
<div><img src="uploads/<?php echo $row['file'];?>" alt=" " height="75" width="75"></div>
<br>
<?php
}
?>
我想显示学校信息,以及所有以school_id为外键的图片。
应使用
select school_name,school_address,user_name,user_contact_information
from user,school,picture where user.user_id=picture_id
and school.school_id=picture.picture_id
<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Perform queries
mysqli_query($con,"select school_name,school_address,user_name,user_contact_information
from user,school,picture where user.user_id=picture_id
and school.school_id=picture.picture_id");
在您的表结构中,您没有在查询中使用的所有列详细信息。
但是尝试一下: -
SELECT s.Schoolname, s.School_contact_info, s.email, s.description, s.address, s.website,
p.file, u.userName
FROM school as s,
picture as p on s.School_ID=p.School_ID,
user as u on p.User_ID=u.User_ID
WHERE s.Location_ID=p.Picture_ID ORDER BY s.Location_ID desc
我假设Location_ID,email,website
之类的列来自学校表。