CodeIgniter Forums

Full Version: Deleting selected records
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I've decided to convert an app that I coded procedurally to CI. I'm learning a lot but still have many gaps in my knowledge of CI (and php for that matter). In this case I'd like to delete all records from table 'mz' where the first char of the 'title' field is lower case alpha. This is a test copy of the database that I've messed up while 'learning'. My proposed function:

<MyCode>
public function cleanLibrary () {
        $titleList=$this->db->get('mz');
        foreach ($titleList as $title) {
            if ( ctype_lower($title['title'][0]) && ctype_alpha($title['title'][0]) ) {
                $this->db->delete($title);
            }
        }
}
</MyCode>

It wouldn't be so bad to have to switch to a saved copy of my test database if I screwed up this function - but I'd really like to know if it's as simple as this or if I'm missing something important. Like, will this even work or does it do something I did not intent.
You can try this:
PHP Code:
$this->db->where('title REGEXP "^[a-z]" 'NULL,FALSE);
$this->delete('mz'); 

Explanation:
Usually, the where function in the query builder class will accept 2 parameters: the field and the value.
But it also accepts a custom MySQL statement as the first parameter. Then you must set the second parameter to NULL, and add a third parameter to tell the query builder that it should not try to "escape" the query.
Thanks Wouter60. I've integrated that snippet into my code and had my model just count and return the number of the rows found. It gives me 659 which is the number of records in the table. I don't have much experience with REGEXP but does that expression only look at the first char of 'title'? Of course, there are lower case alpha characters in every title so I suspect not.
@Wouter60, So I put it in an online regex tester and it worked properly catching only first-char lower case alphas. Now I need to debug the surrounding code to see where I screwed up. This is my model function BTW:

<MyCode>
public function libraryClean() {

$this->db->select('*');
$this->db->where('title REGEXP "^[a-z]" ', NULL, FALSE);
$list=$this->db->get('mz');
return $list->num_rows();
}
</MyCode>

Thanks for the help.