frankchester

I'm not sure if this is a pure mySQL question or if it's more PHP but I thought this seemed like a good place to ask it. I have a table which has data stored into it twice daily. The data stored is the timestamp, an account_ID, and the number of followers. I am trying to add another field of "gains_losses". This should look at the previous entry for that account_ID, find out the followers data, and then find out the difference between the data being submitted today. How would you go about doing this? I'm a bit stuck because I can't do it based on the date previously, and the times can change so I can't do a search for "Where time = this minus one day". Here's a screenie of the table, you can see the account_ID repeats often as these are entries for each of these accounts.

Thanks, that makes a lot of sense. I'll see what I can do.

Hello! First of all, I'm new here. I've been working with PHP for a few months and am really enjoying its capabilities A little background on the project I'm doing at the mo, I'm creating a small dashboard for grabbing Twitter stats via the API. The result will eventually output in a pretty table showing the daily followers, follower gains (or losses) etc. Here's an example of the table I eventually want to end up with. So far I have two arrays of data: $accounts_data is a list of all account names and their associated ID's $followers_data is a list of all follower counts, for a specific time of day, with an associated account ID (which matches the ID found in $accounts_data, thus pairing the follower count to a specific account) I'm now trying to combine these two arrays into something more sensible that can then be used to output the table as shown in the link above. $accounts_data: array(4) { [0]=> array(2) { ["id"]=> string(4) "1279" ["name"]=> string(13) "AccountName1" } [1]=> array(2) { ["id"]=> string(4) "1280" ["name"]=> string(10) "AccountName2" } [2]=> array(2) { ["id"]=> string(4) "1281" ["name"]=> string(12) "AccountName3" } [3]=> array(2) { ["id"]=> string(4) "1283" ["name"]=> string(11) "AccountName4" } } $followers_data (truncated this data) array(12) { [0]=> array(3) { ["account_id"]=> string(4) "1279" ["date_time"]=> string(19) "2014-11-14 09:00:03" ["followers"]=> string(4) "1567" } [1]=> array(3) { ["account_id"]=> string(4) "1280" ["date_time"]=> string(19) "2014-11-14 09:00:29" ["followers"]=> string(4) "7688" } [4]=> array(3) { ["account_id"]=> string(4) "1279" ["date_time"]=> string(19) "2014-11-14 16:30:35" ["followers"]=> string(4) "1566" } } 1. The first thing I want to do is take the name data and make that the first item in a sub-array of the ID. As such: This is because I think the ID data should be the most top-level data, and I think name should come underneath it, not be equal to it in the hierarchy, ["1283"]=> array (1){ ["name"]=> string(11) "AccountName4" } 2. Next up, I want to add a "dates" section to this array This will be where the follower data per date is stored ["1283"]=> array (2){ ["name"]=> string(11) "AccountName4" ["dates"]=> array(3){ // stuff here } } 3. Then, I want to take the contents of $followers_data and put it into the ['dates'] index for it's relevant ID. This is probably the most confusing part. I have no idea how to match the account_ID from my second array to the ID in the first array. And I need to nest the ['followers'] data underneath each date. ["1279"]=> array (2){ ["name"]=> string(11) "AccountName4" ["dates"]=> array(3){ [2014-11-14 09:00:03]=> array(1){ ["followers"]=> string(4) "1567" } [2014-11-14 16:30:35]=> array(1){ ["followers"]=> string(4) "1566" } } } In this array above you can see that the ID contains the name, and another array of dates. The Dates array contains each date. And inside each date, there are the followers. I know this is probably a really huge thing to do, but if anyone can offer help on even getting started in the right direction I would be very grateful.