从MySQL下拉列表中检索值，其中一个数据与多个数据相关（PHP和MySQL）
I have a table where a single food id is related to multiple supplier ids. Hence, I need to produce a result where the drop down menu can list those supplier ids which are related to the food ids. For instance the food id 1 is related to the supplier ids 1, 4 and 3. I need to put all those supplier ids related to the food id 1 in a drop down list. The rest can appear as they are in sequential order like in the case of food id 2.
|FOOD ID | SUPPLIER ID (DROP DOWN LIST) | OPTIONS I want in the drop down ---------------------------------------------------------------------------- |1 | 1 |v| 1, 4 and 3 |2 | 5 |v| 5
I have been trying this for over a week now, and I have failed to do it in every attempt. Please help me out here guys. I need the same output as the second image guys. The site wouldn't allow me to post images. So sorry about that guys.
<?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>
Guys I have four tables
1) suppliers: suppliersID, suppliername....... 2) food: foodid, foodname....... 3) assignfoodtosup:assignfoodtosupid, supplierid, foodid 4) foodstock:foodid, foodname, quantity
The thing is, i have to make a table where i have to show those food whose quantity is less than 10. And I also have to show those suppliers who sell those food. As this is where I have to implement the code I was asking for, this is it.
Just a few changes to your code. I think the biggest problem was that you're not looping over your suppliers array, called $result_array when you write your select options. Here I wrapped a foreach loop around the <options> to write out each supplier id selection.
Also, I'm guessing that you had duplicate foods listed wherever there are more than one supplier. So I took out the assignfoodtosup JOIN in your foods query so your foods aren't duplicated for each supplier. And took out unnecessary fields and the unnecessary food table JOIN from the suppliers query. That query could be shortened even further because I'm guessing you don't really need to limit this query to foodstock quantities less than 10.
And I changed how you prepare and access your suppliers array. First, the [] weren't necessary. Second, it's less error prone to use the foodid to link the foods and their suppliers, rather than a counter.
<?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>
I can help you with the logic; I am sure you can get the syntax from web.
Database Tables:
1) Food (id, name)
2) Suppliers (id, food_id, name)
PHP Query
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*
The result will be an array. You will require to loop this array into your dropdown code.
Note: the food_id that is stored in Supplier table; will be inserted into it while that data-entry is done for the food table. So you need to make sure that your form processing code has that provision done. It basically acts as a link between the two. A common parameter.
Hope this helps.
@stdahal I would like to suggest you some other ways to get it done around, since your logic seems not perfect you scenario,
<?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>
I hope it will help you better..