MySQL FIND_IN_SET

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)

SELECT
    *
WHERE
    `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 MySQL.com 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.

SELECT
    FIND_IN_SET('b','a,b,c,d');
 
// output -> 2

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

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

MySQL ADDDATE or DATE_ADD

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 MySQL.com 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().

SELECT DATE_ADD('1998-01-02', INTERVAL 42 DAY);
        -> '1998-02-02'
SELECT ADDDATE('1998-01-02', INTERVAL 42 DAY);
        -> '1998-02-02'

Zend Studio IDE knowledge nuggets!

I recently realized a few things that I didn’t know about the Zend Studio IDE. As many know I have used the Zend Studio IDE for PHP development for some time. All along I have been using the templates built into the IDE, and never realized that they could be changed since I had no need. My coding standard for years has been the same as the default templates built into the Zend Studio. However, my new employer has a large percentage of coders using Vi who had a different standard in relation to white space than I did, and they were not open to changing their coding standard to meet my Zend Studio preference.

So I had a dilemma. How could I continue to produce speedy PHP using the IDE if I had to type out functions, classes, and “if” statements 100%? (I feel that I save a significant amount of time using the template auto complete shortcuts.) The alternative was to continually edit these constructs after allowing the code completion to insert them.

Continue reading Zend Studio IDE knowledge nuggets!

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_DENIED', 0);
define('PERMISSION_READ', 1);
define('PERMISSION_ADD',  2);
define('PERMISSION_UPDATE', 4);
define('PERMISSION_DELETE', 8);

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;
    }
    sort($return);
    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:

array(
    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.

Upgraded a server to PHP 5 from PHP 4

Everything went pretty smooth while upgrading a server for a customer. I was moving them from Fedora Core 3 to Fedora Core 4. This also meant that PHP 5 was installed instead of PHP 4, and MySQL 4.1 was installed instead of MySQL 3.23. All was working smoothly except one of the websites on the server was having some major issues. The pages would not display, and $HTTP_POST_VARS and $HTTP_GET_VARS were not working correct.

I did not design the site, so I was not familiar with how the $_POST and $_GET were used within the code. After opening the files I quickly saw the problem. In the PHP.ini there is a new setting that, by default, only looks for the short forms of these predefined variables. In other words, it doesn’t know what $HTTP_POST_VARS or $HTTP_GET_VARS are. But it recognizes $_POST and $_GET with no problem.

After changing the setting in the PHP.ini all is working fine. Luckily the previous programmer did an ‘ok’ job.

The setting in the PHP.ini that needs to change is:

register_long_arrays = Off

This is located in the “Data Handling” section about 1/3rd of the way down the file. (If you followed the default installation of Fedora Core 4.)