SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Comparing two fields in MySQL

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    I would do it in PHP.

  3. #3
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guido - how should I query the data?

  4. #4
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    518
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    
    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:
    Code:
    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. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    In this case there's not really a should.
    ah, but there is

    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •