I was faced with a field in the database that had a comma separated list of INT’s with a space after the comma. The application searched this field to generate the recordset to display on a web page.

First I tried using LIKE, with something along this line: ($state_id is being passed by PHP)

    `assoc_states` LIKE '%$state_id%';

Well, as you can guess if I was searching for an INT less than two numbers I would get anything that had that digit in it. (Ex. – Searching for ‘%4%’ would also give me entries of 4, 14, 24, 34, 42, 44, etc.)

I may have been able to use RLIKE, but I didn’t fully understand it and there were no good examples of doing what I needed.

That was when I stumbled across FIND_IN_SET. Here is what has to say about it:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

// output -> 2

Here is what I ended up with: ($state_id is being passed by PHP)

    FIND_IN_SET('$state_id', `assoc_state`) > 0;


This weekend I needed a couple of MySQL query items that were out of the ordinary, so I thought I would write about them.

ADDDATE or DATE_ADD both work the same, but came in very handy. I was struggling with an application where I need to have the date 42 days from now. PHP doesn’t really have anything that is easy to use, and after a few tries I decided to turn to MySQL. Below is what has to say on these:

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().

        -> '1998-02-02'
        -> '1998-02-02'

Having Microsoft Fonts on Linux (Ubuntu)

I have been using Ubuntu in the office lately. However, I did not have the standard fonts that you would find on a Windows system for use in documents. So I simply used the following method to add them:

# sudo aptitude install msttcorefonts

For these fonts to display in 96dpi you may also need to add a line in your /etc/X11/xorg.conf:

# sudo vi /etc/X11/xorg.conf

Add this line in the Section “Device” somewhere before EndSection

Option     "DPI" "96x96"

Then you will need to either restart X or reboot to apply the settings. After the restart/reboot you can use the following command to see if you are using 96dpi.

# xdpyinfo | grep resolution

Using bitmask permissions like Unix in PHP application

Have you ever wanted to give read/write/Delete type of permissions to users or other items within a PHP application?  Why not use Unix-style file permissions (read/write/execute for example)?  A PHP implementation can be used for any kind of permissions in scripts and applications.

Use powers of two (the reasoning behind this will become apparent when you see the decoding function) to define your scheme.  Here’s a sample permission scheme:

define('PERMISSION_READ', 1);
define('PERMISSION_ADD',  2);

Now that your permissions scheme is set you can apply it as needed. Using user permissions as an example lets say you wanted to create a user that has permissions to read and delete a log file. Using the above definitions, you would set the user’s permission to “9”.

Properly decoding that permission and apply it to a user can be easily achieved using the following function:

 * Correct the variables stored in array.
 * @param    integer    $mask Integer of the bit
 * @return    array
function bitMask($mask = 0) {
    if(!is_numeric($mask)) {
        return array();
    $return = array();
    while ($mask > 0) {
        for($i = 0, $n = 0; $i <= $mask; $i = 1 * pow(2, $n), $n++) {
            $end = $i;
        $return[] = $end;
        $mask = $mask - $end;
    return $return;

What this function does is break down the permission ($mask – the bit mask) into its components that are powers of 2 and return them in an array. If you did a print_r() of the above functions return with our example of “9” you would get:

    0 => 1, // READ
    1 => 8  // DELETE

Now that you have your array of permissions, you could use the “in_array” function to check if a user has permission to perform a requested action. Take a look at this sample code:

// ...
$_ARR_permission = bitMask(9);
// ...
if(in_array(PERMISSION_READ, $_ARR_permission)) {
    // [...]
else {
    echo 'Access denied.';

Now you have a simple bitmask permission to use in your apps.