mysql if row doesn't exist, grab default value

I'm making a preferences module for a system so users can define their own preferences for certain parts (stuff like lay-out, color-scheme, homescreen etc.). Every preference in the table has got a default value (in case the user hasn't defined one yet) and once the user has changed their preferences the system should use that user-defined value.

I'm having trouble with selecting the desired value, my query works with the user-defined value, but sadly it returns null when the user hasn't defined a preference yet.

I have 2 tables

table **preferences**:
id
name
default_value

table **preferences_defined**:
id
prefe开发者_运维技巧rence_id
user_id
defined_value

I want to create a function to easily select the desired value by giving the user_id of the person to whom the preference applies and the name of the preference. At the moment my query looks like this:

SELECT IF(ISNULL(defined_value),default_value,defined_value) AS result
FROM preferences
LEFT JOIN preferences_defined ON preferences_defined.id = preferences.id
WHERE user_id = 17
AND name = "menu_items"

I guess the WHERE user_id = 17 part is where things go wrong because when there is no defined value available it means there also isn't a column named user_id. I need to find a way to make this work. So, I need to select the defined value if it exist (given the user_id and the name of the preference), if not, it will have to return the default value.

WHERE (user_id = 17 OR user_id IS NULL) AND name = "menu_items"

And just to clean up the rest of the query:

The id in preferences defined doesn't need to be there, use a combined key of preference_id and user_id instead.

If the id of preferences was named preference_id, the long ON statement could be replaced with USING(preference_id)

The IF function can be replaced with COALESCE(defined_value, default_value)

If I where you I would do one/two queries.

So you would get:

$query = "SELECT * FROM preferences_defined WHERE user_id = 17 AND name = 'menu_items'"

And then in PHP:

$rows = mysql_num_rows($query);
if($rows == 0){
$query = "SELECT * FROM preferences WHERE name = 'menu_items'"}

This way your right query will always be in the $query var. Hope this helps!