Topic: Using PHP to display MySQL data from two different dates in the same row.

[bingwalker]

I need to be able to compare data from today and data from yesterday within the same row, same while loop.

For instance, for yesterday I would have 300 twitter followers and today I had 310 followers, so my +/- column would by +10, or 3% or whatever.  But I can't figure out how to do this in the loop.  The query currently is:

SELECT m.date,m.user,m.followers,m.following,m.tweets,u.avatar FROM metrics m,users u where m.user=u.user and m.date=CURDATE() and u.acct_id=1 order by m.followers desc;

Then I want to use:

SELECT followers FROM metrics where date=DATE_ADD(CURDATE(), INTERVAL -1 DAY);

...to get yesterday's total followers.  And then compare the follower numbers from the two dates.

Can't figure it out.  Any ideas?

[fenway]

I don't see why you need it in "a single row, while loop" -- who cares?  Besides, it's easier to write as two subqueries, though there is a fancy way to get this in a single query. 

[bingwalker]

I don't see why you need it in "a single row, while loop" -- who cares?  Besides, it's easier to write as two subqueries, though there is a fancy way to get this in a single query. 

Subqueries.  I hadn't thought of that.  I'm really a noob right now.  I'll research that, any quick ideas on how to write that query with the subquery?