php - Mysql build query with multiple constraints

126

I am trying to set up a query that outputs the 5 most read articles over the last 4hrs. However, if there are no new articles over the last 4hrs, the list will be empty. What I would like is a query that outputs 5 articles over the last 4hrs. If there are no 5 articles in the last 4hrs, but lets say only 2, I would like to add 3 more newest articles to the list (sorted by log_time). So the query should always output 5 articles. I am wondering if this can be done in one query.

$sqlCommand = "SELECT * FROM feeds where category like '$category' AND log_time > NOW() - INTERVAL 4 HOUR ORDER BY feed_hits DESC LIMIT 5"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
While ($row = mysqli_fetch_array($query)) { 
    $fid1 = $row["id"];
    $feed_id1 = $row["feed_id"];
    $link1 = $row["link"];
    $title1 = $row["title"];
    $output .= '<a href="detail/' . $fid1 . '" title="' . $title1 . '">' . $title1 . '</a><br/>';
    } 
    mysqli_free_result($query); 
echo $output;

327

Answer

Solution:

You can achieve this via aUNION query. The first half of the below query is simply your original query, which may return up to 5 matching records. The second half of the query returns an arbitrary number of records which are older than 4 hours. The entire query is then ordered by log time, with a limit of 5. This puts your recent target records first, with the older records taking any available positions should they be needed.

SELECT *
FROM
(SELECT * FROM feeds
 WHERE category like '$category' AND log_time > NOW() - INTERVAL 4 HOUR
 ORDER BY feed_hits DESC LIMIT 5) t1

UNION ALL

(SELECT * FROM feeds
 WHERE category like '$category' AND log_time <= NOW() - INTERVAL 4 HOUR) t2
ORDER BY log_time DESC
LIMIT 5;

577

Answer

Solution:

You could not limit for 4 HOUR and order by log_time limit 5

  "SELECT * 
  FROM feeds 
  where category like '$category' 
  ORDER BY  log_time DESC LIMIT 5";

and if you need ordered by feed_hits you can reorder the result set

  "select * from ( SELECT * 
  FROM feeds 
  where category like '$category' 
  ORDER BY  log_time DESC LIMIT 5 ) t order by feed_hits ";

Answer

Solution:

Simply remove the time filtering and instead order by time, this will always give you the latest:

SELECT * FROM feeds WHERE category LIKE '$category' ORDER BY log_time DESC, feed_hits DESC LIMIT 5

People are also looking for solutions to the problem: php - How to include file in subdomain from main domain

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question