MySQL: How to update a field only if condition is true

MySQL:  update a field only if condition is true.Update a field only if condition is true in query

Using IF statement we can do this
For example :- If i want to update user level if user score is 500+
and if user join time in 24 hour

We can add multiple condtion in IF statement

UPDATE table SET level = IF(score > 500 AND  jointime (HOUR, jointime , ‘2018-06-27 16:27:54’) > 24 , USERNEWLEVEL , level) WHERE user_id = 5

How to get single row data in CI

Result Rows

Row() returns a single result row data.  If query has more than one row, it returns only the first row of data. The result is returned as an object. Example:-

$query = $this->db->query(“YOUR QUERY”);

$row = $query->row();

if (isset($row))
echo $row->title;
echo $row->name;
If you want data in array format use row_array()

$query = $this->db->query(“YOUR QUERY”);

$row = $query->row_array();

if (isset($row))
echo $row[‘title’];
echo $row[‘name’];
echo $row[‘body’];

Generating Query Results as Row in CI

What are the ways to prevent SQL injection for a CodeIgniter based website?

To prevent SQL injections in PHP, we usually use mysql_real_escape_string() function in mysql queries

In CI there are three methods to prevent SQL injections
1) Escaping Queries
2) Query Binding
3) Active Record Class

Preventing SQL injection in Codeigniter using Escaping Query Method

$sql = ‘SELECT * FROM product WHERE product_name=’.$this->db->escape($name);
$this->db->query($sql ;

Here $this->db->escape() determines the data type so that it can escape only string data.

Preventing SQL injection in CI using Query Binding Method
db->query($sql, array(‘active’, ‘mobile’));
In Query Binding Method, you don’t have to escape the values manually as it will automatically do that for you.

Preventing SQL injection in Codeigniter using Active Record Class

Using Active Records, query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.
db->get_where(‘product’,array(‘status’ => ‘active’,’sellerId’ => ’10’));

InnerJoin in codeigniter