active record where field IS NULL error?

[eluser]KrizzAngel[/eluser]
with this code:

$this->db->where('name',$this->input->post('name'));
$this->db->where('section',NULL);
$ilan = $this->db->count_all_results();

i got this error when running it:

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 'WHERE `name` = 'teddy' AND `section` IS NULL' at line 2

SELECT COUNT(*) AS `numrows` WHERE `name` = 'teddy' AND `section` IS NULL

any problem from my code?? is NULL not allowed on 'where' clause in AR??

[eluser]BaCeTo[/eluser]
Code Igniters's main db handler (i.e. Active Record, unless you have installed something else, like IgniterQuery for example) will automatically try to escape the parameters. In terms of security this is fine. But in your case, the NULL value, escaped will be implemented as
" ... WHERE `section` = 'NULL' ... "

Thus you will not have the results you expect
Anyway, there is a workaround.
There is a third parameter of the method db::where with a default value of false.
If set to true it will pass the data unescaped.
Try again the query, but altered like that:

$this->db->where('section IS NULL', '', true);

Thisway section will be matched against NULL values, not the "NULL" string itself. The second parameter will be passed as an unescaped empty string, which will affect nothing.

If you take a look at the documentation of ActiveRecord you will find some helpful explanations
http://ellislab.com/codeigniter/user-gui...tml#select

Best of luck with your coding.

Greetings
BaCeTo

[eluser]KrizzAngel[/eluser]
hmm.. that doesnt work either.. huhu.. originally i got this code:

$query =$this->db->query("select * from tblprof where name='".$this->input->post('name')."' AND section IS NULL ");
$ilan = $query->num_rows();
// $this->db->where('name',$this->input->post('name'));
// $this->db->where('section IS NULL','',TRUE);
// $ilan = $this->db->count_all_results();
if($ilan>=1){ *update query* }
else { *insert query* }

*note the code above is from my other post

it seems that the IS NULL function isnt working coz my code always executing insert query, thats why i switched it to AR but no luck.. i got db error.. can someone help me asap.. tnx

[eluser]Armchair Samurai[/eluser]
Two things: first, you haven't specified the table you're running the query on. Secondly, you need to set the third parameter to FALSE, not TRUE, if you want to avoid CI escaping the clause.

Code:

$this->db->where('name', $this->input->post('name'));
$this->db->where('section IS NULL', NULL, FALSE); // third param set to FALSE

$ilan = $this->db->count_all_results('tblprof'); // include table name, or use from()

[eluser]KrizzAngel[/eluser]
ok so i use my AR again lol.. didnt notice that i forgot to select a table.. when everything seems right still it doesnt work..

// $query =$this->db->query(“select * from tblprof where name=’”.$this->input->post(‘name’).”’ AND section IS NULL “);
// $ilan = $query->num_rows();
$this->db->where(‘name’,$this->input->post(‘name’));
$this->db->where(‘section IS NULL’,NULL,FALSE);
$ilan = $this->db->count_all_results('tblprof');
if($ilan>=1){ *update query* }
else { *insert query* }

the code still executes insert query..