CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
xRobot
xRobot

Reputation: 26567

How to get a way to distinguish the queries with union

I am creating a blog in php/mysql where the user can post text or image.

If it's a text, then it has an url like this: www.example.com/text.php?id=...

If it's a image, then it has an url like this: www.example.com/image.php?id=...

I am inserting texts and images in 2 different tables:

Table Image:
-id
-url
-date

Table Text:
-id
-body
-date

At this point, in the homepage, I need to do a query that return me latest url to text and image merged. So I need to use UNION:

SELECT id
FROM image
ORDER BY date DESC
UNION
SELECT id
FROM text
ORDER BY date DESC

But I need also a way to distinguish the type ( text or image ), so in the homepage I know if I have to use www.example.com/text.php?id= OR www.example.com/image.php?id=

For example:

SELECT id, TYPE_1
FROM image
ORDER BY date DESC
UNION
SELECT id, TYPE_2
FROM text
ORDER BY date DESC

Is there a way to do that in mysql ?

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

You can't do this directly in the union, because then duplicates will not be removed. You need to do a union all and then remove the duplicate entries afterwards:

select id, type_1,
       (case when min(which) = max(which) then min(which)
            else 'BOTH'
        end) as WhereFrom
from (SELECT id, TYPE_1, date, 'image' as which
      FROM image
      UNION all
      SELECT id, TYPE_2, date, 'text' as which
      FROM text
     ) t
group by id, type_1
order by max(date) desc

Upvotes: 0

andrewsi
andrewsi

Reputation: 10732

Something like this should work:

SELECT id, 'image' AS type
FROM image
ORDER BY date DESC
UNION
SELECT id, 'text' AS type
FROM text
ORDER BY date DESC

You'll have two columsn in your result; and you can check 'type' to see if it's image or text, to process the line appropriately.

Upvotes: 3

Related Questions