SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to read intermmediate value before final commit?

    Dear All,
    I have a list of product which I run a loop and first I do a insert statement. Following that I am going to update fiels called avarageValue,totalStock and stockValue. So for example first I have a line to insert with productID=123 so once inserted I read what is the current totalStock=10 and stockValue=120 and averageValue=12 of productID=123. So say for this new productID=123 totalStock=1 and stockValue=15. So after inserting this line my update of totalStock=11, stockValue=135 and averageValue=12.28 right. The problem is that when comes to my second,third and so on line with productID=123 where I need to read the latest value but I am not able to read and when I read I get totalStock=10 and stockValue=120 and averageValue=12. Just to add on when I run the loop all the values will be in on single commit where I only commit at the end of the loop? So any solution please ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would you still have a problem if you could somehow eliminate the loop?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear r937,
    I dont think so I can eliminate the loop either? So what other option you have then? Any other idea?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    well, i don't really know what you're doing, so it's hard for me to suggest anything

    you haven't given us a lot to go on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear R937,
    Ok maybe my earlier explaination may not be so clear. So let me rephrase for you. Basically I have datagrid with various rows each have a different productID, totalstock and stockValue. So based on that I will run a loop to insert each of the datagrid row value into the database.There is one tables which keep the details of each product with the fields being productID,totalStock, stockValue and averageValue. So each time I will read from the database what is the current value of each of the 3 fields. Following that I will sum with the new value. So the problem if for the same productID there is more then one row for the second row I cant get the latest updated value which was updated by a previous row. What I get is the old value before the first row did the updates ? I hope I am clearer this time. Sorry for earlier.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you do not need a loop for the inserts -- do it all in one statement
    Code:
    INSERT INTO mytable VALUES 
    ( 1, 'a' ) , ( 2, 'b' ) , ( 3, 'c' ) , ( 4, 'd' ) , ...
    you do not need a redundant totals table, you could actually compute the totals and averages in a query every time you need them, but if you feel you need the extra table, then you would issue a single UPDATE query right after the INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear r937,
    If I dont run through the loop how can I get all the value to be like inserted into the way you said INSERT INTO mytable VALUES
    ( 1, 'a' ) , ( 2, 'b' ) , ( 3, 'c' ) , ( 4, 'd' ) , ... right? I guess I must run the loop in order to the the insert values first ? So how about my updates what must I do you mean to keep a temp variable is it?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you run the loop in your form data, building up a single INSERT statement

    i can't help you with the update because i don't know your table layouts

    please show the SHOW CREATE TABLE for your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear r937,
    Below is my tblProduct which keeps the current productAveragePrice,productTotalStock and productTotalAmount. So this is the table which I read and latter do an update on.
    CREATE TABLE `tblproduct` (
    `productID` int(5) NOT NULL auto_increment,
    `productCode` varchar(100) NOT NULL,
    `productName` varchar(100) NOT NULL,
    `productAveragePrice` double(10,2) NOT NULL default '0.00',
    `productType` enum('Accessory','Coupon','Imei') NOT NULL,
    `productTotalStock` int(10) NOT NULL default '0',
    `productTotalAmount` double(10,2) NOT NULL default '0.00',
    PRIMARY KEY (`productID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2228 DEFAULT CHARSET=latin1

    Below is the tblStock where when I run the for loop I insert each line in this table. Hope now things are clearer rite?

    CREATE TABLE `tblstock` (
    `stockID` int(10) NOT NULL auto_increment,
    `productID` int(5) NOT NULL default '0',
    `stockSIQ` varchar(20) NOT NULL default '0',
    `costPrice` double(10,2) NOT NULL default '0.00',
    `stockPrice` double(10,2) NOT NULL default '0.00',
    PRIMARY KEY (`stockID`,)
    ) ENGINE=InnoDB AUTO_INCREMENT=36631 DEFAULT CHARSET=latin1

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    show me an example of an INSERT statement for multiple rows of the same product being added to the tblstock table

    then try to write a SELECT query that calculates the new avergae price, total stock, and total amount for that product, using only the tblstock table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Feb 2007
    Posts
    874
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear r937,
    Below is a sample of insert for productID=303 into tblStock.
    11133,303,7167,9.60,9.60
    11134,303,7168,9.60,9.60
    11135,303,7169,9.60,9.60
    11136,303,7170,9.60,9.60
    11137,303,7171,9.60,9.60
    Below is the current value of tblProduct for productID=303
    303,CPDG,CPLoad,9.60,Load,2100,20160.00

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, here is a query which re-calculates stock levels
    Code:
    SELECT p.productID
         , p.productCode
         , p.productName
         , AVG(s.costPrice) AS productAveragePrice
         , p.productType
         , COUNT(*) AS productTotalStock
         , AVG(s.costPrice)
          *COUNT(*) AS productTotalAmount
      FROM tblproduct AS p
    INNER
      JOIN tblstock AS s
        ON s.productID = p.productID
    GROUP
        BY p.productID
    maybe the formula for some column is different, but you can see the approach, using GROUP BY and aggregate functions

    fix this so it works correctly and then i'll show you how to update the product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •