SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast irkengir's Avatar
    Join Date
    Mar 2006
    Location
    UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [SOLVED] $db->affected_rows always returning 0 after UPDATE query

    I thought at first this could be because its not commiting. But i inserted
    PHP Code:
    $this->db->query('COMMIT'); 
    and tried
    PHP Code:
    $this->db->autocommit(true); 
    Anyway the problem is I'm executing this bit of code:
    PHP Code:
        public function publishAction() 
        {
            
    $id = (int)$this->_getParam('id');
            if (!
    $id) {
                
    $this->illusiveResource();
            }
            
    $db Zend::registry('db');
            if(
    $affected $db->unpublishSite($id)) {
                
    $this->_redirect('/' $this->controllerName '/list/');
            } else {
                
    // this but always executes
               
    var_dump($affected); // and this is always int(0)
                
    $this->illusiveResource();
            }
            return 
    true;
        } 
    Which uses these:
    PHP Code:
        /**
         * Unpublishes a site. Returns whether the site was updated or not.
         * 
         * @param int $siteId
         * @return bool 
         */
        
    public function _unpublishSite($arg
        {
            
    $q 'UPDATE Site SET isOnline = 0, wentOffline = NOW() WHERE siteId = '.$arg[0];
            
    $result $this->qq($q__FUNCTION__);
            return 
    $this->db->affected_rows;
        }
        
    /**
         * Execute a query and throw an exception if it fails.
         *
         * @param string $q
         * @param string $name the name of the function calling qq(); use __FUNCTION__
         * @return mysqli_result
         */
        
    private function qq($q,$name
        {
            
    $status $this->db->query($q);
            
    $this->queriesExecuted++;
            if(!
    $status) {
                
    $name.= ' generated the following error: '.$this->db->error;
                throw new 
    QueryException($name,$this->db->errno,$q);
            }
            else return 
    $status;
        } 
    And yet despite all this when i go back to see if the record has been updated, it has! This problem is not isolated to this query.

    This problem has only started happening recently, so I've obviously changed something to muck it up but i've no idea.
    Last edited by irkengir; Jul 9, 2006 at 07:02. Reason: it was solved

  2. #2
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's the query ?

    What's the query that this snippet executes ? Try running the query and see what's the output. I had a similliar problem recently: I run a valid UPDATE query, and the query always returned affected_rows = 0. The problem was that actually not a single record was modified indeed - hense affected_rows = 0. Try adding a column which tracks the latest modification time: in this way eevn if you do not alter any of the "real" columns, you will still have one column that is modified:

    Code:
    UPDATE xxxxx SET xxxxxxx, updated_datetime = now WHERE xxxx

  3. #3
    SitePoint Enthusiast irkengir's Avatar
    Join Date
    Mar 2006
    Location
    UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it should always be updated because it already has NOW() in it:
    Code:
    $q = 'UPDATE Site SET isOnline = 0, wentOffline = NOW() WHERE siteId = '.$arg[0];
    NOW() is never the same, so as long as the WHERE finds a record which I know exists because has been retrieved from a SELECT just moments ago there should always be affected_rows == 1

    What I'm trying to say is that what you are asking me to do is already in place.

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Let me illustrate it with few examples

    Let me illustrate it with few examples:

    1. The following is a valid query, although this record (id=6666) does not exists

    Code:
    mysql> update `administrators` set email='tralala' where id = 6666;
    Query OK, 0 rows affected (0.13 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    2. The following is the same query plus the `updated` column:

    Code:
    mysql> update `administrators` set email='tralala', updated=now() where id = 6666;
    Query OK, 0 rows affected (0.05 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    See, nevertheless you are using the `updated` column you still get 0 changed. This is because in both queries you got Rows matched: 0

    Now, let's try the aboev with an existing record:

    Code:
    mysql> select * from `administrators` where id=2;
    +----+------------+---------------------+----------------------------------+-----------+---------------------+---------------+---------------------+---------------------+
    | id | username   | email               | password                         | is_active | last_login_date     | last_login_ip | created             | updated             |
    +----+------------+---------------------+----------------------------------+-----------+---------------------+---------------+---------------------+---------------------+
    |  2 | Supervisor | supervisor@test.com | 20bfb0165be27a455789bf28163a6f37 | yes       | 2006-06-27 11:09:05 | 10.0.10.51    | 0000-00-00 00:00:00 | 2006-07-09 00:02:25 |
    +----+------------+---------------------+----------------------------------+-----------+---------------------+---------------+---------------------+---------------------+
    1 row in set (0.00 sec)
    4. The first query on an existing record:

    Code:
    mysql> update `administrators` set email='tralala' where id = 2;
    Query OK, 1 row affected (0.08 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    See, now you got one affected because you really modified something; now let's try it again:

    Code:
    mysql> update `administrators` set email='tralala' where id = 2;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    See, now you still got 1 matched, but 0 affected rows becasue the email is already `tralala`.

    5. Let's try the `updated` column query:

    Code:
    mysql> update `administrators` set email='tralala', updated=now() where id = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    Everything's cool, one row affected; let's run it again:

    Code:
    mysql> update `administrators` set email='tralala', updated=now() where id = 2;
    Query OK, 1 row affected (0.03 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    See, nevertheless the email is not changed (because it is already `tralala`), you got one affected row due to the `updated` column.


    Now, I would advice you to run the actual query in command line mysql and see what the output will be: are there any matched rows and any affected rows.

  5. #5
    SitePoint Enthusiast irkengir's Avatar
    Join Date
    Mar 2006
    Location
    UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I knew everything you said there.
    But I'm going to humour you, and me, and execute all the queries manually:

    Code:
    mysql> SELECT
        ->       isOnline,
        ->       wentOnline,
        ->       wentOffline
        -> FROM
        ->     Site
        -> WHERE
        ->      siteId = 1
        -> ;
    +----------+---------------------+-------------+
    | isOnline | wentOnline          | wentOffline |
    +----------+---------------------+-------------+
    |        1 | 2006-07-09 13:09:29 | NULL        |
    +----------+---------------------+-------------+
    1 row in set (0.00 sec)
    
    mysql> UPDATE Site SET isOnline = 0, wentOffline = NOW() WHERE siteId = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    OK so far so good.
    Now i'm going to change it back:
    Code:
    mysql> UPDATE Site SET isOnline = 1, wentOnline = NOW(), wentOffline = NULL WHERE siteId = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    and execute this PHP:
    PHP Code:
        public function _unpublishSite($arg
        {
            
    var_dump($arg[0]);
            
    $q 'UPDATE Site SET isOnline = 0, wentOffline = NOW() WHERE siteId = '.$arg[0];
            
    $result $this->qq($q__FUNCTION__);
            
    var_dump($this->db->affected_rows);
            return 
    $this->db->affected_rows;
        } 
    PHP outputs:
    Code:
    int(1) int(0)
    and yet:
    Code:
    mysql> SELECT isOnline FROM Site WHERE siteId = 1;
    +----------+
    | isOnline |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.00 sec)
    The update has occurred. Do you see my predicament? :P

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by irkengir
    ...The update has occurred. Do you see my predicament? :P
    Yeah, definetely it is not a MySQL issue

  7. #7
    SitePoint Wizard mark_W's Avatar
    Join Date
    Mar 2004
    Location
    West Midlands, United Kingdom
    Posts
    2,631
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you using transactions by any chance?

    If so you have to call affected_rows after the query and not after commiting the transaction otherwise you can have problems.

  8. #8
    SitePoint Enthusiast irkengir's Avatar
    Join Date
    Mar 2006
    Location
    UK
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mark_W
    Are you using transactions by any chance?

    If so you have to call affected_rows after the query and not after commiting the transaction otherwise you can have problems.
    ****ing genius!

    Seems that mysqli->autocommit(true) by default. So all I had to so was set it to false and change the PHP to get the affected_rows before manually issuing a COMMIT:
    PHP Code:
        public function _unpublishSite($arg
        {
            
    $q 'UPDATE Site SET isOnline = 0, wentOffline = NOW() WHERE siteId = '.$arg[0];
            
    $result $this->qq($q__FUNCTION__);
            
    $affected $this->db->affected_rows;
            
    $this->db->query('COMMIT');
            return 
    $affected;
        } 
    Although that does mean I have to add about 30 of these for all my queries: $this->db->query('COMMIT');

    But I can cope with that. hehe thanks again.

  9. #9
    SitePoint Wizard mark_W's Avatar
    Join Date
    Mar 2004
    Location
    West Midlands, United Kingdom
    Posts
    2,631
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh...Glad its working!


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
  •