我有两个表
Create TABLE Comment(
CommentText TEXT,
CommentDate DATE NOT NULL,
Time time,
PostedBy VARCHAR(30),
FOREIGN KEY (PostedBy) REFERENCES Employee(Name)
);
Create TABLE Employee(
Name VARCHAR(30),
Title VARCHAR(30),
Onshift BOOLEAN,
PRIMARY KEY(Name)
);
现在我该怎么做呢
$Comments = mysql_query("SELECT * FROM Comment");
$all_comments = array();
while($row = mysql_fetch_array($Comments)) {
$all_comments[] = $row;
}
foreach($all_comments as $commentrow){
if($commentrow['PostedBy']){ //Check if The Employee is Onshift
Echo("The employee was on shift");
}
else{
Echo("The employee was on shift");
}
}
。E给定外键(postdby)查找外键表中的另一个值(在本例中，如果是布尔值，如果staff是shift?)
你的选择应该像
SELECT c.*, e.Name, e.Onshift FROM Comment as c join Employee as e on c.PostedBy = e.Name
然后你可以直接使用
$commentrow['Onshift']
您可以使用此注释的任何sql和测试您的代码
//Priority on comment table
$sql = "SELECT cmd.*, emp.* FROM Comment AS cmd
LEFT JOIN employee AS emp ON cmd.name = emp.name";
//use anyone of thsi based on ur need
$sql = "SELECT cmd.*, emp.* FROM Comment AS cmd
LEFT JOIN employee AS emp ON emp.name = cmd.name";
$Comments = mysql_query($sql);
$all_comments = array();
while($row = mysql_fetch_array($Comments)) {
if($row['PostedBy']){ //Check if The Employee is Onshift
echo("The employee was on shift");
}
else{
echo("The employee was not in shift");
}
}