CODEWITHSUNDEEP
phpmysqlcodeigniter
jonboy
jonboy

Reputation: 2749

Codeigniter Select and (conditional) Update Query

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.

Upvotes: 1

Views: 1800

Answers (2)

mickmackusa
mickmackusa

Reputation: 47934

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.

Code:

$data = [
    'item_description'  => $gDescription,
    'item_preview_link' => $gPreviewLink,
    'item_thumbnail'    => $gThumbnail,
    'item_pageCount'    => $gPageCount,
    'g_data'            => '1'
];

$this->db->where([
    'item_id' => $itemId,
    'g_data'  => 0
]);
$this->db->set('g_data_timestamp', 'NOW()', false);
$this->db->update('item', $data);
// return (bool)$this->db->affected_rows();

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 third parameter.

Upvotes: 0

DFriend
DFriend

Reputation: 8964

$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') {...

Upvotes: 2

Related Questions