$db->affected_rows always returning 0 after UPDATE query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

$db->affected_rows always returning 0 after UPDATE query

Post by Ollie Saunders »

I thought at first this could be because its not commiting. But i inserted

Code: Select all

$this->db->query('COMMIT');
and tried

Code: Select all

$this->db->autocommit(true);
Anyway the problem is I'm executing this bit of code:

Code: Select all

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:

Code: Select all

/**
	 * 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I can see it returning 0 if the line I marked is failing...

Code: Select all

$db = Zend::registry('db');
        if($affected = $db->unpublishSite($id)) {  // this is evaluating to false??
            $this->_redirect('/' . $this->controllerName . '/list/');
        } else {
            // this but always executes
             var_dump($affected); // and this is always int(0)
            $this->illusiveResource();
        }
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Thanks for the reply but the good guys at sitepoint fixed it for me.
Its quite interesting though:
http://www.sitepoint.com/forums/showthread.php?t=401484
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Don't you love getting an answer that works. That is awesome man. Glad you got it fixed.
Post Reply