Re: [PHP] Adjust two or more mysql rows ?
Yup, confused me... what is it for? J Dave Carrera [EMAIL PROTECTED] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Adjust two or more mysql rows ?
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:[EMAIL PROTECTED] Sent: 11 August 2004 19:00 To: Dave Carrera; [EMAIL PROTECTED] Subject: Re: [PHP] Adjust two or more mysql rows ? Yup, confused me... what is it for? J Dave Carrera [EMAIL PROTECTED] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Adjust two or more mysql rows ?
Dave Carrera wrote: 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 think you have a seriously flawed database structure. There is no row 1 in a database, you know that, right? Pretty sure you can do what you're after, though, you'll just have to involve about four different queries and some PHP magic in the middle... -- John Holmes php|architect - The magazine for PHP professionals - http://www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Adjust two or more mysql rows ?
I don't think you can have a single mysql query do what you're wanting. Break it into mutiple queries. -Original Message- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Adjust two or more mysql rows ?
I think John's right. Your schema sounds like it needs some serious work, especially if you're storing identical details with every single transaction. Go back and study relational database basics before trying to create an inventory management system or you'll end up in a world of hurt. -Ed -Original Message- From: Dave Carrera [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 11, 2004 11:14 AM To: 'Jason Davidson' Cc: [EMAIL PROTECTED] Subject: RE: [PHP] Adjust two or more mysql rows ? 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:[EMAIL PROTECTED] Sent: 11 August 2004 19:00 To: Dave Carrera; [EMAIL PROTECTED] Subject: Re: [PHP] Adjust two or more mysql rows ? Yup, confused me... what is it for? J Dave Carrera [EMAIL PROTECTED] 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Adjust two or more mysql rows ?
Dave Carrera 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.
Do they need to know that when they order 20 widgets, that 10 came from
the shipment on Tuesday and 10 came from the shipment on Monday? And
that the Monday shipment is all used up?
You could still use a better database structure, I think, but something
along these lines may work.
$qty = $_POST['quantity'];
$query = SELECT shipment_id, quantity FROM shipments WHERE product_id =
'XXX' AND quantity 0 ORDER BY shipment_date ASC;
$result = mysql_query($query) or die(mysql_error());
while($qty 0 $row = mysql_fetch_assoc($result))
{
if($qty $row['quantity'])
{
$qty = $qty - $row['quantity'];
$query = UPDATE shipments SET quantity = 0 WHERE shipment_id =
{$row['shipment_id'] AND product_id = 'XXX';
}
else
{
$r = $row['quantity'] - $qty;
$qty = 0;
$query = UPDATE shipments SET quantity = {$r} WHERE shipment_id =
{$row['shipment_id'] AND product_id = 'XXX';
}
$result2 = mysql_query($query) or die(mysql_error());
}
You'll have to grab what shipments or other information you want as you
loop through the results, of course. I'd recommend you use InnoDB tables
so you can wrap the whole thing in a transaction, also (or use a
database abstraction layer that simulates transactions).
--
John Holmes
php|architect - The magazine for PHP professionals - http://www.phparch.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Adjust two or more mysql rows ?
Sort of... Here is a sort of layout of my table data: Id | prodname | qty | datein 1 toy1332004-08-11 13:41:26 2 toy2122004-08-11 14:41:26 3 toy1162004-08-11 15:41:26 4 toy3222004-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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: 11 August 2004 19:00 To: Dave Carrera; [EMAIL PROTECTED] Subject: Re: [PHP] Adjust two or more mysql rows ? Yup, confused me... what is it for? J Dave Carrera [EMAIL PROTECTED] 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
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 [EMAIL PROTECTED] wrote: Sort of... Here is a sort of layout of my table data: Id | prodname | qty | datein 1 toy1332004-08-11 13:41:26 2 toy2122004-08-11 14:41:26 3 toy1162004-08-11 15:41:26 4 toy3222004-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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: 11 August 2004 19:00 To: Dave Carrera; [EMAIL PROTECTED] Subject: Re: [PHP] Adjust two or more mysql rows ? Yup, confused me... what is it for? J Dave Carrera [EMAIL PROTECTED] 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
