Merging two different arrays from two different MySql queries

Question

My site has a friend feed that I want to combine actions into. For ex, right now, it just returns what your friends have searched. However, I also want it to display what they have favorited in the same feed resulting in a feed of searches and favorites sorted chronologically.

The problem is, I can't figure out a good way to tackle this. I can't use MySql UNION because the two tables I would be pulling from (search table and favorite table) don't have the same number of columns and I need more columns from one then the other.

I looked into JOIN, but it is difficult because I am already joining two tables together to return the search results.

What I was thinking - Is there a way to do the two queries separately, grab the output as arrays and combine them with php based on the time column?

It's almost certainly going to be easier and more effecient doing it using a database join — Mark Baker
– Mark Baker, Commented Jan 24, 2012 at 22:04

Scott Bonner · Accepted Answer · 2012-01-24 22:03:58Z

1

Can't you make the column number match up, either add blank ones to the one with less or something along those lines and still make them match?

answered Jan 24, 2012 at 22:03

Scott Bonner

3,0005 gold badges27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Bonner Over a year ago

select name, '' as email from customer1 union select name, email from customer2

Eugen Rieck · Accepted Answer · 2012-01-24 22:10:17Z

0

Yes, it is possible to do the two queries separately, grab the output as arrays and combine them with php, but no, you don't want to do it this way. You open yourself up to an unmaintainable code mess, once you realize that you don't have enough with searches and favorites, but you also need likes and posts.

My suggestion is:

For every table, create a corresponding view, all such views sharing the structure. I am mimicking the Interface/Implementation pattern of other languages here. Make sure your view structure has the timestamp, a descriptive column, an id to the user, etc. whatever you need
Do your combined query across the interfaces: SELECT * FROM searches_view WHERE ... UNION SELECT * FROM favorites_view WHERE ... ORDER BY date_submitted DESC

this way your app can rely on a standardized "interface" to searches via "searches_view" etc., as a result an expansion to more item types is trivial.

answered Jan 24, 2012 at 22:10

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

Michael Over a year ago

This is a good idea. Definitely better for organization and functionality. Thanks!

Collectives™ on Stack Overflow

Merging two different arrays from two different MySql queries

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related