Timestamps and PHP/MySQL



  • Good morning!

    I've got an issue that is probably very easy, and I may just be overcomplicating it, or it may actually be as complicated as i think. What i'm trying to accomplish is: Creating a chart, using the jquery plugin flot. I've got the chart all nice and pretty and everything, thats not the problem. The problem i'm having is getting the actual data in it.

    My goal is to plot the graph with totals of how many donations a charity of given charity_id had, per day. My query right now is this:

    SELECT COUNT(*) AS total, UNIX_TIMESTAMP(donation_date) AS donationdate FROM donations WHERE donation_charity_id = '$getid' GROUP BY DATE_FORMAT(donation_date, '%Y%m%d') ORDER BY donation_id DESC

    (the group by is because it is a datetime, and i only want to group by day, hopefully thats not a WTF :))

    which gets me something like this:

    <tr class="even">
    <td class="nowrap" align="right">1</td>
    <td class="nowrap" align="right">1254001622</td>
    
    <tr class="odd">
    <td class="nowrap" align="right">1</td>
    <td class="nowrap" align="right">1253913038</td>
    
    <tr class="even">
    <td class="nowrap" align="right">2</td>
    <td class="nowrap" align="right">1253804931</td></tr></tbody></table><p>and so on. Then i loop through it to create a string like [1254046803, 2], [1254001622, 1], etc etc. which gets echo'd into my javascript as needed by the plugin.</p><p>That's fine, it creates the chart and plots the points as it should be, except theres no 0 points at all. Basically, what i need to have happen, is for however many days in between 1254046803 and 1254001622, is be able to put 0 values inside of that string, with the timestamps. so lets assume that those two values are day 1 and day 5, i would need to have my string look like this: [day1, 2], [day2, 0], [day3, 0], [day4, 0], [day5, 1]</p><p>The reason for this is that the graph will stay on 1 horizontally until it gets another timestamp, and another value. assuming day 1 is 2, it would start on 2, then it would just slowly drop down to 1 from day 1 to day 5. i need to have it start at 2 on day1, drop to 0 and stay there for day2, 3, and 4, then pop back up to 1 for day5, and so on.</p><p>Heres the real kicker, a lot of these are going to have data over 4+ years. so thats going to be one hell of a long string. </p><p>The only "solution" i've come up with so far seems really clunky and wtf'y, but it may be the only thing i can do:</p><p>Take all my timestamps, figure out how many days are in between timestamp 1 and timestamp 2 (and 2 and 3, and 3 and 4, and so on), and get timestamps for those days as well, then inject them in the php string in between timestamp 1 and 2... </p><p>&nbsp;Any ideas? am i at least on the right path? Like i said, 4 years worth of donations to any given charity returns a couple hundred rows at it is, without the days that the charity didn't get any donations put in between them.&nbsp; </p><p>Thanks a bunch for any help/advice, its appreciated! <br></p><p>&nbsp;</p><p>&nbsp;</p>
    2 1254046803


  • I don't entirely follow you. End result you want something like:

    • [1254046803, 2]
    • [day1, 2]
    • [1254001622, 1]
    • [day2, 0]
    • [1253913038, 1]
    • [day3, 0]
    • [1253804931, 2]
    ? What do you need the day1 things for if you're getting the timespamps?


  •  Sorry about that. Let me try with actual dates instead of timestamps so that it seems to make more sense.

    there were two donations to charity 29 on 2009/09/01, and 1 donation to charity 29 on 2009/09/05. Which leaves 2009/09/02, 2009/09/03, and 2009/09/04 with no donations. So in order for the graph to go to zero and stay there for those 3 days, i need to get those 3 missing days into my string, which currently is [200/909/01, 2],[2009/09/05,1] (with timestamps instead of dates, of course)

    Heres what the end result should look like:  [2009/09/01, 2], [2009/09/02, 0], [2009/09/03, 0], [2009/09/04, 0], [2009/09/05, 1] (again, timestamps instead of dates)

    Right now, the query gives me 2 2009/09/01 and 1 2009/09/05, so i need to figure out the best way to get in between those days, put [timestamp, 0] in my string in between those dates, and so on, for 4+ years worth of data.

    Heres some code i came up with that is half working, and 100% wtfy and screaming "there has to be a better way!!" at me.

     


    $res = $db->query("SELECT COUNT(*) AS total, UNIX_TIMESTAMP(DATE_FORMAT(donation_date, '%Y%m%d')) AS donationdate FROM donations WHERE donation_charity_id = '$getid' GROUP BY DATE_FORMAT(donation_date, '%Y%m%d') ORDER BY donation_id DESC");
    $donationSums = array();
    while($donations = $db->assoc($res)) {
        $donationSums[] = $donations;
    } //while

    $donationStr = '';
    $max = 0;
    $i=0;
    foreach($donationSums as $k => $v) {
        $thisDay = $v['donationdate'];
        if(array_key_exists($i+1, $donationSums)) {
            $nextDay = $donationSums[$i+1]['donationdate'];
        }
        $thisMax = $v['total'];
        if(($thisDay - $nextDay)/86400 > 1) {
            for($x=1;$x<($thisDay - $nextDay)/86400;$x++) {
                $donationStr .= '[' . ($thisDay+86400)*1000 . ', 0], ';
            } //for
        } else {
            $donationStr .= '[' . ($v['donationdate']*1000) . ', ' . $v['total'] . '], ';
        }
        
        if($thisMax > $max)
            $max = $thisMax;
        $i++;

    } //foreach

    $max is only there to figure out the maximum donation amount given on any day, so the JS knows how tall to make the graph. nothing serious there. This seems to kinda sorta work, in so much that it gives me timestamps for the dates in between a donation in the query results, except that, for example, one charity which has had a total of 3 donations, after running this code on it, i got only one, so it was a bunch of [timestamp, 0]'s, then one [timestamp, 2] in the string.

    It should have had 3 [timestamp, (some value greater than 0)]...

    Kind of at a loss here, this just seems...ridiculously bad. Though suprisingly the page load time for one of these with a few thousand timestamps isnt all that bad...



  • Ah, you can go about it by making an array keyed by every date in the range set to zero, then fill that with the DB query, then foreach over that to produce the output.


    Personally I'd have used the imagemagick or GD functions to make the graph, and would've used something to get prepare()/execute() calls for the DB query (MDB2 is good for this). Careful about $getid, make sure you sanitize it, as it stands it looks like you have an SQL injection vulnerability.



  •  Thanks for the advice. $getid is getting sanitized in the header portion of the script, just not in this block.

    What i've come up with is this, which actually seems to work:

     

    $res = $db->query("SELECT COUNT(*) AS total, UNIX_TIMESTAMP(DATE_FORMAT(donation_date, '%Y%m%d')) AS donationdate FROM donations WHERE donation_charity_id = '$getid' GROUP BY DATE_FORMAT(donation_date, '%Y%m%d') ORDER BY donation_date");
    $donationSums = array();
    while($donations = $db->assoc($res)) {
        $donationSums[] = $donations;
    } //while

    $donationStr = '';
    $max = 0;
    $i=0;
    foreach($donationSums as $k => $v) {
        $thisDay = $v['donationdate'];
        
        if(array_key_exists($i+1, $donationSums)) {
            $nextDay = $donationSums[$i+1]['donationdate'];
        }
        
        $dayDiff = ($nextDay - $thisDay)/86400;
        $donationStr .= '[' . ($v['donationdate']*1000) . ', ' . $v['total'] . '], ';
        if($dayDiff > 1) {
            for($x=1;$x<$dayDiff;$x++) {
                $donationStr .= '[' . ($thisDay+(86400*$x))*1000 . ', 0], ';
            } //for
        } //if daydiff > 0    
        
        $thisMax = $v['total'];
        
        if($thisMax > $max)
            $max = $thisMax;
        $i++;
    } //foreach

    still seems really...crazy and unnecessary, but its at least getting the correct dates with totals for the dates, and most importantly, putting the timestamps in for dates that dont have any donations...

    I'll see what i can come up with as far as imagemagick/GD, though my experience with them is quite limited, but it does seem like it might be a better option than JS.

    Thanks again!



  • The way I handle things like this is to borrow from Data Warehouses and generate a fairly large table of dates with various other descriptor columns like year, month no, month name, quarter,etc. I then use it as the primary table and join it to the summary.  Works very well, takes about an hour to generate the sql scripts to populate from scratch and helps eliminate a lot of date function calls on the fly.




  •  I was thinking about doing something like that as well, except i ran into the problem of not wanting to have to, *at any time*, deal with the code being broken because it's january first, 2310, or whatever date i choose to stop on.

    I was thinking of doing it for the next 30 years or something, then running a cron everyday to tack on another day to the last one i have, but...yeah. That seems more wtf'y than anything i did above, to me at least.



  • I don't know mySQL but if you can't create a calendar table then take a few other stable tables and join them together until you get enough rows to get the date range.  Then add a sequence number, then add your start date to this using the sequence number to add a day.  Now you have a calendar view you can outer join to.

    In SQL Server it would be something like

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    select</FONT></FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2><FONT color=#ff00ff size=2>dateadd</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>d</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT color=#ff00ff size=2><FONT color=#ff00ff size=2>row_number</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>()</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>over</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>order</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>by</FONT></FONT><FONT size=2> X</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>),</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'01-01-1990'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)

    </FONT></FONT>

    from T1,T2,T3

    to give you a calendar (create a view if you like).

    Now outer join to it.


Log in to reply