How to Return Default Values for Empty Result Sets in Database Queries?

Returning Default Values with Empty Result Sets

When querying a database, it's common to handle scenarios where no matching rows are found. To avoid empty results, you can return a default value instead.

In your case, you want to select the next scheduled item for a stream. If no item is scheduled, you desire a default value to ensure the stream plays content.

You've attempted using COALESCE and IFNULL without success. However, there's a slightly different approach that will work:

<code class="sql">SELECT IFNULL(MIN(`file`), 'default.webm') `file` 
FROM `show`, `schedule` 
WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
  AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
ORDER BY `start_time` DESC LIMIT 1</code>

By using MIN() as an aggregate function, you ensure that a NULL value is returned when no rows are selected. Then, IFNULL or COALESCE can replace NULL with your default value, 'default.webm' in this case.

This method allows you to retrieve a default value if the query returns no results, effectively handling the absence of matching rows.

The above is the detailed content of How to Return Default Values for Empty Result Sets in Database Queries?. For more information, please follow other related articles on the PHP Chinese website!