Hasitha

A typical database "select query" in Codeigniter using Active Record can look like the following

$this->db->where('product_name', 'iPhone'); $query = $this->db->get('products');

This is great, as long as you know EXACTLY the name of the product you are searching for. But, what if you just want to see whether the product name starts with a certain word, ends with a certain word, or contains a certain word? For that, you can use the following

$this->db->like('product_name', 'Apple', 'before'); // Selects all products starting with the word "Apple" $this->db->like('product_name', 'iPhone', 'after'); // Selects all products ending with the word "iPhone" $this->db->like('product_name', 'phone', 'both'); // Selects all products containing the word "phone"

This covers most of the situations you'd run into while doing select queries. However, occasionally, you need to do queries that involve much more complex select criteria. And the only way to accommodate for these is by using regular expressions.

I know a lot of you cringe at the thought of regular expressions. However, they can really help you out of tough spots, and are well worth learning. A good starting point is http://www.regular-expressions.info/quickstart.html

Let's say you wanted to select products that have names starting with a number. Using regular expressions, you can write the following Active Record query.

$this->db->where('product_name RLIKE', "'^[0-9].*'", FALSE); return $this->db->get('products');

For one of the projects I'm working on, I had to write a script that fetches RSS feeds from various sources and stores them in a database periodically. My initial thought was to use SimplePie, and write a PHP script which then gets called repeatedly via CRON. However, given the number of sources I'd have to pull posts from etc, I decided instead to go with Python. The idea was to write the feed parsing bit in Python and then to display these posts using Codeigniter (since that's what the full application was written in)

So, I wrote a quick python script with Universal Feed Parser, which worked great. But, I started noticing that some of the sources I was pulling data from had a lot of UTF-8 characters.

Now, Python is brilliant at handling UTF-8. But PHP... not so much (which hopefully would change with PHP6). I hadn't run into UTF-8 related issues with any of the applications I've developed till now. So this was a bit of a curve ball.

The way to tackle this involves a few steps

Install the "mbstring" PHP extension

Configure your php.ini file to use the extension

Change the database and each of the tables over to UTF-8 (Under MySQL, you can use "utf8_general_ci")

Modify various helpers in Codeigniter to take into account UTF-8 (more details in the liks below)

Optionally, write your own helper to convert UTF-8 characters to regular single byte characters

This sounded like a pretty daunting task at first, but the following blog posts helped me out immensly. Everything listed above is clearly explained in the posts below. Also, pay close attention to the comments under those posts as well, because they have fixes for some "gotchas" the original posters had missed.

http://philsturgeon.co.uk/news/2009/08/UTF-8-support-for-CodeIgniter This covers the database side of things quite a bit

http://www.haughin.com/2010/02/23/building-utf8-compatible-codeigniter-applications This covers more of the PHP side of things and the changes to the Codeigniter helpers