0

I have a query like

SELECT *
FROM `mytable`
WHERE 
  (`status` LIKE 'active%' OR `status` LIKE 'featured%')
  AND `views` < `tviews`
  AND `id` > 4
ORDER BY RAND()
LIMIT 1;

Now if NO rows match that query I want it to return ROW where id=2

How is this done. note I want the whole row at position id=2

Ps. does using RAND() matter if the table contains only max 100 rows, just new to mysql dont want to do anything unless necessary

And no its not a duplicate question, that other one i read and its not the same. That returns a default value like "Peter" or 0, i want it to return a full ROW not a single value! So thanks for reporting and stopping me from asking questions

user2899094
  • 491
  • 1
  • 4
  • 17
  • You would be better off getting rid of `ORDER BY RAND()`, and finding a random ID to return the data for. – user1032531 Jan 07 '14 at 19:45
  • To get rid of `ORDER BY RAND()`, take a look at [this thread](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function). – Ted Hopp Jan 07 '14 at 19:46
  • 1
    It feels hacky, but you _could_ `UNION` this together with another component that returns _only_ the row where `id=2`. Then if the entire query only returns _one_ row, you know it was the default row. If it returns multiple rows, fetch all but the last (or first if it's easier to put the default row first in the `UNION`) – Michael Berkowski Jan 07 '14 at 19:46

1 Answers1

0

You should get rid of your use of RAND(). That being said...

$sql ="SELECT *
FROM `mytable`
WHERE 
(`status` LIKE 'active%' OR `status` LIKE 'featured%')
AND `views` < `tviews`
AND `id` > 4
ORDER BY RAND()
LIMIT 1";
$stmt = $conn->query($sql);
if(!$data = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $sql ="SELECT *
    FROM `mytable`
    WHERE 
    (`status` LIKE 'active%' OR `status` LIKE 'featured%')
    AND `views` < `tviews`
    AND `id`=2";
    $stmt = $conn->query($sql);
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
}
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • i was hoping something easy like... SELECT * FROM `mytable` WHERE (`views` < `tviews` AND `id` > 4) OR (`id`=2) ORDER BY RAND() LIMIT 1; – user2899094 Jan 07 '14 at 20:39
  • I don't like that use of two separate calls here. For anyone gathering inspiration from the above snippet, have a look @ http://stackoverflow.com/questions/15319264/return-a-default-value-if-no-rows-found/34758622#34758622 – mickmackusa Jan 13 '16 at 05:19