SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Zend_Db_Table increment value by one

    Let's say I need to increment value in a table row by one. In MySQL it can be done easily:

    Code:
    UPDATE table SET counter = counter+1 WHERE id = 5
    How can I do that in Zend_Db_Table? I tried the following with no success:

    PHP Code:
        public function incrementCounter($id)
        {
            
    $data = array('counter' => 'counter+1');
            return 
    $this->update($data$this->getAdapter()->quoteInto('id = ?'$id));
        } 
    Please help.

  2. #2
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another question if I may. What if I want to increment counter in more rows at once. In normal MySQL I would just use WHERE IN statement:

    Code:
    UPDATE table SET counter = counter+1 WHERE id IN (5, 7, 15)
    But it appears there isn't equivalent for WHERE IN or WHERE BETWEEN in Zend_Db_Table, just for plain WHERE. I tried the following, again with no success:

    PHP Code:
        public function incrementCounter(array $ids)
        {
            
    $data = array('counter' => 'counter+1');
            
    $ids implode($ids', ');
            
    $ids '(' $ids ')';
            return 
    $this->update($data$this->getAdapter()->quoteInto('id IN (?)'$ids));
        } 
    The $ids argument I passed to the method looked like this:

    PHP Code:
    $ids = array(5715); 

  3. #3
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course I tried searching the Zend documentation but it is very lacking in this area. There isn't a word about incrementing or WHERE IN/WHERE BETWEEN.

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that impossible?

  5. #5
    SitePoint Member
    Join Date
    Feb 2009
    Location
    Here, There, & Everywhere
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to check out the documentation for Zend_Db_Table_Select in the API guide. It is extremely flexible in what you can do when accessing the database.

  6. #6
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by risoknop View Post
    Let's say I need to increment value in a table row by one. In MySQL it can be done easily:

    Code:
    UPDATE table SET counter = counter+1 WHERE id = 5
    How can I do that in Zend_Db_Table? I tried the following with no success:

    PHP Code:
        public function incrementCounter($id)
        {
            
    $data = array('counter' => 'counter+1');
            return 
    $this->update($data$this->getAdapter()->quoteInto('id = ?'$id));
        } 
    Please help.
    You wouldn't want to use Zend_Db_Table for this. The only way that I can see this being accomplished would be to use Zend_Db_Table::find() to fetch the rows based on the primary key -- I'm assuming id is the primary key if not use Zend_Db_Table::fetchAll() -- to get all the rows in question then integrate over each row incrementing counter and saving the row. It's much better to just write the update sql and execute it using the the adapter.
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  7. #7
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by risoknop View Post
    Another question if I may. What if I want to increment counter in more rows at once. In normal MySQL I would just use WHERE IN statement:

    Code:
    UPDATE table SET counter = counter+1 WHERE id IN (5, 7, 15)
    But it appears there isn't equivalent for WHERE IN or WHERE BETWEEN in Zend_Db_Table, just for plain WHERE. I tried the following, again with no success:

    PHP Code:
        public function incrementCounter(array $ids)
        {
            
    $data = array('counter' => 'counter+1');
            
    $ids implode($ids', ');
            
    $ids '(' $ids ')';
            return 
    $this->update($data$this->getAdapter()->quoteInto('id IN (?)'$ids));
        } 
    The $ids argument I passed to the method looked like this:

    PHP Code:
    $ids = array(5715); 
    You can just specify additional conditions using the Zend_Db_Select:rWhere if your dealing with a small set.
    PHP Code:
    $select $table->select();
    $select->where('id =?'$id1)
           ->
    orWhere('id =?'$id2)
           ->
    orWhere('id =?'$id3); 
    Question though, why are you using $this->getAdapter()->quoteInto() with integers o.O. That's probably your problem.
    Code:
    where in('1,2,3')
    sorry for double posting I don't like the number 666 ._.... bad omens D:!
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  8. #8
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I used something like this for incrementing:

    PHP Code:
        private function _getDb()
        {
            return 
    Zend_Registry::get('dbAdapter');
        }

        public function 
    incrementCounter($id)
        {
            
    $sql "UPDATE table SET counter = counter + 1 WHERE id = $id";
            
    $stmt = new Zend_Db_Statement_Pdo($this->_getDb(), $sql);
            
    $stmt->execute();
        } 

  9. #9
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I shouldn't have used $this->getAdapter()->quoteInto() with integers, that's a bad mistake.


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
  •