Selecting rows with null columns

[eluser]gabe[/eluser]
Hi

I have a table with a column called datacash_ref, it can be null. Only after someone successfully pays does a reference number get entered.

About half the entries are NULL and the other half have references. I am trying to select only those that are empty (null).

These don't work:

Code:

$this->db->get_where( 'ci_bookings', array( 'datacash_ref' => '' ) );

Code:

$this->db->get_where( 'ci_bookings', array( 'datacash_ref' => 'NULL' ) );

Code:

$this->db->get_where( 'ci_bookings', array( 'datacash_ref' => FALSE ) );

What's the correct way to approach this, please?

Kind regards

[eluser]adamp1[/eluser]
I believe the correct way is to say

Code:

$this->db->get_where('ci_bookings', array('datacash_ref IS NULL'));

That's how I 'think' my applications do it

[eluser]depthcharge[/eluser]
isnull() I believe is what you are looking for

Lee

[eluser]gabe[/eluser]
thanks, is that MySQL specific code? Or is it in the active record class? I'm trying to keep all my code database agnostic.

Cheers

[eluser]adamp1[/eluser]
Which person are you talking to? I don't know how you would use the isnull() thing, but I know mine is proper MySQL code.

[eluser]gabe[/eluser]
adamp1, thank you for your advice. I would like to avoid using MySQL specific code.

[eluser]adamp1[/eluser]
Then I don't know how you would do it.

Just looking at at the active record class in CodeIgniter it also uses the IS NULL property to search for NULL values.

Code:

if ( ! $this->_has_operator($k) && is_null($key[$k]))
{
  // value appears not to have been set, assign the test to IS NULL
  $k .= ' IS NULL';
}

[eluser]gabe[/eluser]
Right finally worked it out. adamp1 that code you posted didn't work, but it got me experimenting. This is what worked for me:

Code:

$query = $this->db->get_where( $this->table, array( 'datacash_ref' => NULL ) );

[eluser]adamp1[/eluser]
Umm thats odd, I'm sure it did. Oh well never mind, you seem to have found the answer. And I learnt something also