Today I had to figure out the most efficient way to generate Emoji from ISO 3166-1 Alpha-2 country codes such as US, GB, etc. I opted to go for a for 100% SQL solution.
The most efficient way to convert each ASCII letter to their Unicode Regional Indicator Symbol Letter that I determined is provided below (simplified)…
Continue reading
Google reporting that your OpenCart generated sitemap submission has invalid dates? Here is a quick fix we applied to adjust the date problem directly in the products table… Continue reading
This procedural function will tell you if the table you are looking for is found within your currently selected MySQL database. Optionally, it will also determine if a column exists within the specified table. You can even pass the a value as the optional third parameter to see if the field’s value is also what you expect. Continue reading
Simply replace table and column
SELECT COUNT(*), * FROM `table` GROUP BY `column` HAVING COUNT(*) > 1;
I have seen a few people asking how to load a view from a database (or something other than a physical file in the “views” folder).
In my particular case, a client wants the ability to edit HTML and TEXT email templates. Without giving them direct access to editing the physical files, I decided to move the views being used for email to the database instead. Continue reading
I can’t remember what all I looked at for references, but this completed tool will generate the SQL commands necessary to convert your database, tables and fields from any collation and character set into another.
It goes without saying to make a backup of your database before running such commands. If you don’t make a backup first, shame on you 😛 Continue reading
Load the helper:
$this->load->helper(‘database’);
Call the function:
$enums = field_enums(‘table_name’, ‘field_name’);
For a while I had been wanting to author a generic database table model for CodeIgniter. Here are the results of those efforts… Continue reading
I had a problem where CodeIgniter sessions stored in the database were creating multiple session_id’s (each time the session library was called) for the same user.
Here is how my session configuration is defined…
$config['sess_cookie_name'] = 'my-session'; $config['sess_expiration'] = 60*60*2; //2 hours $config['sess_expire_on_close'] = TRUE; $config['sess_encrypt_cookie'] = FALSE; $config['sess_use_database'] = TRUE; $config['sess_table_name'] = 'sessions'; $config['sess_match_ip'] = FALSE; $config['sess_match_useragent'] = TRUE; $config['sess_time_to_update'] = 300;
Here is the updated table structure…
CREATE TABLE IF NOT EXISTS `sessions` ( `session_id` varchar(32) NOT NULL default '0', `ip_address` varchar(16) NOT NULL default '0', `user_agent` varchar(255) NOT NULL, `last_activity` int(10) unsigned NOT NULL default '0', `user_data` text NOT NULL, PRIMARY KEY (`session_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
My solution… First, I increased the user_agent field to 255 characters in length. Then, since session_id is always 32 characters in length, I reduced that from 40. If you have any other suggestions, feel free to leave a comment.
Hope this helps someone else having the same problem with CodeIgniter sessions that are being stored in the database 🙂
Here is a quick snippet to pulls the month and year from any date field in a MySQL table…
SELECT DATE_FORMAT(date_added, '%b %Y') AS sDate, COUNT(post_id) AS iCount FROM blog_posts GROUP BY sDate ORDER BY sDate DESC
| sDate | iCount |
|---|---|
| Jan 2012 | 4 |
| Dec 2011 | 16 |
| Nov 2011 | 0 |
| Oct 2011 | 12 |
Mostly for my reference, but hope it helps someone else 😉
The lesson for today is… how to sort records sequentially on a numeric column and provide the ability to move records up and down in the existing sort order.
There are 2 functions I use for manual record sorting to change the display order in admin/user areas. The following example is based on a “pages” table and the commands are located in a model. For this example, the table has the following fields: page_id (int), page_sort (smallint), page_title (varchar), and page_content(text).
In a project I am currently working on, I needed a way to efficiently allow sorting of the records in an admin area while preserving the functionality of a search function (that passes the query in the url) and the pagination library. Initially it started off as a section of each method where sorting was allowed (record lists of things like countries, states, etc). My sorting routines finally ended up being put into their own helper.