Drop-Down List From Database Table

[SalientAnimal]

Hi All,

 

I have a bit of a complex question. (Well not sure how complex it is    ) But basically here's what I want to do.

 

I want my PHP form to select the values for the drop-down from the database table. This list however needs to update real-time.

 

Here's what I mean (Hope it makes sense):

 

Every morning I get a list of stock that is available:

 

• This stock is uploaded into the handset_stock table
  
• During the coarse of the day the availablity of stock changes according to what is sold.
  
• Each item sold gets captured seperately.
  

 

I want my drop-down list on my form to be populated by doing the following:

 

• Count All the Stock of a particular type sold
  
• Subtract the stock sold from the list of stock uploaded in the morning
  
• Once the stock has sold out, this option must be removed from the list
  

 

At the moment I have the query below which displays on a screen what stock is available

 

SELECT handset_stock.handset
, handset_stock.total_stock
, COALESCE( handset_stock.total_stock 
     - ( SELECT COUNT( sales.phone_deals ) AS 'handset_stock'
	FROM sales
	WHERE sales.phone_deals = handset_stock.handset
	AND DATE(sys_date) = CURDATE()
	GROUP BY phone_deals ) 
, handset_stock.total_stock) AS Balance
FROM handset_stock
GROUP BY handset

 

I think what might need to happen here is that this has to become a temporary table? But I'm not sure.

Can anyone tell me how to have my drop-down list populated by the result of this query?

[SalientAnimal]

Ok, so I think I might have a some progress on coding this, but I'm not sure if I at all on the right, please any advise will be appreciated.

 

So at the top of my paeg I have the folowing:

 

$result2 = mysql_query("SELECT handset_stock.handset
, handset_stock.total_stock
, COALESCE( handset_stock.total_stock 
     - ( SELECT COUNT( sales.phone_deals ) AS 'handset_stock'
	FROM sales
	WHERE sales.phone_deals = handset_stock.handset
	AND DATE(sys_date) = CURDATE()
	GROUP BY phone_deals ) 
, handset_stock.total_stock) AS Balance
FROM handset_stock
GROUP BY handset");

 

And then as my select option on the form I have this:

 

		<tr>
	<td width="34%" align="right" valign="middle"><strong>Handset Sold :</strong></td>		
	<select name="ud_handset_sold"><?php while($handset = mysql_fetch_array( $result2 ))?>
  			<option value="<?php echo $handset[handset];?>"><?php echo $handset['handset'];?></option>
        </select>
	</td></tr>

 

Now my page isn't displaying, and I'm getting an HTTP 500 Internal Server Error. What am I doing wrong?

 

Oh, and the reason the query is showing as $result2 is because I have two queries running on this page. The first one, collects information from another table on the database, and then pre-populates the form, the only field that is not populated is the handset field, hence the second query to look up for stock in the table.

[SalientAnimal]

Ok, so slowly but surely, I am making progress on this. And I have been able to get my list to be returned from the database. The problem is that is it returning everything, rather than returning results based on the calculation.

 

Here's what I have:

 

$result2 = mysql_query("SELECT *
FROM handset_stock
GROUP BY handset");
$handset = mysql_fetch_array($result2);

 

And then to return the list:

 

		<td width="34%" align="right" valign="middle"><strong>Handset Sold :</strong></td>
	<td width="66%" align="left" valign="middle"><select name="ud_handset_sold" id="ud_handset_sold">	
	<option value="<?php echo "$handset_sold"?>">-- Select the Handset Sold --</option>		
		<?php
		while ($handset = mysql_fetch_array($result2)){
		?>
		<option value="<?php $handset['handset']; ?>"><?php echo $handset['handset'];?></option>
		<?php
		} 
		?>
		</select>
		</td></tr>

 

So yes that works, but I'm not able to use the result set that I want to use. The result that I want to use will be from the following query:

$result2 = mysql_query("SELECT handset_stock.handset
, handset_stock.total_stock
, COALESCE( handset_stock.total_stock 
     - ( SELECT COUNT( sales.phone_deals ) AS 'handset_stock'
	FROM sales
	WHERE sales.phone_deals = handset_stock.handset
	AND DATE(sys_date) = CURDATE()
	GROUP BY phone_deals ) 
, handset_stock.total_stock) AS Balance
FROM handset_stock
WHERE Balance >= '1'
GROUP BY handset");
$handset = mysql_fetch_array($result2);

 

Oh and the problem with the query above is that the where clause on the balance field does not seem to be working. How do I get it to display only the handsets if there is 1 or more left?