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!