Codeigniter Select and (conditional) Update Query

Question

I am using Codeigniter 3, PHP and MySQL.

I'm trying to select a record from a MySQL database, and depending on the result run an update query.

If result = 0, update.
If result = 1, do nothing.

My code so far is;

public function addData($itemId) {
    $gDescription = 'test';
    $gPreviewLink = 'test';
    $gThumbnail = 'test';
    $gPageCount = 'test';

    $this->db->select('g_data');
    $this->db->from('item');
    $this->db->where('item_id', $itemId);
    $query = $this->db->get();
    $result = $query->result(); 
    // var_dump($result) returns array(1) { [0]=> object(stdClass)#22 (1) { ["g_data"]=> string(1) "0" } }

        $data = array(
        'item_description'  => $gDescription,
        'item_preview_link' => $gPreviewLink,
        'item_thumbnail'    => $gThumbnail,
        'item_pageCount'    => $gPageCount,
        'g_data'            => '1',
        'g_data_timestamp'  => 'NOW()'
        );
        // if result = 0 update
        if($result == '0') {
        $this->db->where('item_id',$itemId);
        $this->db->update('item', $data);
    }
        }

Is there any reason the data won't update in my database? I'm not receiving any error messages.

Any help would be appreciated.

is $result numeric or character? top example shows numeric but you're == to a character '0' — xQbert
– xQbert, Commented Apr 6, 2017 at 15:24

DFriend · Accepted Answer · 2017-04-06 17:14:43Z

2

$query->result() returns an array of objects where each object is a row from the table. (As you can see in the var_dump in your comments)

Without other changes your conditional should be

if($result->g_data == '0') { ...

That said, you should have checked earlier in the method that the database atually returned results. Also, you don't need more than one row so don't use result() use 'row()' instead

...
$query = $this->db->get();
// The following will set $result to the value of "g_data" if there 
// are rows and to "0" if there are none.
$result = $query->num_rows() > 0 ? $query->row()->g_data: '0';
...

If you do the above then the conditional can remain as you have it coded

if($result == '0') {...

answered Apr 6, 2017 at 17:14

DFriend

8,9741 gold badge16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2025-11-01 22:36:41Z

Executing the SELECT query means making an unnecessary extra trip to the database. Just build your 0 criteria into your UPDATE query -- if it is not satisfied, then no rows will be affected.

update()'s first 3 parameters are "table name", "set clause", and "where clause".

Code:

return $this->db
    ->set('g_data_timestamp', 'NOW()', false)
    ->update(
        'item',
        [
            'item_description'  => $gDescription,
            'item_preview_link' => $gPreviewLink,
            'item_thumbnail'    => $gThumbnail,
            'item_pageCount'    => $gPageCount,
            'g_data'            => '1'
        ],
        [
            'item_id' => $itemId,
            'g_data'  => 0
        ]
    )
    ? $this->db->affected_rows()
    : 0;

It's all done in a single query execution. I also took the liberty of demonstrating how to pass a MySQL function as a value using set()'s second parameter.

Collectives™ on Stack Overflow

Codeigniter Select and (conditional) Update Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related