mysql get non existing results

Question

Using php and mysql,
I have a table "subdomains" that contains subdomains for my application, depending of a category. subdomains - id, cat_id, subdomain

Some categories have subdomain and others don't have. For those that don't have subdomain, I want the subdomain to be 'www'.

Now in my subdomains table there are a few values, for example :

subdomains : id, cat_id, subdomain
             1,  6,   "sales"
             2,  7,   "infos"

Now, I have an array of cat_id, for example $aCatIds = array(1,5,6,8);

At the end of the mysql query I would like a something like this :

array(
0 => (cat_id="1", subdomain="www") ,
1 => (cat_id="5", subdomain="www") ,
2 => (cat_id="6", subdomain="sales") ,
3 => (cat_id="8", subdomain="www") ,
)

Is it possible with only one mysql query ?

I use php and tried things like this :

$stmt = "select cat_id, ifnull('www', subdomain) as subdomain
    from subdomains
    where cat_id in (". implode(',', $aCatIds) .")";

But I only get the values that are set (6 in the above example), and I don't know how to tell mysql to get the expected array.

Answer 1

To make this work, you'll have to (left) join to existing data (where the cat_id does exist). For instance, if I assume a cat table exists which the cat_id references, 't would be so:

SELECT c.id as cat_id,IFNULL(s.subdomain,'www')
FROM cat c 
LEFT JOIN subdomains s
ON s.cat_id = c.id
WHERE c.id IN (1,2,3,4);