Need help with Storage system


#1

I’m busy with a small storage system for my site, but i encounter some problems with it.
I have successfully managed to get all products from the order, and traced on what spots those product are in the warehouse.
It looks like this atm http://img37.imageshack.us/img37/7302/tableordereng.jpg

this is the code i have atm (sorry i program in dutch, i hope its abit understandable)

[php]

<?php $query = "select * from `orderregel` WHERE `ordernr`='".$ordernr."'"; $row = mysql_fetch_array( $query ); echo "
artikelnummer productnaam Plaats aantal in magazijn aantal nodig
"; foreach ( self::find_by_sql($query) as $user) { $query2 = "select * from magazijn WHERE artikelnr = ".$user->artikelnr.""; $row = mysql_fetch_array( $query ); foreach ( self::find_by_sql($query2) as $magazijn) { echo ""; echo "
".$magazijn->artikelnr." ".$magazijn->productnaam." ".$magazijn->magazijn_plaats." ".$magazijn->aantal." ".$user->aantal."
"; } } ?>

[/php]

The problem i have now is, that i want it to do the following.
for example someone orders 430 packages of Haribo Kers Cola (see imageshack upload). I first want it to empty spot 16 in the wherehouse.
Only when spot 16 is empty, i want it to go to the next spot (19) wich contains the same product.

the end result would be something like this:

http://img690.imageshack.us/img690/6246/tableorderneweng.jpg

i hope you guys can help me out on this one, becouse i have no clue how i can resolve this problem

thanks in advance

(Sorry btw for my bad english :P)


#2

Well, your sample code you posted shows a query pulling data and displaying the data.
If you want to do the processing in the query, you would have to sort it by quantities of the product.

Using ORDER BY in the SQL could sort the table for you, but, it will not select the smallest place first.

First, make sure your table has each serial number sorted so that the PLACE’s col will be the same
every time you display it. I suggest lowest number first. Then, your program can check that col each
time it is displayed and do some calculations. First, it will see if the place is empty ( 0 ), if so, nothing
would be placed into the “amount needed” col. If not empty, then the total needed would be used to
enter into “amount needed” col. If not enough in the “place” col, you would have to carry some needed
over to the next row. Does this make sense. Then, every thing would be handled in the table display.
Mostly a mix of PHP in the display code.

If you can not figure this out, perhaps I can give you some further ideas… Good luck…