IF statement in an MySQL query

IF statement in an MySQL query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: IF statement in an MySQL query (/showthread.php?tid=24311)

Pages: 1 2

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]rebellion[/eluser]
I have the following query:

Code:
SELECT receivers.*, (IF(bills.paid=1, MAX(bills.due), 0)) AS due, SUM(bills.amount) AS amount FROM receivers INNER JOIN bills ON receivers.id = bills.receiver GROUP BY receivers.id

In my model, I use the active record class. The code looks like this:

Code:
$this->db->select('receivers.*, IF(bills.paid=1, MAX(bills.due), 0) AS due, SUM(bills.amount) AS amount FROM receivers INNER JOIN bills ON receivers.id = bills.receiver GROUP BY receivers.id');

$q = $this->db->get();

This causes CodeIgniter to throw me this error:

Quote:A Database Error Occurred
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS due, SUM(bills.amount) AS amount FROM receivers INNER JOIN bills ON receivers' at line 1

SELECT `receivers`.*, IF(bills.paid=1, MAX(bills.due), `0)` AS due, SUM(bills.amount) AS amount FROM receivers INNER JOIN bills ON receivers.id = bills.receiver GROUP BY receivers.id

For some reason, CI insists on escaping the last part of the IF statement: IF(bills.paid=1, MAX(bills.due), `0)`

Why? And how can I fix this?

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]überfuzz[/eluser]
I think AR lets you add rowbreakers...

Code:
$this->db->select('receivers.*,

 IF(bills.paid=1,

 MAX(bills.due), 0) AS due,

 SUM(bills.amount) AS amount

 FROM receivers

 INNER JOIN bills ON receivers.id = bills.receiver

 GROUP BY receivers.id');

$q = $this->db->get();

Try and see.

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]rebellion[/eluser]
It didn't work for me.. Is the only solution not to use the active record class?

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]davidbehler[/eluser]

Code:
$this->db->select('receivers.*, IF(bills.paid=1, MAX(bills.due), 0) AS due, SUM(bills.amount) AS amount FROM receivers INNER JOIN bills ON receivers.id = bills.receiver GROUP BY receivers.id', FALSE);

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]rebellion[/eluser]
Thansk waldmeister! I wasn't aware that I had to set the second parameter to false, even though I remember reading it earlier today. This means I must escape any input myself, right?

IF statement in an MySQL query - El Forum - 11-05-2009

[eluser]davidbehler[/eluser]
Right.
If the second parameter is set to FALSE, then the data in the first param will not be escaped.

IF statement in an MySQL query - El Forum - 01-21-2014

[eluser]Musaddiq Khan[/eluser]
This is my query.

Code:
$this->db->select("item.*,IF( item.Is_group = '1',  .65 ,  .75 ) AS margin, parent_cat.Name as ParentCategory

when I run it gives the following error.

Quote:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '65`, `.`75` ) AS margin, `parent_cat`.`Name` as ParentCategory, `sub_cat`.`Name`' at line 1

SELECT DISTINCT `item`.*, IF( item.Is_group = '1', `.`65`, `.`75` ) AS margin,

Any way to use these values not the table fields

IF statement in an MySQL query - El Forum - 01-21-2014

[eluser]CroNiX[/eluser]
You didn't show the full line for the db: Confused

elect(), but try setting the 2nd parameter of db: Confused

elect() to FALSE.

Code:
$this->db->select(the_query, FALSE);

which will prevent it from escaping the identifiers by adding tickmarks, "`"

IF statement in an MySQL query - El Forum - 01-21-2014

[eluser]jonez[/eluser]
Shouldn't this:

Code:
IF( item.Is_group = '1',  .65 ,  .75 )

Be:

Code:
IF( item.Is_group = '1',  '0.65' ,  '0.75' )

Every value should be encapsulated. As a general rule I would not recommend disabling the built in escaping. There's a chance you will open yourself up to SQL injections. It is very easy to inject code (or dump table data) on a public form that isn't escaped properly.

IF statement in an MySQL query - El Forum - 01-21-2014

[eluser]CroNiX[/eluser]
Jonez, there is a difference between escaping identifiers with backticks and escaping variables using blah_real_escape_string(), which AR does automatically when using their built in methods. The 2nd parameter to select only disables the table/field identifier tickmarks, which CI can't correctly add in to more complex statements due to it's limitations. Like, there is no db: Confused

elect_if() statement, so it doesn't know how to handle it manually. So in those cases, you'd want to set the 2nd parameter of select to FALSE so it won't add them in at all. You can add them in manually if you want.

Notice the inappropriately placed tickmarks within the IF clause in the error:

Code:
SELECT DISTINCT `item`.*, IF( item.Is_group = '1', `.`65`, `.`75` ) AS margin,