Comparing two fields in MySQL

PHP
1

I have built a simple website that grabs data from MySQL. The site uses the Twitter API to grab data about a users account, i.e. number of followers. I am fetching the data once a day and storing the results as a new entry in the table. The goal is to be able to show trending over time. I have successfully create a MySQL query to display the latest result but now would like to compare the latest entry with the last entry (or an entry from a specific date) to see how it has changed.

A simplified version of the table looks like this

Twitter_handle | Followers | date_of_last_update
handle1 | 1,100 | 2012-02-17 15:36:31
handle1 | 1,110 | 2012-02-18 15:25:00

I would like to display on the page that handle1 increased by 10 since the last entry.

Should this be handled as a SQL query or should I be doing something with PHP.
.

Thanks in advance.

2

I would do it in PHP.

3

Guido - how should I query the data?

4

I would probably do it in mysql, because I like having as much number crunching as possible done on the db side, and php pretty much outputting it, though it often makes the query more complex. If you’re using really large amounts of data, mysql will probably do the calculations faster than bringing in a lot of data an looping through. Of course you can also do it largely in php. In this case there’s not really a should.

An example query - compare followers since the last check




SELECT tw1.Twitter_handle, MAX(tw1.last_update) AS last_update,
(
	SELECT tw2.followers
	FROM twitter_followers tw2
	WHERE
		tw2.Twitter_handle=tw1.twitter_handle AND
		tw2.last_update = MAX(tw1.last_update)
) AS last_followers,
(
	SELECT MAX(tw3.last_update)
	FROM twitter_followers tw3
	WHERE
		tw3.Twitter_handle=tw1.twitter_handle AND
		tw3.last_update<MAX(tw1.last_update)
) AS prev_update,
(
	SELECT tw4.followers
	FROM twitter_followers tw4
	WHERE
		tw4.Twitter_handle=tw1.twitter_handle AND
		tw4.last_update=(
			SELECT MAX(tw3.last_update)
			FROM twitter_followers tw3
			WHERE
				tw3.Twitter_handle=tw1.twitter_handle
				AND tw3.last_update<MAX(tw1.last_update)
			)
) AS prev_followers
FROM  twitter_followers tw1 GROUP BY tw1.Twitter_handle

Output:


Twitter_handle	last_update	last_followers	prev_update	prev_followers
handle1	2012-02-20	1100	2012-02-15	900
handle2	2012-02-20	1300	2012-02-15	1125

This looks a bit messy, what with all the subqueries, and I’m sure there’s a more elegant/efficient way of doing it keeping within the database layer.

5

ah, but there is :slight_smile:

you should use the method that performs better, and is easiest to manipulate with regards to the original specs, namely “or an entry from a specific date”

i’m pretty good with sql, but i sure wouldn’t want to try to modify your query to meet those specs