php - 尝试在使用回声行时将发送者和接收者与内部联合表分开
问题描述
我已经从整个数据库中检索了整个表并拥有所有值，但是由于发送者和接收者在同一个表上，所以我在使用 echo 将它们显示在我的 html 页面上的表上时遇到了问题
我试过用
<?php
echo echo $row["u.username"]
?>
和
<?php
echo echo $row["us.username"]
?>
像我在查询中那样分开发送者和接收者但是我收到这个错误 注意：未定义的索引：C:\xampp\htdocs\otkoth\status.php中的 us.username 如您所见，我必须更改我的运输详细信息id 到 sid 以便在表格中显示这是当前输出
<!DOCTYPE html>
<html>
<!--
** Author - Steve Nginyo
** Project - Courier Services
** Section - Workflow
** Description - Acquiring on transit objects for display from the database
Product origin and destination are viewed
Product sender and recipient are also displayed
Viewing current status of the product is available here
Products are manually dispatched when placed in cars
At each checkpoint the product is marked that it has passed
The last checkpoint is the destination
The product is the marked arrived when it reaches the destination
-->
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Speedy Courier Status</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" type="text/css" media="screen" href="status.css" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
<!--
* Importation of bootstrap online classes is done here
* links to local css and javascript file is also done here
* bootstrap helps in styling of the web page content
-->
</head>
<?php
//creation of connection to the database
$conn = mysqli_connect("localhost:9090","root","","courier-services");
/**
* Check connection or link to the database is done or available display a message
* A message is displayed when the database connection is successful
* An error message is also displayed if the database connection is not successful
*/
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else{
echo "Worked";
}
/**
* Database tables are queried
* the table contains foreign keys
* the tables are therefore joined
* this helps query several databases at the same time
* the result is then saved in a variable result
*/
$sql = " SELECT * from shippingdetails s
inner join parcel p on s.parcelid = p.id
inner join offices o on s.officeid = o.id
inner join offices of on s.destinationid = of.id
inner join users u on s.senderid = u.id
inner join users us on s.recepientid = us.id
inner join vehicle v on s.vehicleid = v.id ";
$result = $conn->query($sql);
/**
* This commented code confirms the execution of the query
* in case the syntax is correct
* there may be no syntax error
* but the query may not have executed
*/
/* CODE TO CHECK QUERY EXECUTION
mysqli_query($conn, $sql);
$result1 = $conn->query($sql1);
if ($result){
echo "no";
}else{
echo "yes";
}
*/
?>
<body>
<div class="jumbotron">
<h1 class="display-3">View current status of the product sent</h1>
<p class="lead">Tracking of the current status by the work flow sections of the project ie. customer care, dispatch, checkpoints and destination</p>
<hr class="my-2">
<p>Edit product status on dispatch, arrival and at checkpoints</p>
</div>
<div class="container-fluid">
<?php
/**
* Checks whether there is a result set from the database
* if there is a result set the table is created
* the table header is then created here
* this is before the result set iteration
*/
if ($result->num_rows > 0) {
?>
<table class="table table-dark table-striped">
<thead>
<tr>
<th>ID</th>
<th>Sender</th>
<th>Receiver</th>
<th>Dispatch</th>
<th>Checkpoint</th>
<th>Arrival</th>
<th>Vehicle</th>
<th>Origin</th>
<th>Destination</th>
</tr>
</thead>
<tbody>
<?php
/**
* a new iteration without the table header is created
* the iteration is supposed to display items of the result set
* the iteration creates a new table row
* then it inputs the values from the designated table headers
* into the table columns within the row
* the iteration happens for each result in the set creating
* multiple rows
*/
// output data of each row
while($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo $row["sid"]?></td>
<td><?php echo $row["username"]?></td>
<td><?php echo $row["username"]?></td>
<td>
<?php
//checking for the current checkpoint of the product from table
if($row["checkpoint"] == 0 || $row["checkpoint"] < 0){
/**
* If the checkpoint is zero it means the product
* has not been dispatched yet
*/
echo "Not dispatched";
}else{
/**
* Otherwise the product has been dispatched
* and is enroute to the first checkpoint
*/
echo "Dispatched";
}
?>
</td>
<td><?php echo $row["checkpoint"]?> of 6</td>
<td>
<?php
//checks for current checkpoint of the product from table
if($row["checkpoint"] == 6 || $row["checkpoint"] > 6){
/**
* If the product has reached the sixth checkpoint it means it has arrived
* to the final checkpoint
* therefore it is marked arrived
*/
echo "Arrived";
}else{
/**
* If the product has yet to arrive at the final checkpoint
* the message of the product not having arrived is displayed
*/
echo "Not yet arrived";
}
?>
</td>
<td><?php echo $row["platenumber"]?></td>
<td><?php echo $row["cityname"]?></td>
<td><?php echo $row["location"]?></td>
<td>
<form action="todispatch.php">
<!--
a hidden input for a form is created with
a value of the id of the product from the database
-->
<input type="hidden" name="dispatch" value="<?php echo $row["sid"]?>">
<?php
//checks whether the product has been dispatched
if($row["checkpoint"] == 0 || $row["checkpoint"] < 0){
/**
* if it has yet to be dispatched the button is activated
* the form with the hidden input
* is redirected to "todispatch.php"
*/
?>
<button class="btn btn-primary btn-lg" type="submit" method="post">Dispatch</button>
<?php
}else{
/**
* the button is deactivated/disabled if the product has already been dispatched
*/
?>
<button class="btn btn-primary btn-lg" type="submit" disabled>Dispatch</button>
<?php
}
?>
</form>
</td>
<td>
<form action="tocheckpoint.php">
<input type="hidden" name="checkpoint" value="<?php echo $row["sid"]?>">
<?php
//checks the current status of the product
if($row["checkpoint"] > 0 && $row["checkpoint"] < 5){
/**
* if the product has not arrived and is dispatched
* the checkpoint button is enabled
* the form redirects to "to checkpoint.php"
*/
?>
<button class="btn btn-primary btn-lg" type="submit" method="post">Checkpoint</button>
<?php
}else{
/**
* the product is not dispatched
* therefore cannot go through the checkpoints
* or the product has arrived
* therefore has gone though all the checkpoints
*/
?>
<button class="btn btn-primary btn-lg" type="submit" disabled>Checkpoint</button>
<?php
}
?>
</form>
</td>
<td>
<form action="toarrival.php">
<input type="hidden" name="arrival" value="<?php echo $row["sid"]?>">
<?php
//checks current status of the product
if($row["checkpoint"] == 5){
/**
* If the product is at the final checkpoint designated
* as 5
* the button for arrival is enabled
* the hidden form with the id of the specific product
* is redirected to "to arrival.php"
*/
?>
<button class="btn btn-primary btn-lg" type="submit" method="post">Arrival</button>
<?php
}else{
/**
* else the button displayed is disabled
* since the product is yet to arrive to it's
* destination or has already arrived at it's
* destination and marked as so
* checkpoint 6
*/
?>
<button class="btn btn-primary btn-lg" type="submit" disabled>Arrival</button>
<?php
}
?>
</form>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
} else {
//in case there are no products within the database
//this message is displayed instead of the table
echo "<h3> There are no items currently in transit currently</h3>";
}
//the connection to the database is then closed for security purposes
$conn->close();
?>
</div>
</body>
</html>
我的表运输详细信息有来自 4 个不同表的 6 个外键要清楚，此查询确实检索数据，但我的问题是将我在连接期间使用的列与同一个表中的列分开
SELECT * from shippingdetails s
inner join parcel p on s.parcelid = p.id
inner join offices o on s.officeid = o.id
inner join offices of on s.destinationid = of.id
inner join users u on s.senderid = u.id
inner join users us on s.recepientid = us.id
inner join vehicle v on s.vehicleid = v.id
我希望将买家和接收者分开，然后为起点和目的地复制相同的内容，我改为输出城市名称和位置以使其更真实
解决方案
如果空间允许，我会这样做作为评论：
你的问题不是很清楚。但是，如果您必须将表与其自身连接起来，则需要在列名上使用别名以确保它们是唯一的。如果我有一个名为 my_table 的表，其中包含id、a和b列，并且我希望将其与自身连接，我可能会这样做：
SELECT t1.a AS a1, t1.b AS b1, t2.a AS a2, t2.b as b2 FROM
my_table t1 JOIN my_table t2 ON t1.id = t2.id
;
以上是一个无意义的示例，因为我只是为每一行复制相同的值 a1、b1 和 b1 和 b2。但是您可以看到我如何通过使用别名为每一列赋予一个唯一的名称。当然，您不必像我所做的那样对两个表都使用别名。
这是你想要完成的吗？
推荐阅读
- python - python中的主成分分析（PCA），加载图
- python - 在 Python 中查找字符串中的模式并打印
- sharepoint - 要添加到列表的页面上的 Sharepoint 表单
- python - 从 html 标签中提取信息到 pandas
- javascript - 如何为IOS实现javascript beforeunload事件功能？
- r - 如何在ggplot2中调整我的箭头方向？
- python - 熊猫矢量化方式产生一组行与所有其他行的成对组合
- java - Java.lang.UnsupportedClassVersionError，在 xampp tomcat 服务器上启动 url 时出现此错误
- python - 错误：sqlalchemy.exc.TimeoutError：QueuePool 限制大小为 5 溢出 10，连接超时，超时 30
- python - 如何从 Tkinter 中的菜单标签中获取文本