CODEWITHSUNDEEP
php
Jamie Warren
Jamie Warren

Reputation: 45

Subtract a number from a SQL Query in code-igniter framework

I am wondering how I can subtract 1 from a field in my database i tried the following but it just set it to "-1", did not actually subtract it.

// update listing status to sold
this->db->update("listings", array("cases" => "cases" - 1,

Upvotes: 0

Views: 595

Answers (2)

Rashmy
Rashmy

Reputation: 125

$this->db->set('cases','`cases`-1',FALSE);
$this->db->where('id', $id);/*whatever condition you want*/
$this->db->update('yourtablename');

Heres a link from codeigniter userguide: https://codeigniter.com/user_guide/database/query_builder.html

Upvotes: 1

Vivek
Vivek

Reputation: 2755

Your code inserts -1 in the database because in PHP, "cases" - 1 evaluates to -1.

Try echo ("any_string"-1);. Read String conversion to numbers for details.

In CodeIgniter, you cannot use the the format of the function $this->db->update you have used directly to implement this query since the update function escapes all values passed to it.

However, you can use the following code to implement the same.

//Passing false as third parameter turns off escaping
$this->db->set('cases', 'cases - 1', false);
$this->db->where('id', $id); //Your where condition
$this->db->update('listings');

Alternatively, you can write the query manually and use the query function.

$sql = "UPDATE `listings` SET `cases` = `cases` - 1 WHERE [your where condition]";
$this->db->query($sql);

Upvotes: 1

Related Questions