使用php从模态中的第二个sql表中检索数据

Question

所以问题是，如果数据在sql中的第二个名为locationstbl的表中，列名为address，我如何在模态中检索Locatie(下拉列表(的数据。

也许有某种方法可以使用JOIN或类似的东西来在下拉列表中显示我需要的数据？

谢谢。

这是代码。

<table class="table table-bordered" id="dataTable" width="100%" cellspacing="0">
<thead>
<tr>
<th>#</th>
<th>Locatie</th>
<th>Serie Aparat</th>
<th>Tip Joc</th>
<th>Cabinet</th>
<th>Data Expirare</th>
<th>Actiuni</th>
</tr>
</thead>
<tbody>

<?php

$sql = "SELECT location, serial, game_type, cabinet, date 
FROM all_machines 
ORDER BY location ASC";
$result = $conn->query($sql);
$resultNum = mysqli_num_rows($result);
$counter = 1;
if ($resultNum > 0) {
while ($row  = mysqli_fetch_assoc ($result)){
$location = $row['location'];
$serial = $row['serial'];
$game_type = $row['game_type'];
$cabinet = $row['cabinet'];
$date = $row['date'];

?>
<tr>
<td><?php echo $counter++;?></td>
<td><?php echo $location;?></td>
<td><?php echo $serial;?></td>
<td><?php echo $game_type;?></td>
<td><?php echo $cabinet;?></td>
<td><?php echo $date;?></td>
<td>    
<!-- Table Action Buttons -->
<a href="#editare<?php echo $serial;?>" data-toggle="modal">
<button type='button' class='btn btn-success btn-sm' data-whatever="<?php echo $serial;?>">Editare</button></a> 
<a href="#delete<?php echo $serial;?>" data-toggle="modal">
<button type='button' class='btn btn-danger btn-sm' >Sterge</button></a>
<a href='detalii.php?id=<?php echo $serial;?>'>
<button type='button' class='btn btn-info btn-sm'>Detalii</button></a>
</td>

<!--Delete Modal -->
<div id="delete<?php echo $serial; ?>" class="modal fade" role="dialog">                     
<div class="modal-dialog">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Confirmati stergerea</h4>
<button type="button" class="close" data-dismiss="modal">&times;</button>
</div>
<div class="modal-body">
<input type="hidden" name="serial" value="<?php echo $serial; ?>">
<div class="alert alert-danger"><p>Sunteti sigur ca doriti stergerea seriei <strong><?php echo $serial; ?></strong> ?</p>
</div>
</div>
<div class="modal-footer">
<a class='btn btn-danger btn-sm' href="delete.php?id=<?php echo $serial;?>">Sterge</a>
<button type="button" class="btn btn-primary btn-sm" data-dismiss="modal">Anuleaza</button>
</div>
</div>
</div>
</div>

<!--Edit Modal -->
<div id="editare<?php echo $serial; ?>" class="modal fade" role="dialog">                     
<div class="modal-dialog" role="document">
<!-- Modal content-->
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title">Editare Serie: <strong><?php echo $serial; ?></strong></h4>
<button type="button" class="close" data-dismiss="modal">&times;</button>
</div>
<div class="modal-body">
<form method="POST" action="edit-mac.php?id=<?php echo $serial;?>">
<div class="form-group">
<div class="form-row">
<!-- Select data from locationstbl -->
<div class='col-md-6'>
<label>Locatie</label>
<select name='location' class='form-control input-sm'>
<option></option>
</select>
<span class='help-block'></span>
</div>
<div class="col-md-6">
<label for="serial">Serie</label>
<input type="text" class="form-control" id="serialup" name="serialup" value="<?php echo $serial; ?>">
<span class="help-block"></span>
</div>
<div class="col-md-6">
<label for="serial">Tip Joc</label>
<input type="text" class="form-control" id="game_type" name="game_type" value="<?php echo $game_type; ?>">
<span class="help-block"></span>
</div>
<div class="col-md-6">
<label for="serial">Cabinet</label>
<input type="text" class="form-control" id="cabinet" name="cabinet" value="<?php echo $cabinet; ?>">
<span class="help-block"></span>
</div>
<div class="col-md-6">
<label for="date">Data expirare</label>
<input class="form-control" id="date" type="text" autocomplete="off" name="date" value="<?php echo $date; ?>" placeholder="LL-AAAA">
<span class="help-block"></span>
</div>
</div>
</div>    
</div>
<div class="modal-footer">
<button type="submit" class="btn btn-success btn-sm">Actualizeaza</button>
<button type="button" class="btn btn-primary btn-sm" data-dismiss="modal">Anuleaza</button>
</form>
</div>
</div>
</div>
</div>

</tr>
<?php
}
}
?>   

</tbody>     
</table>

Answer 1

在给出可能的答案之前，我想警告您开始考虑采用PHP开发框架并利用其优势。将PHP与HTML混合使用会适得其反，而且很难长期维护。你有两种方法：

1. 像第一个表一样对第二个表执行SQL查询，只获取下拉列表的信息：$locaties = "SELECT * FROM locationstbl"通过此解决方案，下拉列表如下所示：

<div class='col-md-6'>
<label>Locatie</label>
<select name='location' class='form-control input-sm'>
<?php foreach($locaties as $locatie){ ?>
<option><?php echo $locatie ?></option> 
<?php } ?>
</select>
<span class='help-block'></span>
</div>

2. 使用INNER JOIN进行单次搜索，只要有外键，就可以从位置表中获取信息+位置表信息