Thread: displaying db records in groups?

Hi,

i am curious to know if it is possible to list the results of a query in certain groups, for example on my page i have the 10 discount codes ending soonest and i wanted to group all the codes which expired in <1 day, 1 day, 2 days etc, but only display 10 records

i currently have this

PHP Code:

<?php
    $codes = "SELECT codeID,codeLink,storeID,details,expireDate 
    FROM codes 
    WHERE expireDate >= CURDATE() 
    ORDER BY expireDate ASC";
    $query = mysql_query($codes)or die(mysql_error);
    $query_rows = mysql_num_rows($resultset);

    $count = 1;
                
    while($row=mysql_fetch_assoc($query))
    {
        $storeID = $row['storeID'];                
        $date1 = time();
        $date2 = explode("-",$row['expireDate']);
        $expireTime = mktime(0,0,0,$date2[1],$date2[2],$date2[0]);
        $dateDiff = $expireTime - $date1;
        $fullDays = floor($dateDiff/(60*60*24));      
                    
        if ($count <= 10)
        {
            $shortCode_Desc = preg_replace("/.{0}(Valid | Expires | - expires ).*/", "", $row['details']) . "\n";?>
            <?php print "<div class='individual_code_container'>";
            print "<div class='code red'>".$row['codeID']."</div>";
            print "<div class='description small'>$shortCode_Desc</div>";
            print "</div>";
            $count++;
        }
    }
?>

what i want to do is have a heading from the number of days left till the codes expire but instead of giving each one a heading grouping them would look nicer and as the codes could have upto 300 days left im not sure how to get this to work?

if this is at all possible.

my codes table is like so

Code:

TABLE `codes` (
  `codeID` varchar(20) NOT NULL,
  `codeLink` varchar(200) NOT NULL,
  `storeID` varchar(30) NOT NULL,
  `details` mediumtext NOT NULL,
  `expireDate` date NOT NULL,
  PRIMARY KEY  (`codeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

p.s here is a link to my ever improving page

cheers
Luke

try this as your query:

Code:

SELECT codeID,codeLink,storeID,details,expireDate 
FROM codes 
WHERE expireDate >= CURDATE() 
ORDER BY expireDate ASC
LIMIT 0, 10 

hi mate, still the same, is there not a grouping function which i can use in my sql statement?

cheers
Luke

dont know if one of these might help SQL Group Functions

One solution would be to instead of the 'count' statement the you have:

PHP Code:

if ($count <= 10){  } 


In your SQL query, use Saltedm8's:

PHP Code:

LIMIT 0, 10 


Then replace the 'count' with something like:

PHP Code:

if($lastDate!=$row['expireDate']){
  print "<h6>Expires: ";
  echo $row['expireDate']; //format this however you like
  print "</h6>";
  $lastDate=$row['expireDate'];
}
$shortCode_Desc = preg_replace("/.{0}(Valid | Expires | - expires ).*/", "", $row['details']) . "\n";
print "<div class='individual_code_container'>";
print "<div class='code red'>".$row['codeID']."</div>";
print "<div class='description small'>$shortCode_Desc</div>";
print "</div>"; 


What this does is:

a) display the date the first discount code expires

b) checks the expiry date for each discount code.

c) If the expiry date for the next discount code is the same as the previous one, it won't display the date again, but will list this discount code right under the last one.

d) If the expiry date is different, it will display the new date, and list the next discount code under the new date. repeat from b) for all other results one at a time.

I put the expiry dates in a h6 tags so you can see it nice and clearly, but obviously you can format that however you like!

I hope that makes sense?

hi guys, thanks for the suggestions im short of time at the mo but will check them out tomorrow. thanks
Luke

hi,

i have tryied your code and it does exactly as you said it would which is brilliant, one question how do i get it to show the number of days left instrad of the date. i have the code to work out the number of day.

here is the code

PHP Code:

    $codes = "SELECT codeID,codeLink,storeID,details,expireDate 
    FROM codes 
    WHERE expireDate >= CURDATE() 
    ORDER BY expireDate ASC LIMIT 0, 10";
    $query = mysql_query($codes)or die(mysql_error);
    $query_rows = mysql_num_rows($resultset);

                
    while($row=mysql_fetch_assoc($query))
    {
        $storeID = $row['storeID'];                
        $date1 = time();
        $date2 = explode("-",$row['expireDate']);
        $expireTime = mktime(0,0,0,$date2[1],$date2[2],$date2[0]);
        $dateDiff = $expireTime - $date1;
        $fullDays = floor($dateDiff/(60*60*24));      
        
            if($lastDate!=$row['expireDate']){
              print "Expires: ";
              echo $row['expireDate']; //format this however you like
              $lastDate=$row['expireDate'];
            }
            
        $shortCode_Desc = preg_replace("/.{0}(Valid | Expires | - expires ).*/", "", $row['details']) . "\n";
        print "<div class='individual_code_container'>";
        print "<div class='code red'>".$row['codeID']."</div>";
        print "<div class='description small'>$shortCode_Desc</div>";
        print "</div>";
    } 


and i tried this

PHP Code:

    $codes = "SELECT codeID,codeLink,storeID,details,expireDate 
    FROM codes 
    WHERE expireDate >= CURDATE() 
    ORDER BY expireDate ASC LIMIT 0, 10";
    $query = mysql_query($codes)or die(mysql_error);
    $query_rows = mysql_num_rows($resultset);

                
    while($row=mysql_fetch_assoc($query))
    {
        $storeID = $row['storeID'];                
        
            if($lastDate!=$row['expireDate'])
            {
                print "Expires: ";
                #echo $row['expireDate']; //format this however you like
                $lastDate=$row['expireDate'];
                $date1 = time();
                $date2 = explode("-",$row['expireDate']);
                $expireTime = mktime(0,0,0,$date2[1],$date2[2],$date2[0]);
                $dateDiff = $expireTime - $date1;
                $fullDays = floor($dateDiff/(60*60*24)); 
                echo $fullDays;
            }
            
        $shortCode_Desc = preg_replace("/.{0}(Valid | Expires | - expires ).*/", "", $row['details']) . "\n";
        print "<div class='individual_code_container'>";
        print "<div class='code red'>".$row['codeID']."</div>";
        print "<div class='description small'>$shortCode_Desc</div>";
        print "</div>";
    } 


but it prints out all the days at the top and not grouped?

any ideas
thanks
Luke

ok ive solved that little problem but have another

now im trying to get the store name from another table which matches the current storeID but its showing the first one but thats it?
here is the code

PHP Code:

    $codes = "SELECT codeID,codeLink,storeID,details,expireDate
    FROM codes 
    WHERE expireDate >= CURDATE() 
    ORDER BY expireDate ASC LIMIT 0, 10";
    $query = mysql_query($codes)or die(mysql_error);
    $query_rows = mysql_num_rows($resultset);
                
    while($row=mysql_fetch_assoc($query))
    {
        $storeID = $row['storeID'];                
        $date1 = time();
        $date2 = explode("-",$row['expireDate']);
        $expireTime = mktime(0,0,0,$date2[1],$date2[2],$date2[0]);
        $dateDiff = $expireTime - $date1;
        $fullDays = floor($dateDiff/(60*60*24));      
        
            if($lastDate!=$row['expireDate'])
            {
                $query = "SELECT storeName, storeLink 
                FROM stores
                WHERE storeID = '$storeID'";
                $query_results = mysql_query($query)or die(mysql_error);
                $new_row = mysql_fetch_array($query_results);
                $shortCode_Desc = preg_replace("/.{0}(Valid | Expires | - expires ).*/", "", $row['details']) . "\n";?>
        
                <div style='float:left; width:300px; height:20px; font-family:"Trebuchet MS"; font-size:12px; font-weight:bold;'>
                <?php
                  if($fullDays < '1')
                  {
                      $plural = 'Hurry!! Less Than 1 Day Left';
                  }
                  elseif($fullDays == '1')
                  {
                        $plural = 'Expiring In 1 Day';
                  }
                    else
                  {
                        $plural = 'Expiring In '.$fullDays.' Days';
                  }
                  echo $plural;
                  print "</div>";
                  $lastDate=$row['expireDate'];              
            }        

            ?>
            <a href="<?php echo htmlspecialchars($row['codeLink'])?>" />
            <div class='individual_code_container'>
               <div class='code red'><?php echo $row['codeID']." ".$new_row['storeName']?></div>
            <div class='description small'><?php echo $shortCode_Desc?></div>
            </div></a><?php
    }

any ideas
thanks
Luke

Hi Luke,

You can do all that in one query with JOIN.

Something like (not tested!):

Code:

SELECT codes.codeID,codes.codeLink,codes.storeID,codes.details,codes.expireDate,stores.storeName,stores.storeLink
    FROM codes 
    WHERE codes.expireDate >= CURDATE() 
    JOIN stores ON stores.storeID = codes.storeID
    GROUP BY codes.expireDate ASC LIMIT 0, 10

You can read up on the types of joins here: SQL Joins. They are VERY helpful when making large database driven sites!

Stu