When people trip and fall in a horror movie.
USING REGULAR EXPRESSIONS IN CODEIGNITER DATABASE QUERIES
A typical database “select query” in Codeigniter using Active Record can look like the following
$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);
With this, you can utilise the full power of regular expressions in your database queries to select records that follow specific and complex patterns.
Handling UTF-8 with Codeigniter
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.
This covers the database side of things quite a bit
This covers more of the PHP side of things and the changes to the Codeigniter helpers
Offers a helper function that converts incoming POST data into MySQL compatible UTF8 and convert outgoing text into HTML UTF8 streams.
Running on 2 hours of sleep. I blinked and may have teleported from Queen station to Finch station :s