Developer tips and tricks, how to information, etc.

mySQL CONCAT_WS() for select lists

Source: Jopa -
Published: 2021-02-05

Sometimes you have to merge the contents of a couple of mySQL columns if they are not  NULL.

Enter CONCAT_WS() Concatenate With Separator. It allows you to concat or join a liat of columns on a separator like CONCAT() though the separator is listed first and it skips NULL values.

Here I was looking for a nice way to pull the records for categories that have content in them while returning select list friendly member names and category names. While CONCAT() may be enough, this drops NULL values and makes a nice clean list.

  CONCAT_WS(' ', t1.firstname, t1.lastname) AS member_id, 
  CONCAT_WS(' ', t2.title, t2.title) AS category_ids, 
FROM content 

JOIN members AS t1 ON = content.member_id 
JOIN categories AS t2 ON = content.category_ids


add comment

Custom PHP function arrayCols()

Source: Jopa -
Published: 2021-02-05

PHP coding trick, ever wanted more than one column from an array? The php built in function array_columns() is nice, though when you want two or more colums from an array this little function will get them for you.

  function arrayCols(array $arr, $keys) {
    if (!is_array($keys)) $keys = [$keys];
    return array_map(function ($item) use ($keys) {
      $out = [];
      foreach($keys as $key){
        $out[$key] = isset($item[$key])?$item[$key]:false;
      return $out;
    }, $arr);


add comment