How to replace NULL values in a MySQL SELECT statement

Sometimes when you are SELECTing data from MySQL, some of your columns may have NULL data values and NULL’s don’t display well to end-users, so it may be best to replace the NULL’s with a string of text instead such as:

  • “Not defined”
  • “Undefined”
  • “Default value”

You could do this in PHP when you are echoing out your data, but it’s a lot easier to do it your MySQL statement.

For example if you have this simple SELECT statement, but not every manufacturers website is defined, so that the website column value is sometimes null.


SELECT id, manufacturer, website
FROM manufacturers

Then you can simply use the COALESCE function to check for and replace any NULL values with ‘Not defined’.


SELECT id, manufacturer, COALESCE(website, 'Not defined') AS website
FROM manufacturers

Also I’ve given the same column name as an alias to the function so your resulting recordset will have the same column header as your first query.

Did you find the above helpful? Please leave a comment below and let me know…

MySQL phpMyAdmin Tutorial, creating tables and joining tables together

Check out my first video tutorial on YouTube.

I’m demonstrating MySQL phpMyAdmin, to get you started creating a database, creating and populating two MySQL tables and a simple SELECT statement showing how to join the two tables together.

The text size does look a little small, so the next video will be zoomed in to make it easier to see, stay tuned for more.

If you have any questions you would like answered in a video, just leave them in comments below.

Thanks,

Jon

How to join three MySQL database tables together in a simple SELECT statement

Following on from my post of how to join two MySQL tables together, adding a third table is just as easy, but if you need an example this one extends the previous post by adding a third table.

For example I have a table of products, table of product categories and table of product suppliers, how to return the category description of each product and the supplier of the product?

Products
———–
id
category_id
supplier_id
product_name

Categories
————-
id
description

Suppliers
—————
id
supplier_name
supplier_address

In it’s simplest form you would join three tables based on the relationship between a key and it’s foreign key stored in another table:

SELECT p.id, p.product_name, c.description AS ‘cat_description’, s.supplier_name
FROM products p
INNER JOIN categories c on p.category_id = c.id
INNER JOIN suppliers s ON p.supplier_id = s.id

Using a well designed relational database and JOINed queries will make your PHP code simpler and more efficient by returning the data from two or more tables in one query.

Like this post? please leave a comment below:

How to join two MySQL database tables together in a simple SELECT statement

How to join two MySQL database tables together in a simple SELECT statement?
For example I have a table of products and a table of product categories, how to return the category description of each product?
Products
———–
id
category_id
product_name

Categories
————-
id
description

A. In it’s simplest form you would join two tables based on the relationship between a key and it’s foreign key stored in another table:
SELECT p.id, p.product_name, c.description AS ‘cat_description’
FROM products p
INNER JOIN categories c on p.category_id = c.id

Using a well designed relational database and JOINed queries will make your PHP code simpler and more efficient by returning the data from two or more tables in one query.

Like this post? please leave a comment below: