How to merge two arrays from SQL query?

Question

I've got following two queries :

SELECT date( FROM_UNIXTIME( user_reg_date ) ) 'Current Date', COUNT( * ) 'registered_user'
FROM users
WHERE user_reg_date >=1341100800
AND user_reg_date <=1374451200
GROUP BY date( FROM_UNIXTIME( user_reg_date ) )

SELECT date( FROM_UNIXTIME( user_last_login ) ) 'Current Date', COUNT( * ) 'logged_in_user'
FROM users
WHERE user_last_login >=1341100800
AND user_last_login <=1374451200
GROUP BY date( FROM_UNIXTIME( user_last_login ) )

Now I want merge the two arrays which I got from the above query and each array element should be of the following form.

date = > array(registered_user, logged_in_user)

Can anyone help me in this regard? Thanks in advance.

score 2 · Answer 1 · answered Jul 22 '13 at 10:36

2

Use UNION like

SELECT date( FROM_UNIXTIME( user_reg_date ) ) 'Current Date', COUNT( * ) 'registered_user'
FROM users
WHERE user_reg_date >=1341100800
AND user_reg_date <=1374451200
GROUP BY date( FROM_UNIXTIME( user_reg_date ) )

UNION

SELECT date( FROM_UNIXTIME( user_last_login ) ) 'Current Date', COUNT( * )   'logged_in_user'
FROM users
WHERE user_last_login >=1341100800
AND user_last_login <=1374451200
GROUP BY date( FROM_UNIXTIME( user_last_login ) )

But careful with the duplicate columns that these two tables have.Try to call them using table object like

table1.id,table2.id

Consider that id has exists in both the tables.

answered Jul 22 '13 at 10:36

GautamD31

28,552
10
64
85

I tried this but it gives only current_date and registered_user. Actually I want both the counts in an array. – PHPLover Jul 22 '13 at 10:38
You forgot to write like this with 'as' COUNT( * ) as 'registered_user' – GautamD31 Jul 22 '13 at 10:41
@phpLover: What does prevent you to do so? I can not see any code in your question that shows what you've actually tried, so it's a bit unfair to let other place guessed answers only then to tell them you tried that already. Don't you think so? – hakre Jul 22 '13 at 10:42

score 2 · Accepted Answer · answered Jul 22 '13 at 10:55

2

You could do a very simple UNION ALL and group by date later with a SUM to get the values;

SELECT DATE(FROM_UNIXTIME(date)) date, 
       SUM(registered_user) registered_user, 
       SUM(logged_in_user) logged_in_user
FROM (
  SELECT user_reg_date date, 1 registered_user, 0 logged_in_user FROM users
  UNION ALL
  SELECT user_last_login,    0 registered_user, 1 logged_in_user FROM users
) a
WHERE date >= 1341100800 AND date <= 1374451200
GROUP BY DATE(FROM_UNIXTIME(date));

answered Jul 22 '13 at 10:55

Joachim Isaksson

176,943
25
281
294

1

Thank you so much one again. Your query really did the magic for me. I tworked perfectly as per my requirement. I've accepted your answer and upvoted it also. – PHPLover Jul 22 '13 at 11:15
:As like above query I want to fetch the same record counts whose reg_date and last_login date falls within current week, last week and last two weeks. How could I write these things in WHERE condition? – PHPLover Jul 22 '13 at 11:17
Can't test, but `WHERE YEARWEEK(FROM_UNIXTIME(date)) = YEARWEEK(NOW())`, `WHERE YEARWEEK(FROM_UNIXTIME(date)) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)` and `WHERE YEARWEEK(FROM_UNIXTIME(date)) >= YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK)` respectively would seem promising. – Joachim Isaksson Jul 22 '13 at 11:24

How to merge two arrays from SQL query?

2 Answers2