How to combine the results of two SQL queries so they can be looped through on php website

Hi there,
I have two types of advert messages on my site: Text and Video which are scheduled to go out later on. So I have a Text table, Video table, TextMsgScheduled table and VideoMsgScheduled.  
I want my customers to be able to look at all their messages chronologically in a dynamic slider which I have up and running. However I need to populate the slider with the details of the Text and Video Messages.

So I have been trying to query the DB to give me the list of TEXT messages and the list of Videos messages for a particular customer. I can list the text messages alone  and I can list the video messages alone but not combine them. See the below queries which work fine individually

SELECT * FROM `txtmsgscheduled` join `txtmsg` on txtmsg.txtmsgId=txtmsgscheduled.txtmsgId  where txtmsgscheduled.UserId = '50d16ee5-2f63-417a-b315-356a7c6dc8ec')

SELECT * join `videos` on videomsgscheduled.videoID= videos.videoID where videomsgscheduled.userID = '50d16ee5-2f63-417a-b315-356a7c6dc8ec'

I would like to be able to combine the results of both queries so that I can loop through them in php on my site, identify which type they are and add them to my slider in chronological order. The slider can take different content types.

I am unsure the best way of doing this. Both resulting query tables have different columns types and numbers of columns so a Union doesn’t seems to be possible.


Any suggestions would be great
 
Thanks in advance
Williery (novice)
williery10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please post the CREATE TABLE statements for these tables, thanks.  And we would need a few rows of data so we can set up a test case.  It may be possible to do this in the SQL engine, but if it's not, it can surely be done in PHP with some kind of array work.
0
gr8gonzoConsultantCommented:
Do you really NEED to use SELECT * ?

You can still use UNION by selecting individual columns and naming them the same between tables:

SELECT txtmsgId as ID,txtmsgContent as Content,'TextMessage' as Type FROM `txtmsgscheduled` join `txtmsg` on txtmsg.txtmsgId=txtmsgscheduled.txtmsgId  where txtmsgscheduled.UserId = '50d16ee5-2f63-417a-b315-356a7c6dc8ec')
UNION
SELECT videoID as ID,videomsgContent as Content,'Video' as Type join `videos` on videomsgscheduled.videoID= videos.videoID where videomsgscheduled.userID = '50d16ee5-2f63-417a-b315-356a7c6dc8ec'

That should produce a single, combined result with three columns:
ID, Content, and Type
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
F PCommented:
Using a JOIN within SELECT tells the SQL engine to filter the results on each table and the returned result should match and display only results that match with the ON operator. So? If there are no records matched using your filter, it could simply be because you're telling it you only want records where those fields match, and the filter for WHERE criteria are met. So if that's what you want, then you're set. If you mean that you want each query to be independent of one another in their filtering, so it doesn't matter is the id matches the other id, but simply concatenate the queries, you want the UNION operator. Here's more info...

https://dev.mysql.com/doc/refman/5.6/en/union.html

UNION is used to combine the result from multiple SELECT statements into a single result set.

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

Open in new window

0
williery10Author Commented:
Thanks for the suugestions. Regarding the Unions, selecting 3 and renaming them is a great idea but I needed all the columns from both set of tables. One set had 9 and the other 8 so it made the union that bit more complicated. I am sure there was a work around but probably messy. I have taken the results and put them through an array to combine the result using PHP which has worked well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.