I'm working on a new site and I wanted an easy way to read config for a user from the database. Earlier I've stored all the config variables in the user table, but this leaves a problem when developing; adding a new parameter means that you'll have to add a new entry in the user table. So, why not use one table for config and then have a default value stored in the database as user 0?

 EXPLAIN config;
+-------------+--------------+------+-----+---------+-------+
| FIELD       | Type         | NULL | Key | DEFAULT | Extra |
+-------------+--------------+------+-----+---------+-------+
| configUser  | INT(11)      | NO   |     | 0       |       |
| configVar   | VARCHAR(255) | NO   |     | NULL    |       |
| configValue | VARCHAR(255) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
 
 SELECT * FROM config;
+------------+----------------+-------------+
| configUser | configVar      | configValue |
+------------+----------------+-------------+
|          0 | streamsPerPage | 30          |
|          1 | streamsPerPage | 10          |
+------------+----------------+-------------+

The problem this is when you're trying to get the config value from the database for a user and the value is set, if you don't want to run two queries that is:

SELECT * FROM config WHERE configUser = 0;
SELECT * FROM config WHERE configUser = 4384;

function getConfig($uid = 0) {
  $ids = array("0", => 0, "1", $uid);
  $ids = array_unique($ids);
  foreach ( $ids as $id ) {
   $sql = "select * from config where configUser = $id";
   $data = executeSql($sql);
   foreach ( $data as $q ) {
     $return[$q[configVar]] = $q[configValue];
   }
  }
 return $return;
}

Could be used, but you do want a more elegant way, right?

SELECT * FROM config WHERE configUser = (SELECT max(configUser) FROM config WHERE configUser = 4384 OR configUser = 0);

Could also work, but sub-queries are CPU hoggers. So what I came up with (with some help from my friends):

SELECT
 config.configVar,
 IFNULL(userconfig.configvalue, config.configvalue) AS configValue
FROM
 config LEFT JOIN config as userconfig
    on
     (userconfig.configVar = config.configVar AND userconfig.configuser=4384)
  WHERE
    config.configuser=0";

This gives us the following code:

function getConfig($uid = 0) {
  $sql = "select
                 config.configVar,
                 ifnull(userconfig.configvalue, config.configvalue) as configValue
          from
                 config
          left join
                 config as userconfig on (userconfig.configVar = config.configVar and userconfig.configuser=$uid)
          where
                 config.configuser=0";
  $data = executeSql($sql);
  foreach ( $data as $q ) {
    $return[$q[configVar]] = $q[configValue];
  }
 return $return;
}