CODEWITHSUNDEEP
mysql
patrick
patrick

Reputation: 11721

getting newer and older posts from MySQL, but at least a certain number

I have a database like this:

+------------+-------------+
| listed     | data        |
+------------+-------------+
| 2013-01-01 | random text |
| 2013-01-02 | random text |
| 2013-01-03 | random text |
| 2013-01-05 | random text |
| 2013-01-06 | random text |
| 2013-01-07 | random text |
+------------+-------------+

in this case 'data' is the title of a joke. I want to list older jokes and new ones next to the current joke. Not every day has a joke. And if there are no newer ones I want just older ones and vice-verse...

so:

   for 2013-01-02 I want 2013-01-01 and 2013-01-03
   for 2013-01-03 I want 2013-01-02 and 2013-01-05
   for 2013-01-07 I want 2013-01-05 and 2013-01-06
   for 2013-01-01 I want 2013-01-02 and 2013-01-03

I can do this in two queries, getting at least 2 jokes in case the other query doesn't return anything:

SELECT * FROM jokes WHERE listed>'$date' ORDER BY listed ASC limit 2
SELECT * FROM jokes WHERE listed<'$date' ORDER BY listed DESC limit 2

and then do the math on the length of the arrays, but I was wondering if there was a proper way to do this in a single query?

Upvotes: 1

Views: 51

Answers (1)

Deval Shah
Deval Shah

Reputation: 1094

Check SQL Fiddle here.I have posted solution that very large query.But I think someone can decrease its size.

SELECT * FROM (SELECT 
   listed,DATA,@r2 := @r2 + 1 AS num
FROM 
  jokes,
  (SELECT @r2:=0) AS e) t WHERE FIND_IN_SET(num,(SELECT FOUND FROM (SELECT 
    listed,`data`,@rn := @rn + 1 AS number,
    IF(listed = '2013-01-07',#pass your date here
        IF(@rn = 1,CONCAT(2,',',3),
            IF(@rn = (SELECT COUNT(*) FROM jokes),CONCAT(@rn-1,',',@rn-2),CONCAT(@rn-1,',',@rn+1)))    ,-1) 
        AS `found`
FROM jokes,(SELECT @rn := 0 ) r

ORDER BY listed ) AS k  WHERE `found` != -1))>0

Upvotes: 1

Related Questions