我在从两个表中检索一些数据,然后将输出显示到一个区域时遇到问题。
这是检索的代码我有
$select_users = "SELECT CarID, Model, Year, Price, ManuName FROM Vehicle, Manufacturer";
//run query
$result = mysql_query($select_users);
这是输出的代码
<?php
while ($user = mysql_fetch_array($result)){
$user_row = sprintf(
"<li><a href='select_veh.php?CarID=%d' class='mainnavstyle'>%s %s %s %s</a></li>",
$user['CarID'],
$user['ManuName'],
$user['Model'],
$user['Year'],
$user['Price']);
echo $user_row;
}
?>
我似乎无法让"ManuName"正确显示,因为它来自不同的表。目前有了这个代码,我得到了重复的结果:
honda Civic 2000 $15,000
honda Corolla 1991 $5,000
Toyota Civic 2000 $15,000
Toyota Corolla 1991 $5,000
而不仅仅是
Honda Civic 2000 $15,000
Toyota Corolla 1991 $5,000
如有任何帮助,我们将不胜感激感谢
您正在执行一个交叉联接,它将一个表的每一行与另一个表中的每一行都匹配。这几乎可以肯定是你不想要的。相反,您需要限制表格的连接方式,为此,您的表格车辆和制造商需要相互关联,例如:
CREATE TABLE manufacturer (
id integer primary key auto_increment,
name varchar(255)
);
CREATE TABLE vehicle (
id integer primary key auto_increment,
name varchar(255),
year integer,
price double,
manufacturer_id integer not null,
FOREIGN KEY(manufacturer_id) REFERENCES manufacturer(id)
);
然后你可以像这样得到你想要的信息:
SELECT m.name, v.name, v.year, v.price FROM vehicle v
INNER JOIN manufacturer m
ON v.manufacturer_id = m.id
你可以在这里看到这一点:http://sqlfiddle.com/#!9/5c1f2/1
在vehicle
表中,我会有制造商的外键,然后查询类似于:
SELECT Vehicle.CarID, Vehicle.Model, Vehicle.Year, Vehicle.Price, Vehicle.ManuName, Manufacturer.name
INNER JOIN Manufacturer ON(Vehicle.manufacturerId = Manufacturer.id)
FROM Vehicle
您可以使用联合或联接一起执行sql查询
或者,你也可以这样做:
$sql1= "SELECT something FROM tbl WHERE column='".query."'";
$result1= $con->query($sql1);
if($result1->num_rows > 0){
while($row = $result->fetch_assoc()) {
$rowans = $row["row"];
$sql2= "SELECT * FROM tbl2 WHERE column2= '".something."'";
$gettingRows2= $con->query($sql);
if($gettingRows2->num_rows > 0){
while($anotherrow= $sql2->fetch_assoc()) {
$sql2Row= $anotherrow["row2"];
}
}
}
}
echo $rowans." ".$sql2Row;