CODEWITHSUNDEEP
mysql
qadenza
qadenza

Reputation: 9293

Incorrect usage of UNION and ORDER BY

I want to select 12 posts from posts table. If there is no 12 of them, I need to select the missing from parchive table. 

$stmt = $db->query("SELECT * FROM posts where status='public' and user='public' order by inde asc limit 12 offset " . $offset);
$count1 = $stmt->rowCount();
if ($count1 < 12){
    $diff = 12 - $count1;
    $stmt = $db->query("SELECT * FROM posts where status='public' and user='public' order by inde asc limit 12 offset " . $offset . 
    " union select * from parchive where status='public' order by date desc limit " . $diff);
}

Error:
General error: 1221 Incorrect usage of UNION and ORDER BY in...

Any help?

Upvotes: 0

Views: 1415

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133390

if you need limited and order select for union add a couple of () around the select out the union 

  $stmt = $db->query("(SELECT * 
              FROM posts 
              where status='public' and user='public' 
              order by inde asc limit 12 offset " . $offset . 
          ")  union  ( select * 
          from parchive where status='public' order by date desc limit " . $diff .")");

in this way the the UNION work on result set and not on the direct select

Upvotes: 2

Related Questions