Jump to content

Trying to create a UNION between two tables


Chrisj

Recommended Posts

In the PHP/SQL script I'm using the search query is only done on one table at a time, currently. I'd like help creating combined search results "All" from the videos table and images table when similar "tags" are searched. Can a Union be used to display just specific, similar columns from each table?
Currently, the separate 'search results' are displayed the same way. The 'image search results' display an image, title, description, in a results column on the page. The 'video search results' display a thumbnail image, title, description, in a results column on the page. I've attached a shot of the 'images' table and a shot of the 'videos' table. Any guidance will be appreciated.

post-20454-0-27649400-1466202693_thumb.png

post-20454-0-35253500-1466202704_thumb.png

Link to comment
Share on other sites

So long as the end results have the same number and types of columns you can do a UNION of two separate queries. So you're query would look something like:

SELECT video_id as id, title, description FROM videos
UNION ALL
SELECT image_id as id, title, description FROM images
That would give you a single result set with three columns: id, title, and description.

 

You'll also likely want some way to know whether a given row is an image or a video. To do that you can add a static column to each query that indicates the row type.

SELECT 'video' as rowType, video_id as id, title, description FROM videos
UNION ALL
SELECT 'image' as rowType, image_id as id, title, description FROM images
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.