Grouping by date displaying message including date once per group

Discussion in 'PHP' started by lektrikpuke, Jul 30, 2009.

0
  1. #1
    Hi guys,

    Sorry if the topic is long. Any way, I've made script to pull coupons from a db, and I'd like to show them with a heading like 'these coupons expire in x days' above each group (and I'm trying to group them by today, 3 days, 7 days, and one month). I've written the script twice and both times I end up querying the DB twice (or more times) to get the results. I'm ok with that, except it causes problems with pagination (something I like).

    Any help?

    Thanks,

    Rob :D

    $expiration_window = 28;

    $rel_query = "SELECT * FROM table_name WHERE active = 1";
    $rel_query .= " AND STR_TO_DATE(`expires`, '%m/%d/%Y') >= NOW()";
    $rel_query .= " AND STR_TO_DATE(`expires`, '%m/%d/%Y') <= DATE_ADD(CURDATE(), INTERVAL $expiration_window DAY) ORDER BY STR_TO_DATE(`expires`, '%m/%d/%Y') ASC";
    $rel_result = mysql_query($rel_query) or die(mysql_error()); // Get relevant coupon total count
    $rel_result_count=mysql_num_rows($rel_result);
    $NumberOfPages=ceil($rel_result_count/$Limit); // for pagination

    $query = "SELECT * FROM table_name WHERE active = 1";
    $query .= " AND STR_TO_DATE(`expires`, '%m/%d/%Y') >= NOW()";
    $query .= " AND STR_TO_DATE(`expires`, '%m/%d/%Y') <= DATE_ADD(CURDATE(), INTERVAL $expiration_window DAY) ORDER BY STR_TO_DATE(`expires`, '%m/%d/%Y') ASC";
    $query .= " LIMIT " . (($page-1)*$Limit) . ",$Limit";
    $TotalResult = mysql_query($query) or die(mysql_error()); // Get relevant coupons

    if(condition)
    {
    <p>Group Heading goes here</p>
    }
    while ($row = mysql_fetch_object($TotalResult)){ // while coupons
    $expires = $row->expires;

    $timestamp_day = 86400;
    $todays_date = mktime();
    $dateArr = explode("/",$expires);
    $expires_comp = mktime(0,0,0,$dateArr[0],$dateArr[1],$dateArr[2]);
    $timestamp_diff = ($expires_comp - $timestamp_day);

    if(($todays_date + $timestamp_day) >= $expires_comp) { // if matches date requirement - other dates would be compared less than and greater than

    <p>Coupon DATA expressed here</p>
    }
    }
     
    lektrikpuke, Jul 30, 2009 IP
  2. lektrikpuke

    lektrikpuke Well-Known Member

    Messages:
    297
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    113
    #2
    Never mind guys. Finally figured it out. Sorry for the wasted post. Thanks anyway. :D
     
    lektrikpuke, Jul 30, 2009 IP
  3. ezprint2008

    ezprint2008 Well-Known Member

    Messages:
    611
    Likes Received:
    15
    Best Answers:
    2
    Trophy Points:
    140
    Digital Goods:
    1
    #3
    so what was the problem that you figured out?
     
    ezprint2008, Jul 31, 2009 IP
  4. lektrikpuke

    lektrikpuke Well-Known Member

    Messages:
    297
    Likes Received:
    1
    Best Answers:
    1
    Trophy Points:
    113
    #4
    It was really just loop management, which I'm not the best at (but getting better). Keeping all the subsequent loops inside the top while. Adding a loop for time frame selection. Adding some if toggles to only declare the header (for the group) once. I will post my working if you'd like to see it, but the basic look is:

    toggle = false; // had to put the toggles outside the while
    toggle1 = false; // that kind of messed me up for a while

    while (results of query){
    if (satisfies time window){
    if(!toggle){display header; toggle = true;}
    display time window results}
    if (satisfies different time window) {
    if(!toggle1){display header; toggle1 = true;}
    display time window results}
    }

    Once I had my head wrapped around it, it wasn't that difficult. And maybe if I could learn to write it up like I just did (beforehand), I'd be better with loops. :D
     
    lektrikpuke, Jul 31, 2009 IP