Page 1 of 1

$db->affected_rows always returning 0 after UPDATE query

Posted: Sat Jul 08, 2006 7:11 pm
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.

Posted: Wed Jul 12, 2006 12:09 am
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();
        }

Posted: Thu Jul 13, 2006 10:37 am
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

Posted: Thu Jul 13, 2006 11:08 am
by RobertGonzalez
Don't you love getting an answer that works. That is awesome man. Glad you got it fixed.