我有一张表,其中一个食品id与多个供应商id相关。因此,我需要产生一个结果,下拉菜单可以列出与食品ID相关的供应商ID。例如,食品id 1与供应商id 1、4和3相关。我需要将所有与食品id 1相关的供应商id放在下拉列表中。其余的可以按照顺序出现,就像食物id 2的情况一样。
|食品ID |供应商ID(下拉列表)|下拉列表中我想要的选项----------------------------------------------------------------------------|1|1|v|1、4和3|2|5|v|5
我已经尝试了一个多星期了,但每次尝试都失败了。伙计们,请帮帮我。我需要和第二张图片的人一样的输出。该网站不允许我发布图片。真对不起那些家伙。
<?php
require_once('connect.php');
$selectfood= mysql_query("
SELECT foodstock.stock_id, foodstock.foodid, food.food_id, food.food_name, foodstock.quantity, assignfoodtosup.supplierid
FROM foodstock
JOIN food
ON foodstock.foodid=food.food_id
JOIN assignfoodtosup
ON food.food_id=assignfoodtosup.foodid
WHERE foodstock.quantity<10
");
$selectsupplier= mysql_query("
SELECT foodstock.stock_id, foodstock.foodid, food.food_id, food.food_name, foodstock.quantity, assignfoodtosup.supplierid
FROM foodstock
JOIN food
ON foodstock.foodid=food.food_id
JOIN assignfoodtosup
ON food.food_id=assignfoodtosup.foodid
WHERE foodstock.quantity<10
");
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Make Order</title>
</head>
<body>
<form action="#" method="post">
<table border=1>
<tr>
<th id="tdsn">S.No.</th>
<th id="tdfoodid">Food ID</th>
<th id="tdfoodname">Food Name</th>
<th id="tdstockqty">Stock Qty.</th>
<th id="tdorderqty">Order Qty.</th>
<th id="tdsupplierid">Supplier ID</th>
<th id="tdsuppliername">Supplier Name</th>
<th id="tdmorder">Make Order</th>
</tr>
<?php
$count=0;
$suppliercount=0;
$stock_array[]=array();
$result_array[]=array();
while($row = mysql_fetch_array($selectsupplier)){
$suppliercount++;
$result_array[$suppliercount]=$row['supplierid'];
}
while($rowstock = mysql_fetch_array($selectfood)){
$stock_array[] = $rowstock;
$count++;
?>
<tr>
<td><?php echo $count; ?></td>
<td><?php echo $stock_array[$count]['foodid']; ?></td>
<td><?php echo $stock_array[$count]['food_name']; ?></td>
<td><?php echo $stock_array[$count]['quantity']; ?></td>
<td><input type="text" id="orderqty" name="orderqty" style="width:50px"></td>
<td>
<select id="supplierid" name="supplierid">
<option value="<?php echo $result_array[$count]; ?>"><?php echo $result_array[$count]; ?> </option>
</select>
</td>
<td>
</td>
<td><input type="checkbox" id="makeorder" name="makeorder" value="$count"></td>
</tr>
<?php
}
?>
</table>
<input type="submit" id="submit" name="submit" value="Submit">
<br>
</form>
</body>
</html>
伙计们我有四张桌子
1) 供应商:suppliersID,suppliername。。。。。。。2) 食物:美食家,美食家的名字。。。。。。。3) assignfodtosup:assignfodtossupid、supplierid、foodid4) 食品库存:食品id,食品名称,数量
问题是,我必须做一张桌子,在那里我必须展示那些数量少于10的食物。我还必须向那些销售这些食品的供应商展示。因为这是我必须实现我所要求的代码的地方,所以就是它。
只需对代码进行一些更改。我认为最大的问题是,在编写select
选项时,您没有在供应商数组(称为$result_array
)上循环。在这里,我在<options>
周围封装了一个foreach
循环,以写出每个供应商id选择。
此外,我猜您在有多个供应商的地方列出了重复的食品。所以我在食品查询中取出了assignfoodtosup
JOIN
,这样每个供应商就不会重复的食物。并从供应商查询中取出不必要的字段和不必要的food
表JOIN
。该查询可能会进一步缩短,因为我猜您实际上不需要将该查询限制为食品库存数量小于10。
我还更改了您准备和访问供应商阵列的方式。首先,[]
不是必需的。其次,使用foodid
来链接食品及其供应商,而不是计数器,不太容易出错。
<?php
require_once('connect.php');
$selectfood= mysql_query("
SELECT foodstock.stock_id, foodstock.foodid, food.food_id, food.food_name, foodstock.quantity
FROM foodstock
JOIN food
ON foodstock.foodid=food.food_id
WHERE foodstock.quantity<10
");
$selectsupplier= mysql_query("
SELECT foodstock.stock_id, foodstock.foodid, assignfoodtosup.supplierid
FROM foodstock
JOIN assignfoodtosup
ON foodstock.foodid=assignfoodtosup.foodid
WHERE foodstock.quantity<10
");
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Make Order</title>
</head>
<body>
<form action="#" method="post">
<table border=1>
<tr>
<th id="tdsn">S.No.</th>
<th id="tdfoodid">Food ID</th>
<th id="tdfoodname">Food Name</th>
<th id="tdstockqty">Stock Qty.</th>
<th id="tdorderqty">Order Qty.</th>
<th id="tdsupplierid">Supplier ID</th>
<th id="tdsuppliername">Supplier Name</th>
<th id="tdmorder">Make Order</th>
</tr>
<?php
$count=0;
$food_suppliers=array();
while($row = mysql_fetch_array($selectsupplier)){
// using the supplierid for both the key and the value to ensure there are no duplicates
$food_suppliers[$row['foodid']][$row['supplierid']]=$row['supplierid'];
}
while($row = mysql_fetch_array($selectfood)){
?>
<tr>
<td><?php echo $count; ?></td>
<td><?php echo $row['foodid']; ?></td>
<td><?php echo $row['food_name']; ?></td>
<td><?php echo $row['quantity']; ?></td>
<td><input type="text" id="orderqty" name="orderqty" style="width:50px"></td>
<td>
<select id="supplierid" name="supplierid">
<?php foreach ( $food_suppliers[$row['foodid']] as $supplierid ) { ?>
<option value="<?php echo $supplierid; ?>"><?php echo $supplierid; ?></option>
<?php } ?>
</select>
</td>
<td>
</td>
<td><input type="checkbox" id="makeorder" name="makeorder" value="$count"></td>
</tr>
<?php
}
?>
</table>
<input type="submit" id="submit" name="submit" value="Submit">
<br>
</form>
</body>
</html>
我可以帮助您处理逻辑;我相信你可以从网上得到语法。
数据库表:
1) 食品(id,name)
2) 供应商(id,food_id,name)
PHP查询
SELECT * FROM `Suppliers` WHERE `food_id` is 1 // 1 is just an example. You can declare and call a variable here. *Check for the syntax*
结果将是一个数组。您将需要将此数组循环到下拉代码中。
注:存储在Supplier
表中的food_id
;将被插入其中,同时为CCD_ 13表完成该数据输入。因此,您需要确保您的表单处理代码已经完成了该规定。它基本上充当了两者之间的纽带。一个常见的参数。
希望这能有所帮助。
@stdahal我想建议你一些其他的方法来完成它,因为你的逻辑似乎不完美你的场景,
<?php
require_once('connect.php');
$selectfood= mysql_query("select foodstock.id,foodstock.food_name,foodstock.quantity,foodstock.foodid from foodstock");
$selectsuppliers = mysql_query("select assignfoodtosup.foodid,assignfoodtosup.supplierid from suppliers join assignfoodtosup on supplierid=suppliersID");
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Make Order</title>
</head>
<body>
<form action="#" method="post">
<table border=1>
<tr>
<th id="tdsn">S.No.</th>
<th id="tdfoodid">Food ID</th>
<th id="tdfoodname">Food Name</th>
<th id="tdstockqty">Stock Qty.</th>
<th id="tdorderqty">Order Qty.</th>
<th id="tdsupplierid">Supplier ID</th>
<th id="tdsuppliername">Supplier Name</th>
<th id="tdmorder">Make Order</th>
</tr>
<?php
$count=0;
$suppliercount=0;
$stock_array[]=array();
$result_array[]=array();
while($row = mysql_fetch_array($selectsuppliers)){
$result_array[$row["foodid"]][] = $row["supplierid"];
}
while($rowstock = mysql_fetch_array($selectfood)){
$count++;
?>
<tr>
<td><?php echo $count; ?></td>
<td><?php echo $rowstock['foodid']; ?></td>
<td><?php echo $rowstock['food_name']; ?></td>
<td><?php echo $rowstock['quantity']; ?></td>
<td><input type="text" id="orderqty" name="orderqty" style="width:50px"></td>
<td>
<select id="supplierid" name="supplierid">
<?php foreach ($result_array[$rowstock['foodid']] as $key => $value) {
?>
<option value="<?php echo $value; ?>"><?php echo $value; ?> </option>
<?php }?>
</select>
</td>
<td>
</td>
<td><input type="checkbox" id="makeorder" name="makeorder" value="$count"></td>
</tr>
<?php
}
?>
</table>
<input type="submit" id="submit" name="submit" value="Submit">
<br>
</form>
</body>
</html>
我希望它能更好地帮助你。。