EllisLab text mark
Advanced Search
1 of 2
1
   
IS NOT NULL using database helper
Posted: 07 June 2009 08:52 AM
Joined: 2009-05-27
17 posts

Hi. When I wanted to get the number of rows in a database with their “surname” field still empty, I simply ran:

$query = $this->db->get_where(‘users’, array(‘surname’ => Null));
$data[‘reg_count’] = $query->num_rows;

Now I want to do the opposite: Get the number of rows that have their surname field filled with text. In terms of SQL syntax, the above example procudes “SELECT * FROM (`users`) WHERE `surname` IS NULL”. What I want now is something like “SELECT * FROM (`users`) WHERE `surname` IS NOT NULL”. However, I want to use database helper for that rather than hardcoding it myself. How can I do that?

 
Posted: 09 June 2009 04:05 PM   [ # 1 ]   [ Rating: 0 ]
Joined: 2009-05-27
17 posts

Any comments?

 
Posted: 09 June 2009 04:09 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2009-03-30
29 posts

Set a variable and write the string, then use active record for the rest. Here’s an example from the User Guide:

$where = “name=‘Joe’ AND status=‘boss’ OR status=‘active’”;
$this->db->where($where);

 
Posted: 09 June 2009 04:15 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 posts
$this->db->where('surname IS NOT NULL');
$query $this->db->get('users');
$data[‘reg_count’] $query->num_rows

It’s possible you could do it this way too:

$query $this->db->get_where('users', array('surname !=' => NULL));
$data[‘reg_count’] $query->num_rows
 Signature 

————————
Blog | Twitter | GitHub | BitBucket
————————-
PyroCMS - open source modular CMS built with CodeIgniter
PancakeApp - Simple, hosted invoicing/w project management

 
Posted: 10 June 2009 09:45 AM   [ # 4 ]   [ Rating: 0 ]
Joined: 2009-05-27
17 posts

narayanis: your comment didn’t help.

Phil Sturgeon: Your second code “could” be what I wanted; however, I tested it and it throws an error on the SQL statement.

 
Posted: 10 June 2009 12:12 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 posts

Did you try the first? And what is the error? Remember to try debugging your SQL before just saying “it doesnt work”.

 Signature 

————————
Blog | Twitter | GitHub | BitBucket
————————-
PyroCMS - open source modular CMS built with CodeIgniter
PancakeApp - Simple, hosted invoicing/w project management

 
Posted: 10 June 2009 03:59 PM   [ # 6 ]   [ Rating: 0 ]
Joined: 2009-05-27
17 posts

Phil, I’m sure the first solution is working. However, I’m not simply looking for a solution here. I’m looking to see if there is a way to use Database helper to run negative clauses without hard coding them (your first solution hard codes the IS NOT NULL part). If not, I’m going to suggest this feature to be added.

About debuging the SQL statement, I don’t think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn’t support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?

 
Posted: 10 June 2009 04:49 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-28
511 posts
$query $this->db->get_where('users', array('surname IS NOT' => NULL)); 
 Signature 

~ 4 All the Right Reasons ~

 
Posted: 10 June 2009 06:16 PM   [ # 8 ]   [ Rating: 0 ]
Joined: 2003-10-22
209 posts

The $this->db->select() function will protect your field names with a backtick unless you pass a FALSE as the second parameter.  The $this->db->where() and $this->db->get_where() do not so you can pass pretty much any text string as they key in the array.  In fact, the docs even show examples where the key has custom operators so drewbee’s technique should be valid for future versions as well.

 Signature 

Template Driven PHP Shopping Cart Software

 
Posted: 10 June 2009 06:59 PM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-11
2987 posts
Huji - 10 June 2009 07:59 PM

About debuging the SQL statement, I don’t think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn’t support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?

How do we know its wrong or how to fix it if you dont show us the query it produces? wink

 Signature 

————————
Blog | Twitter | GitHub | BitBucket
————————-
PyroCMS - open source modular CMS built with CodeIgniter
PancakeApp - Simple, hosted invoicing/w project management

 
Posted: 10 June 2009 07:48 PM   [ # 10 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-28
511 posts
Phil Sturgeon - 10 June 2009 10:59 PM
Huji - 10 June 2009 07:59 PM

About debuging the SQL statement, I don’t think it has a place here. The SQL statement created by the second solution is wrong, and this means that CodeIgniter doesn’t support that idea. Should I repeat that it leads me to the idea of asking for this new feature to be supported?

How do we know its wrong or how to fix it if you dont show us the query it produces? wink

Yup. This one is chalked up to user error.

 Signature 

~ 4 All the Right Reasons ~

 
Posted: 11 June 2009 04:08 PM   [ # 11 ]   [ Rating: 0 ]
Joined: 2009-05-27
17 posts
drewbee - 10 June 2009 08:49 PM
$query $this->db->get_where('users', array('surname IS NOT' => NULL)); 

This doesn’t work either. The SQL statement it generates is like:

SELECT FROM `usersWHERE `surnameIS NOT 

The “NULL” part is missing.

 
Posted: 11 June 2009 04:58 PM   [ # 12 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-28
511 posts

That is what I get for assuming. Huji is correct in this one gentlemen.

You can see the check in the function _where in the db_active_record.php file.

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

So… this works:

$where = array('forum_id' => null);
        
$this->db->where($whereFALSE);        
        
$this->db->get('forum'); 

Outputs : SELECT * FROM (`forum`) WHERE `forum_id` IS NULL

We can do the positive check, however we cannot do the negative

None of the following work:

$where = array('forum_id IS NOT' => null);
$where = array('forum_id !=' => null); 

After digging through active record, and finding the following function _has_operator in DB_driver.php the function called above uses this:

function _has_operator($str)
    
{
        $str 
trim($str);
        if ( ! 
preg_match("/(\s|<|>|!|=|is null|is not null)/i"$str))
        
{
            
return FALSE;
        
}

        
return TRUE;
    

To properly check against not null, we do the following, and can also do the following for IS NULL as well:

$where = array('forum_id IS NULL' => null,
               
'forum_id IS NOT NULL' => null);
$this->db->get('forum'); 

Outputs: SELECT * FROM (`forum`) WHERE `forum_id` IS NULL AND `forum_id` IS NOT NULL


Granted, I would expect it to more work like this:

$where = array('forum_id IS' => null,
               
'forum_id IS NOT' => null); 

You would simply have to change the code in _has_operator and _where accordingly.

 Signature 

~ 4 All the Right Reasons ~

 
Posted: 11 June 2009 06:20 PM   [ # 13 ]   [ Rating: 0 ]
Joined: 2008-10-18
76 posts

lol nice one drewbee…

I know you are helping that guy out because he wants to use the AR helper…

But wouldn’t it be easier to just use:

$query $this->db->query("SELECT ..."); 

AR helper only makes the code less readable, more complicated and slower…

 
Posted: 11 June 2009 07:06 PM   [ # 14 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-28
511 posts

I would disagree that it makes it slower. It is merely a query builder, and does very little analysis on it.  I personally think the AR helper is a godsend to PHP querying. Query syntax can get very, very ugly if there are a lot of conditionals, and I always prefer coldfusion over php when it comes to writing queries, but that was before CI & AR.

I think it makes it far superior in terms of readability and am willing to take whatever micro hit on speed that it does any day.

 Signature 

~ 4 All the Right Reasons ~

 
Posted: 12 June 2009 03:40 AM   [ # 15 ]   [ Rating: 0 ]
Joined: 2009-05-27
17 posts

Thanks drwbeee, it really helped; not only my question was answered, but also I got a better concept about AR. Now, do you think we should suggest this feature in the feature request section?

 
1 of 2
1