IF statement in an MySQL query

[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:

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?

[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.

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

[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);

[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?

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

[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.

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

[eluser]CroNiX[/eluser]
You didn't show the full line for the db:elect(), but try setting the 2nd parameter of db:elect() to FALSE.

Code:

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

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

[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.

[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: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,