我有困难显示数据从mysql数据库到PHP生成的表。有趣的是,下面显示的查询在SQL中运行良好,但当我尝试在PHP中包装它时,它显示空表而没有给出任何错误。
SELECT city.name, cinema.name, whattime, whichdate
FROM city, cinema, relationship
WHERE cinema.city = city.id
AND cinemaid = cinema.id
ORDER BY whichdate
下面是PHP代码:
<?php
$usr = "admin";
$pwd = "";
$db = "dbname";
$host = "localhost";
$con = mysql_connect($host, $usr, $pwd) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$sql = "select city.name, cinema.name, whichdate, whattime ";
$sql .= "from city, cinema, relationship ";
$sql .= "where cinema.city = city.id ";
$sql .= "and cinemaid = cinema.id ";
$sql .= "order by whichdate";
$query = mysql_query($sql, $con) or die(mysql_error());
echo "<table id='premiere'>";
echo "<tr> <th>CITY</th> <th>CINEMA</th> <th>DATE</th> <th>TIME</th></tr>";
while($result = mysql_fetch_array( $query )) {
echo "<tr><td>";
echo $result['city.name'];
echo "</td><td>";
echo $result['cinema.name'];
echo "</td><td>";
echo $result['relationship.whichdate'];
echo "</td><td>";
echo $result['relationship.whattime'];
echo "</td></tr>";
}
echo "</table>";
?>
您获取的数组将不包括表名作为数组键,只包括列名(如$result['whichname']
)
对city.name
和cinema.name
使用列别名来区分它们。
$sql = "select city.name AS cityname, cinema.name AS cinemaname, whichdate, whattime ";
//--------------------^^^^^^^^^^^^^^^------------^^^^^^^^^^^^^^^
$sql .= "from city, cinema, relationship ";
$sql .= "where cinema.city = city.id ";
$sql .= "and cinemaid = cinema.id ";
$sql .= "order by whichdate";
然后,而不是:
while($result = mysql_fetch_array( $query )) {
echo "<tr><td>";
echo $result['city.name'];
echo "</td><td>";
echo $result['cinema.name'];
echo "</td><td>";
echo $result['relationship.whichdate'];
echo "</td><td>";
echo $result['relationship.whattime'];
echo "</td></tr>";
}
只使用列名(或别名)作为数组键:
while($result = mysql_fetch_array( $query )) {
echo "<tr><td>";
echo $result['cityname'];
echo "</td><td>";
echo $result['cinemaname'];
echo "</td><td>";
echo $result['whichdate'];
echo "</td><td>";
echo $result['whattime'];
echo "</td></tr>";
}