Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2010
    Posts
    100
    Plugin Contributions
    0

    Default How to find updated rows from a query?

    (I looked around and can't find a better place to ask this question, hope someone here knows the answer)

    I am writing customized code to update some status in a table in zen cart, something like:

    $sql = "update xxx set status = 1 where xx=xx";

    I use the default $db object for this, like:
    $result = $db->Execute($sql);

    Now, I want to know how many records are actually updated here. How do I know that? -- I don't see anything like "mysql_affected_rows" in the query_factory.php. The only thing is RecordCount(), but I think that is for select (at least per the php manual).

    Thanks!

  2. #2
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default Re: How to find updated rows from a query?

    What exactly are you attempting??
    Zen-Venom Get Bitten

  3. #3
    Join Date
    Apr 2010
    Posts
    100
    Plugin Contributions
    0

    Default Re: How to find updated rows from a query?

    It is:
    After execute a standard update statement via:
    $result = $db->Execute($sql);
    how do I know how many rows were actually updated in database?

  4. #4
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: How to find updated rows from a query?

    If you can't get it afterward, why not get it beforehand? You could do a "select count(*) as count from ... where xx=xx". Then take the value from 'count'. Then do your update.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Apr 2010
    Posts
    100
    Plugin Contributions
    0

    Default Re: How to find updated rows from a query?

    Quote Originally Posted by DrByte View Post
    If you can't get it afterward, why not get it beforehand? You could do a "select count(*) as count from ... where xx=xx". Then take the value from 'count'. Then do your update.
    That's different -- I actually see many places in zen cart that having this problem, and here is the problem:

    We first do a select, for example, we find we need to update 5 rows.
    When a update to update them. We assume these 5 rows are updated.
    However, during that time period, some other process may change the record, so we may end up with only updated 4 records (for example).
    If we don't check this again, we will wrongly return and tell the calling code that 5 rows are updated (which is not true, in this case, only 4 row are updated).
    So a good practice in database is always to check exactly how many rows are updated, after a update sql. I hope zen-cart's query factory provide this function. :-(

  6. #6
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: How to find updated rows from a query?

    Quote Originally Posted by Cindy2010 View Post
    We first do a select, for example, we find we need to update 5 rows.
    When a update to update them. We assume these 5 rows are updated.
    However, during that time period, some other process may change the record, so we may end up with only updated 4 records (for example).
    If we don't check this again, we will wrongly return and tell the calling code that 5 rows are updated (which is not true, in this case, only 4 row are updated).
    So a good practice in database is always to check exactly how many rows are updated, after a update sql.
    While in certain cases checking the number of rows affected is "good practice", it can be irrelevant in many cases too, other than maybe looking for a non-zero value.

    Also consider the fact that if you're performing, for example, and update on 5 rows but 2 of them actually already have the value you're updating them to, then mysql_affected_rows will only return "3", which is another completely different but totally valid case vs the race-condition you were referring to above.

    So, it's not an exact science to rely on mysql_affected_rows in every situation. It must be used carefully.

    Nevertheless, there is no support for mysql_affected_rows in the current query_factory.
    However, you can easily call it directly:
    Code:
    $check_affected = mysql_affected_rows();
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  7. #7
    Join Date
    Apr 2010
    Posts
    100
    Plugin Contributions
    0

    Default Re: How to find updated rows from a query?

    Thanks DrByte!
    This works for me:
    mysql_affected_rows($db->link)

  8. #8
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: How to find updated rows from a query?

    In your current PHP version, adding $db->link is probably a bad idea, since $db->link isn't necessary (because mysql_affected_rows() automatically operates on the last "link" used, which would be exactly what you need anyway), and because $db->link isn't always going to be a public variable and so shouldn't be relied upon, and indeed may not even exist in all cases.

    Hence the suggestion of simply calling mysql_affected_rows() directly as stated in my post above.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  9. #9
    Join Date
    Apr 2010
    Posts
    100
    Plugin Contributions
    0

    Default Re: How to find updated rows from a query?

    Quote Originally Posted by DrByte View Post
    In your current PHP version, adding $db->link is probably a bad idea, since $db->link isn't necessary (because mysql_affected_rows() automatically operates on the last "link" used, which would be exactly what you need anyway), and because $db->link isn't always going to be a public variable and so shouldn't be relied upon, and indeed may not even exist in all cases.

    Hence the suggestion of simply calling mysql_affected_rows() directly as stated in my post above.
    Thanks for the follow up! Yes, I just realized there is no way to fine control the "connection pool" in PHP, so maybe let the system auto select the link will be a better idea.

  10. #10
    Join Date
    Jul 2009
    Location
    Texas
    Posts
    209
    Plugin Contributions
    2

    Default Re: How to find updated rows from a query?

    Please note that as of Zen Cart 1.5.3 you should use mysqli_affected_rows();

 

 

Similar Threads

  1. v153 How to change product display from single column to multiple columns and rows?
    By pageblair in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 27 Sep 2014, 06:24 AM
  2. v138a Get amount of returned rows with $query->EOF ?
    By joecooper in forum General Questions
    Replies: 2
    Last Post: 15 Feb 2012, 11:20 PM
  3. How to change rows from 3 to more?
    By mubasher in forum Templates, Stylesheets, Page Layout
    Replies: 3
    Last Post: 9 Dec 2011, 09:54 PM
  4. Help with SQL query returning multiple rows
    By acetate in forum General Questions
    Replies: 3
    Last Post: 9 Mar 2008, 03:23 AM

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
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR