CODEWITHSUNDEEP
phpmysqlarraysjoinmerge
Michael
Michael

Reputation: 400

Merging two different arrays from two different MySql queries

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?

Upvotes: 0

Views: 969

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65264

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.

Upvotes: 0

Scott Bonner
Scott Bonner

Reputation: 2970

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?

Upvotes: 1

Related Questions