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.
