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:
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
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
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…
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?
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.