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


Problem :

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! @[email protected] ). Any suggestions would be appreciated.



Solution :

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)

Mysql Tutorials

Mysql Howto..

How to structure a MYSQL table with lots of data?

How to “Delete rows ignoring errors” in MySQL?

UTC Problems, not sure how to fix them (PHP/MySQL)

How do I create a function in MySQL that is not attached to a schema/database?

How to call a function from result set in MySQL Workbench?

How to store the timestamp as decimal(17,3) in mysql?

How much does GRANT permission checking affect MySQL performance?

How to get information from 2 tables at once in PHP and MySQL?

How to update multiple mysql database rows with same field names?

How to backup a huge mysql database in PHP with lowest usage (busy) of linux server?

how to backup and restore mysql database using java

How to insert multiple rows in mysql using codeigniter

How to match a word from string and retrieve data in SQL Query and PHP?

How do I check if my mysql database causes slow down

MySQL: How to pull information from multiple tables based on information in other tables?

How to update values to table fields in MySql from html form?

How to update multiple fields

How to know when MySQL is reaching memory limits of server?

How to put value from MySQL cell into a variable? [closed]

How to optimise join between two MySQL tables?

Replace a part of a string with mySQL - how to remove middle initial from first name field

How to add a record to a MySQL Database from JDBC

How to protect sql query when a php variable is empty [duplicate]

How to insert current date in database mysql

MySQL: How to insert a record for each result in a SQL query?

In MySQL how should I alter my syntax to add a 'WHERE' clause

How to add suctom column based on some condition in query

How to rewrite this php slugify function to mysql?

How to do string comparison?

How to replace Mysql enum values for sorting