Thread

This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.

The active forums are here.

IS NOT NULL using database helper

June 07, 2009 8:52am

Subscribe [9]
  • #1 / Jun 07, 2009 8:52am

    Huji

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

  • #2 / Jun 09, 2009 4:05pm

    Huji

    12 posts

    Any comments?

  • #3 / Jun 09, 2009 4:09pm

    Nicholai

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

  • #4 / Jun 09, 2009 4:15pm

    Phil Sturgeon's avatar

    Phil Sturgeon

    2889 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;
  • #5 / Jun 10, 2009 9:45am

    Huji

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

  • #6 / Jun 10, 2009 12:12pm

    Phil Sturgeon's avatar

    Phil Sturgeon

    2889 posts

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

  • #7 / Jun 10, 2009 3:59pm

    Huji

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

  • #8 / Jun 10, 2009 4:49pm

    drewbee's avatar

    drewbee

    480 posts

    $query = $this->db->get_where('users', array('surname IS NOT' => NULL));
  • #9 / Jun 10, 2009 6:16pm

    Jondolar

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

  • #10 / Jun 10, 2009 6:59pm

    Phil Sturgeon's avatar

    Phil Sturgeon

    2889 posts

    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

  • #11 / Jun 10, 2009 7:48pm

    drewbee's avatar

    drewbee

    480 posts

    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.

  • #12 / Jun 11, 2009 4:08pm

    Huji

    12 posts

    $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 `users` WHERE `surname` IS NOT

    The “NULL” part is missing.

  • #13 / Jun 11, 2009 4:58pm

    drewbee's avatar

    drewbee

    480 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($where, FALSE);        
            $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.

  • #14 / Jun 11, 2009 6:20pm

    CtheB

    69 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…

  • #15 / Jun 11, 2009 7:06pm

    drewbee's avatar

    drewbee

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

ExpressionEngine News

#eecms, #events, #releases