Emulating ADD_TO_SET and REMOVE_FROM_SET in MySQL

Posted by on

I regularly use the SET data type in MySQL. The SET data type is ideal to store multiple values in a column. As an example, you can have a column in a table that stores possible ways to go to work. The column could be defined as SET(‘car’,’public_transport’,’bike’,’walk’). The nice thing about a SET column is that is can contain multiple values, so somebody that typically goes to work by car, but sometimes comes to work by bike, can have the value ‘car,walk’.

You can read more about the use of the SET data type on the MySQL website. The article contains some hints on how to add and remove values from a SET, but as Beat Vontobel points out in a post on his website, there are some issues with those approaches. He also describes a better solution, which leads to creating stored function for ADD_TO_SET and REMOVE_FROM_SET.

My internet provider does not allow stored functions, but you can mimic the behavior by simply using his proposed solution directly into the UPDATE statement.

ADD_TO_SET:

UPDATE set_test SET myset = 
CONCAT_WS(',', IF(myset = '', NULL, myset), 'value')
WHERE ...

REMOVE_FROM_SET:

UPDATE set_test SET myset =
TRIM(BOTH ',' FROM REPLACE(CONCAT(',', myset, ','),
CONCAT(',', 'value', ','), ','))
WHERE ...

Update on How to implement Facebook Connect on a PHP website

Posted by on

A few weeks ago, I blogged about my first tests with Facebook Connect. Since then, Facebook has made it a bit easier by introducing the Facebook Query Language, or FQL.

FQL allows you to use a SQL-style interface to more easily query the Facebook social data. As an example, you can use statements like

SELECT name, pic FROM user WHERE uid=211031 OR uid=4801660

to get the name and picture of specific users.

Details about FQL can be found on the Facebook Developers website. To implement it in PHP, you use the following code:

$fbid = $facebook->user;
echo $fbid;
if ( $fbid )
  {
  $query = sprintf("SELECT name, pic_square_with_logo FROM user WHERE uid = %s",$fbid);
  $user_details = $facebook->api_client->fql_query($query);
  }

...

<? if ( $user_details[0]["pic_square_with_logo"] != ''
  { ?>
  <img src="<?=$user_details[0]["pic_square_with_logo"]?>" />
  <? } ?>

You can test FQL queries with the Facebook API test tool.