Nested Repeat By Date

Hi Jason,

I am trying to setup a nested repeat by dates.
Example:

The main category pulls the dates from the product table by month and year.

SELECT YEAR(DateAdd) AS 'year', MONTH(DateAdd) AS 'month'
FROM VideoDictionary
GROUP BY year, month
ORDER BY year DESC, month DESC

So this will list the most recent 12 month entries as Month/Year

Now the nested repeat will pull the new products that were added within the relative Month/Year.

SELECT vdID, DateAdd, vidcateID, Title, Photo, Display
FROM VideoDictionary
WHERE Display = 1 AND DateAdd = colname
GROUP BY DateAdd DESC

Is this correct?

thx
Eric

SELECT vdID, DateAdd, vidcateID, Title, Photo, Display
FROM VideoDictionary
WHERE Display = 1 AND YEAR(DateAdd) = yearparam AND MONTH(DateAdd) = monthparam

Thanks Ray!

I am almost there :)

Here is the code I have in place, but I think I may be missing something.


$maxRows_rsMonths = 12;
$pageNum_rsMonths = 0;
if (isset($_GET['pageNum_rsMonths'])) {
$pageNum_rsMonths = $_GET['pageNum_rsMonths'];
}
$startRow_rsMonths = $pageNum_rsMonths * $maxRows_rsMonths;

mysql_select_db($database_connBLUR, $connBLUR);
$query_rsMonths = "SELECT YEAR(DateAdd) AS 'year', MONTH(DateAdd) AS 'month' FROM VideoDictionary GROUP BY year, month ORDER BY year DESC, month DESC";
$query_limit_rsMonths = sprintf("%s LIMIT %d, %d", $query_rsMonths, $startRow_rsMonths, $maxRows_rsMonths);
$rsMonths = mysql_query($query_limit_rsMonths, $connBLUR) or die(mysql_error());
$row_rsMonths = mysql_fetch_assoc($rsMonths);

if (isset($_GET['totalRows_rsMonths'])) {
$totalRows_rsMonths = $_GET['totalRows_rsMonths'];
} else {
$all_rsMonths = mysql_query($query_rsMonths);
$totalRows_rsMonths = mysql_num_rows($all_rsMonths);
}
$totalPages_rsMonths = ceil($totalRows_rsMonths/$maxRows_rsMonths)-1;
?>
<?php
// RepeatSelectionCounter_1 Initialization
$RepeatSelectionCounter_1 = 0;
$RepeatSelectionCounterBasedLooping_1 = false;
$RepeatSelectionCounter_1_Iterations = "12";
?>
<?php
// RepeatSelectionCounter_2 Initialization
$RepeatSelectionCounter_2 = 0;
$RepeatSelectionCounterBasedLooping_2 = false;
$RepeatSelectionCounter_2_Iterations = "-1";
?>
<?php

$page_title = $row_rsMeta['title'];
$page_description = $row_rsMeta['description'];
?>
<?php include("_header.php"); ?>
<section class="row pagebound">
<!-- content -->

<div class="row">
<div class="large-12 columns">
<h1><img src="assets/icons/icons-green11.png"/> Video Library New Releases</h1>
</div>
</div>
<div class="row">

<?php
// RepeatSelectionCounter_1 Begin Loop
$RepeatSelectionCounter_1_IterationsRemaining = $RepeatSelectionCounter_1_Iterations;
while($RepeatSelectionCounter_1_IterationsRemaining--){
if($RepeatSelectionCounterBasedLooping_1 || $row_rsMonths){
?>
<div class="large-12 columns">

<h2> <?php echo $row_rsMonths['month']; ?>/<?php echo $row_rsMonths['year']; ?></h2>
<?php
$yearparam_rsVideo = "-1";
if (isset($_GET['DateAdd'])) {
$yearparam_rsVideo = $_GET['DateAdd'];
}
$monthparam_rsVideo = "-1";
if (isset($_GET['DateAdd'])) {
$monthparam_rsVideo = $_GET['DateAdd'];
}
mysql_select_db($database_connBLUR, $connBLUR);
$query_rsVideo = sprintf("SELECT vdID, DateAdd, vidcateID, Title, Photo, Display FROM VideoDictionary WHERE Display = 1 AND YEAR(DateAdd) = %s AND MONTH(DateAdd) = %s ORDER BY DateAdd Desc", GetSQLValueString($yearparam_rsVideo, "int"),GetSQLValueString($monthparam_rsVideo, "int"));
$rsVideo = mysql_query($query_rsVideo, $connBLUR) or die(mysql_error());
$row_rsVideo = mysql_fetch_assoc($rsVideo);
$totalRows_rsVideo = mysql_num_rows($rsVideo);
?>
<?php

$_GET['DateAdd'] = $row_rsMonths['DateAdd'];

?>
<?php
// RepeatSelectionCounter_2 Begin Loop
$RepeatSelectionCounter_2_IterationsRemaining = $RepeatSelectionCounter_2_Iterations;
while($RepeatSelectionCounter_2_IterationsRemaining--){
if($RepeatSelectionCounterBasedLooping_2 || $row_rsVideo){
?>
<div><?php echo $row_rsVideo['Title']; ?></div>
<?php
} // RepeatSelectionCounter_2 Begin Alternate Content
else{
?>
<?php } // RepeatSelectionCounter_2 End Alternate Content
if(!$RepeatSelectionCounterBasedLooping_2 && $RepeatSelectionCounter_2_IterationsRemaining != 0){
if(!$row_rsVideo && $RepeatSelectionCounter_2_Iterations == -1){$RepeatSelectionCounter_2_IterationsRemaining = 0;}
$row_rsVideo = mysql_fetch_assoc($rsVideo);
}
$RepeatSelectionCounter_2++;
} // RepeatSelectionCounter_2 End Loop
?>


</div>
<?php
} // RepeatSelectionCounter_1 Begin Alternate Content
else{
?>
<?php } // RepeatSelectionCounter_1 End Alternate Content
if(!$RepeatSelectionCounterBasedLooping_1 && $RepeatSelectionCounter_1_IterationsRemaining != 0){
if(!$row_rsMonths && $RepeatSelectionCounter_1_Iterations == -1){$RepeatSelectionCounter_1_IterationsRemaining = 0;}
$row_rsMonths = mysql_fetch_assoc($rsMonths);
}
$RepeatSelectionCounter_1++;
} // RepeatSelectionCounter_1 End Loop
?>

Inside the outer loop you need to define the parameters for the internal recordset to come from the main recordset. So instead of:

<?php

$yearparam_rsVideo = "-1";

if (isset($_GET['DateAdd'])) {

$yearparam_rsVideo = $_GET['DateAdd'];

}

$monthparam_rsVideo = "-1";

if (isset($_GET['DateAdd'])) {

$monthparam_rsVideo = $_GET['DateAdd'];

}

mysql_select_db($database_connBLUR, $connBLUR);

$query_rsVideo = sprintf("SELECT vdID, DateAdd, vidcateID, Title, Photo, Display FROM VideoDictionary WHERE Display = 1 AND YEAR(DateAdd) = %s AND MONTH(DateAdd) = %s ORDER BY DateAdd Desc", GetSQLValueString($yearparam_rsVideo, "int"),GetSQLValueString($monthparam_rsVideo, "int"));

$rsVideo = mysql_query($query_rsVideo, $connBLUR) or die(mysql_error());

$row_rsVideo = mysql_fetch_assoc($rsVideo);

$totalRows_rsVideo = mysql_num_rows($rsVideo);

?>

<?php



$_GET['DateAdd'] = $row_rsMonths['DateAdd'];



?>

you would use:

<?php



$_GET['yearAdd'] = $row_rsMonths['year'];

$_GET['monthAdd'] = $row_rsMonths['month'];



?>

<?php

$yearparam_rsVideo = "-1";

if (isset($_GET['yearAdd'])) {

$yearparam_rsVideo = $_GET['yearAdd'];

}

$monthparam_rsVideo = "-1";

if (isset($_GET['monthAdd'])) {

$monthparam_rsVideo = $_GET['monthAdd'];

}

mysql_select_db($database_connBLUR, $connBLUR);

$query_rsVideo = sprintf("SELECT vdID, DateAdd, vidcateID, Title, Photo, Display FROM VideoDictionary WHERE Display = 1 AND YEAR(DateAdd) = %s AND MONTH(DateAdd) = %s ORDER BY DateAdd Desc", GetSQLValueString($yearparam_rsVideo, "int"),GetSQLValueString($monthparam_rsVideo, "int"));

$rsVideo = mysql_query($query_rsVideo, $connBLUR) or die(mysql_error());

$row_rsVideo = mysql_fetch_assoc($rsVideo);

$totalRows_rsVideo = mysql_num_rows($rsVideo);

?>

Perfect! That works - THANKS Ray!