Union Select in PDO not working

Question

I'm new to using PDO to make db requests and need a little help. I have the following db call:

$stmt1 = $pdo->prepare('
    SELECT * FROM news WHERE pub_date >= ? AND pub_date < ? AND display = 1 ORDER BY pub_date DESC
    UNION
    SELECT * FROM vs_news WHERE pub_date >= ? AND pub_date < ? AND display = 1 ORDER BY pub_date DESC
');
$stmt1->bindParam(1, $col_start);
$stmt1->bindParam(2, $col_end);
$stmt1->execute();

I have read enough to think the UNION is compatable with PDO, but I can't seem to get the code right and can't find an example in complete code format.

The fields in both tables are the same and the db call works with just one or the other table, but not with the UNIION that I have shown.

Could someone please point where my problem is?

Thanks

Do I need to add:$stmt1->bindParam(3, $col_start); $stmt1->bindParam(4, $col_end); — user1028866, Mar 16 '12 at 00:33
As far as I am concerned, you cannot order on the different SELECT statements you UNION. UNION does not guarantee to keep the order of the tables unioned - this is also why it does not make sense to order them before using UNION. You should just order after using UNION. — Kent Munthe Caspersen, Apr 23 '14 at 14:06

score 6 · Accepted Answer · answered Mar 16 '12 at 00:34

Using the ? means you need to match a param for each ?

Use this sort of approach:

$stmt1 = $pdo->prepare('
    SELECT * FROM news WHERE pub_date >= :date1 AND pub_date < :date2 AND display = 1 ORDER BY pub_date DESC
    UNION
    SELECT * FROM vs_news WHERE pub_date >= :date1 AND pub_date < :date2 AND display = 1 ORDER BY pub_date DESC
');
$stmt1->bindParam(':date1', $col_start);
$stmt1->bindParam(':date2', $col_end);
$stmt1->execute();

Also; With union, make sure that you use the SAME number of columns in both queries.

score 3 · Answer 2 · answered Mar 16 '12 at 00:37

In order to use ORDER BY inside UNION, you should enclose the components in (). Do this in addition to binding all four parameters as suggested in the comments, or using named place holders as suggested elsewhere:

$stmt1 = $pdo->prepare('
    (SELECT * FROM news WHERE pub_date >= ? AND pub_date < ? AND display = 1 ORDER BY pub_date DESC)
    UNION
    (SELECT * FROM vs_news WHERE pub_date >= ? AND pub_date < ? AND display = 1 ORDER BY pub_date DESC)
');

This is described in the MySQL UNION syntax reference.

I found something else that might help someone else reading this. I removed the two ORDER BY and put it once after the the closing ")" of the second SELECT. This resulted in ordering the unioned selects. — user1028866, Mar 16 '12 at 01:06

score 0 · Answer 3 · answered Mar 26 '22 at 16:19

I like Menztrual's answer. It's the one I like the most because it gives me more control over the visual distribution of my elements in a query, however, the fun part comes after in the binding of the data, I always find to complicated having to write a bindParam for every placeholder

$stmt1->bindParam(':date1', $col_start);
$stmt1->bindParam(':date2', $col_end);
$stmt1->execute();

So, I use a much simpler and elegant way of doing this. I'll leave here an example of a simpler binding array way of execution for anyone that doesn't already know about it.

$stmt1->execute([':date1'=>$col_start,':date2'=>$col_end]);

For cooler facts on PDO I recommend this site: (The only proper) PDO Tutorial lots of useful content there.

https://stackoverflow.com/a/12392590/14809952 – syahid246 Jun 19 '22 at 07:19 — syahid246, Jun 19 '22 at 07:19

Union Select in PDO not working

3 Answers3

Linked