Tag Archives: mysql

MYSQL: Get Flag Emoji Unicode from ISO 3166-1 Alpha-2 Country Codes

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

PHP: Check MySQL database to see if table, field, or value exists

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

CodeIgniter: Load View from Database (more or less)

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

Easily convert MySQL database collations and alter character sets

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

Improved database table structure for CodeIgniter sessions

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…

  `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`)

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 🙂

CodeIgniter, MySQL and User-Defined Variables

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).

Continue reading

Table/record sorting made easier for CodeIgniter

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.

Continue reading