how can I return all users with names that DON'T begin with a letter?

[eluser]got 2 doodle[/eluser]
The title says it all, I want to return all users that have chosen to have a user name that does not start with a letter.

This is used for a series of anchor's to allow selection of all users with names that start with A,B,C... etc.. Each anchor also has a count of the users within that letter group.

So in the controller I have A-Z working.

Code:

for ($i='65'; $i<='90'; $i++)
                    {
                    $letter = chr($i);
                    $temp = $this->band_data->count_where_bandname_starts_with($letter);
                    $data ['count_'] ["$letter"] = $temp;
                    }

in my model

Code:

function count_where_bandname_starts_with($letter = NULL){
      $query = $this->db->where('active = 1');
      $query = $this->db->where('group = 4');
     if($letter <> NULL){
     $query = $this->db->like('username', $letter,'after');
        }
    $query = $this->db->get($this->config->item('backendpro_table_prefix').'users');
    if ($query->num_rows() < 0){return '0'; } else { return $query->num_rows();} ;
}

So what can I do to get all the folks who use wierd names (these are names of musical groups)
like
1of_four or 2cool4words or whatever!

any help would be much appreciated

doodle

[eluser]jcopling[/eluser]
well, there does not appear to be anything in the DB Active Record class to provide you with anything like does_not_start_with, however you can achieve the same thing by using the standard where function like this:

Code:

function count_where_bandname_starts_with($letter = NULL){
      $query = $this->db->where('active = 1');
      $query = $this->db->where('group = 4');
      if($letter <> NULL){
          $query = $this->db->where("username NOT LIKE '$letter%'");
      }
      $query = $this->db->get($this->config->item('backendpro_table_prefix').'users');
      if ($query->num_rows() < 0){return '0'; } else { return $query->num_rows();} ;
}

Hope that helps.

[eluser]got 2 doodle[/eluser]
Ya thanks, I saw the NOT LIKE syntax, I have achieved the desired result with this code

In a model file

Code:

function count_where_bandname_starts_num(){
/* get all the band names */
    $query = $this->db->where('active = 1');
       $query = $this->db->where('group = 4');
       $query = $this->db->get($this->config->item('backendpro_table_prefix').'users');
    if ($query->num_rows() < 0){$all = NULL; } else { $all = $query->num_rows();} ;
    if (!$all) { return 0; } else {
    $bucket = 0;
            for ($i='65'; $i<='90'; $i++)
                        { $scoop = $this->count_where_bandname_starts_with(chr($i));
                          $bucket = $bucket + $scoop;
                        }
    }                        
    return $all - $bucket;
}

So this returns everthing that doesn't fit in the other user groupings (which is what I want).

But I wonder if my hack style is missing a key feature in mysql or php or CI?

Anything like matching for [A-Z] ?? like a regex

Thanks,
doodle

[eluser]jcopling[/eluser]
Using my example again you could do something like this to only match Alpha characters at the beginning of the field:

Code:

$query = $this->db->where("username LIKE '[a-z]%'");

or alternatively to select something NOT in range:

Code:

$query = $this->db->where("username LIKE '[^a-z]%'");

[eluser]got 2 doodle[/eluser]
OK
I tried and tried and tweaked and tweaked and then a new approach

Code:

//This doesn't work
function get_only_number_bands($num,$offset){
   $query = $this->db->where('active = 1');
   $query = $this->db->where('group = 4');
   $query = $this->db->not_like('username', '[A-Z]','after');
   $query = $this->db->get($this->config->item('backendpro_table_prefix').'users',$num,$offset);
   if ($query->num_rows() > 0){return $query->result();}
            
}

This returns the number of bands that start with [0-9]

Code:

function count_where_bandname_starts_num(){
    $query = $this->db->where('active = 1');
       $query = $this->db->where('group = 4');
      $query = $this->db->like('username', '0','after');
       for ($i='1'; $i<='9'; $i++)
                        { $query = $this->db->or_like('username',$i,'after');}
    $query = $this->db->get($this->config->item('backendpro_table_prefix').'users');
    if ($query->num_rows() > 0){return $query->num_rows();}
}

This returns the data from the table

Code:

function get_only_number_bands($num,$offset){
   $query = $this->db->where('active = 1');
   $query = $this->db->where('group = 4');
   $query = $this->db->like('username', '0','after');
       for ($i='1'; $i<='9'; $i++)
                        { $query = $this->db->or_like('username',$i,'after');}
    $query = $this->db->get($this->config->item('backendpro_table_prefix').'users',$num,$offset);
    if ($query->num_rows() > 0){return $query->result();}
            
}

So this seems to work fine, but I would be interested in a cleaner approach. I tried some REGEXP stuff in my mySQL queries but nothing worked, it could also have something to do with my version of mySQL.

doodle

[eluser]Aea[/eluser]
Sub String? I don't know how to do it in active record since it's too weak for anything but the most basic queries.