How to prevent, counter or compensate for, an empty/NULL field (MySQL) returning an empty set...

Question

I'm learning PHP and MySQL to get a hang of server-side scripting and for personal interest/development. The first 'program,' beyond 'hello world' seems to be writing ones' own blog.

This is a little beyond me yet and so thought I'd start with a contacts-database type. So far so good, but (and there's always the but!) I'm trying to format a presentation in the form that the contact's name is formatted as a mailto: link.

$query = mysql_query("
    SELECT CONCAT(fname,', ',sname) AS name, email.address AS email
    FROM contacts, emails 
    WHERE contacts.contactID=email.contactID");

while ($row = mysql_fetch_array($query)) {

 echo "<li><a href=\"mailto" . $row['email'] . "\"> . $row['name'] . "</a></li>";

}

This is fine until I dare to enter a contact that has no email address in the Db, at which point the obvious lack of an email.contactID to equal the contacts.contactID rears its head.

I realise that I should be able to deal with this but...I can't think of how (yay to insomnia! @.@ ). Any suggestions would be appreciated.

Answer 1

Use an Outer join to join the contacts and email tables:

SELECT 
      CONCAT(fname,', ',sname) AS name, email.address AS email 
FROM 
    contacts c
    left outer join emails e on (c.contactID = e.contactID)

This will return you all contacts and their email address's if they have one, other wise address will be null.

Answer 2

You want a left (aka left outer) join.

select concat(c.fname, ' ', c.sname) as name, e.address as email
from contacts c
left join emails e
on (c.contactID = e.contactID)