RE: [PHP] Adjust two or more mysql rows ?

Multiple queries are going to be in order as mentioend i beleive, also, as mentioned, you may want to put some relational db design into practice here, im not sure how big your table is going to be, but its going to have a lot of same data in it.. how about table products id INT AUTO_INCREMENT NOT NULL, name VARCHAR(100) NOT NULL, datein DATETIME NOT NULL table stock product_id INT NOT NULL raw_stock UNSIGNED INT NOT NULL, other_stock UNSIGNED INT NOT NULL, jason "Dave Carrera" <dave@ephgroup.com> wrote: > > Sort of... > > Here is a sort of layout of my table data: > > Id | prodname | qty | datein > 1 toy1 33 2004-08-11 13:41:26 > 2 toy2 12 2004-08-11 14:41:26 > 3 toy1 16 2004-08-11 15:41:26 > 4 toy3 22 2004-08-11 16:41:26 > > This is what I mean by rows, maybe wrong terminology... > > So as you can see two rows have a qty > 0 and prodname is the same (toy1) > > It is reducing their qtys where datein is oldest from now() is what I am > trying to do. > > I am trying something now sort of like this: > > $val = $_POST[qty] // equals 53 > > Select from table where prodanme=$_POST[pname] and qty >0 order by datein > desc > > $row = mysql_fetch_array($sql); // this gives me the oldest row with whers > satisfied > > Now compare $row[qty] with $val > > If its <= $row[qty] then update "row" with row[qty]-$val; exit; > > Else if $val > $row[qty] then $newval = $val-$row[qty]; > > Update the current $row[qty] to 0 then run the sql again as it will get the > row which has a qty value > > Keep going til $newval = 0 exit. > > Probably wont work as I have it laid out here but I think the basic logic > is ok. > > Hers goes.... :-) > > Dave Carrera > > -- > UK Web Hosting @ http://www.ephgroup.com > > > -----Original Message----- > From: Jason Davidson [mailto:jdavidson@zworg.com] > Sent: 11 August 2004 19:26 > To: Dave Carrera > Subject: RE: [PHP] Adjust two or more mysql rows ? > > > ok, i understand better, so you have a good idea of what your doing by looks > of it, i guess your trying to get some opinionis on the best way of doing > this? > > so, to make your query and logic easier, how about making your row1 column > type an unsigned int, then it cant go below zero, and you can just minus the > qty right in the update query, then you can do something like.. UPDATE goods > SET row1 = (row1-$qty), row2 = .................. > > likely will have to use some logic to extrapolate some of your values..does > this help at all... Jason > > "Dave Carrera" <dave@ephgroup.com> wrote: > > > > I am deving a goods in system that REQUIRES that each delivery of > > items be entered into raw stock separately. This is why two rows in > > the table have identical details like, > > > > Supplier > > Product name > > > > But the qtys may be diff. > > > > Ect ect > > > > Now the PREPARATION dept need to internally order the RAW STOCK to > > make the final items for sale. SO they order from RAW STOCK items they > > want and this is where the RAW stock reductions come in. > > > > To keep it simple, I group the identical items available to the PREP > > team showing only the total in raw stock. They enter what they need > > and this is where I want to reduce the raw stock until I have reached > > the total ordered by the PREP team. > > > > I am having a look at array_walk to see if that can help but ask > > yourself or the list if you have any ideas how I can step through each > > returned row adjusting the qty level until I reach 0 which the posted > > value. > > > > I hope that helps > > > > Dave Carrera > > > > > > > > -- > > UK Web Hosting @ http://www.ephgroup.com > > > > > > -----Original Message----- > > From: Jason Davidson [mailto:jdavidson@zworg.com] > > Sent: 11 August 2004 19:00 > > To: Dave Carrera; php-general@lists.php.net > > Subject: Re: [PHP] Adjust two or more mysql rows ? > > > > > > Yup, confused me... > > what is it for? > > > > J > > "Dave Carrera" <dave@ephgroup.com> wrote: > > > > > > Hi List, > > > > > > I have a qty posted via a form field, lets say 46. > > > > > > In my mysql table I have two rows which I want to reduce their qty > > > fields. > > > > > > 1 row has a qty of say 13 and the second has a qty of 50, making 63 > > > in > > > total. > > > > > > So I would like to say something like if $_POST[qty] is greater than > > > row 1's qty then update row[qty] = 0, then minus what was the row qty, > > > 13 , from $_POST[qty] to new var say $newqty = $_POST[qty]-$row[qty], > > > this leaves 37. > > > > > > Then from the 2nd row take 37 from its qty of 50 leaving 13. End > > > result being row 1's qty field = 0 and row 2's qty field = 13. > > > > > > I hope this is not as clear as mud and if you need further clarity > > > to > > > be able to advise me then I will be happy to correspond. > > > > > > I thank you fully in advance for any help, logic pointers or nuggets > > > of wisdom. > > > > > > Dave Carrera > > > > > > > > > -- > > > UK Web Hosting @ http://www.ephgroup.com > > > > > > --- > > > Outgoing mail is certified Virus Free. > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004 > > > > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > --- > > Incoming mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004 > > > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004 > > > > > > > > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004 > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.736 / Virus Database: 490 - Release Date: 09/08/2004 > > >