Most powerful way to select MySql data if result is null

Question

I would ask what is the best and most powerful way to extract data from two mysql tables based on certain filters and, if the result is null, extract data without conditions?

table user

ID      NAME        SURNAME 

1       nick        smith
2       john        davis
3       mattew      miller
4       roger       wilson
5       karl        taylor
6       july        moore
7       michael     white
8       genny       lopez


table access

ID      IDUSER      LASTACCESS  

1       1           2015-05-27 08:10:15
2       5           2015-05-27 08:25:23
3       4           2015-05-27 09:15:24
4       7           2015-05-27 09:28:24
5       7           2015-05-27 23:02:05
6       1           2015-05-28 01:10:55
7       2           2015-05-28 02:11:20
8       3           2015-05-28 04:15:49

assuming that now is 10:00 am of the day 05/28/2015, I would like to extract the accesses occurred between now and three hours ago and if the result is zero (and it is) I would like the query returns the last few 3 accesses in descending date order. Thanks

============================= UPDATE ================================

I try this:

SELECT * FROM access 
WHERE access.iduser = user.id 
AND < DATE_SUB(NOW(), INTERVAL 3 HOUR) 
OR user.id = ANY ( SELECT * FROM access ORDER BY lastaccess DESC ) 
GROUP BY user.id ORDER BY lastaccess DESC

I would get the result directly from MySQL without using PHP

If you provide some options, we can tell you which is better. Alternatively, if you actually just want a solution to the problem, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, May 28 '15 at 11:53

score -1 · Answer 1 · answered May 28 '15 at 10:48

$hours = 3;


$query = mysql_query("SELECT * FROM user, access
    WHERE access.iduser = user.id
    AND < DATE_SUB(NOW(), INTERVAL '$hours' HOUR) LIMIT 3");

if you get no results, increment the $hours by one each time and rerun the query until you get results.

Most powerful way to select MySql data if result is null

1 Answers1