Displaying certain dates?

[cturner]

I am not sure whether this is a php or mysql question. Anyway I am wanting to display dates from a database, after someone has clicked on a month year in the archives. For example:
Archives
December 2006
January 2007

then to display
December 2006
1st December 2006 // link to a newsletter for that date
31st December 2006 // link to a newsletter for that date
or
January 2007
1st January 2007 // link to a newsletter for that date
31st January 2007 // link to a newsletter for that date

Can someone please show me how I can achieve this? Did I mention that my client needs this to be completed by tomorrow morning (Australia eastern time). Thanks in advance.

[cturner]

I will post some of code that I am working with.
For the archives:
[code=php:0]
require "config.php";
$result = mysql_query ("SELECT month AS `month`, year AS `year`, id FROM newsletters");

if ( mysql_num_rows ( $result ) > 0 )
{
    $c_month = '';
    $c_year  = '';
echo "<h4>ARCHIVES</h4>";
    while ( $row = mysql_fetch_assoc ( $result ) )
    {
        if ( $row['month'] != $c_month ) {
            echo '<a href=newsletters.php?id='.$row['id'].'>' . $row['month'] . ' ';
            $c_month = $row['month'];
        }
        if ( $row['year'] != $c_year ) {
            echo $row['year'] . "</a><br />";
            $c_year = $row['year'];
        }
    }
}
[/code]

For the current newsletter:
[code=php:0]
$select = mysql_query("SELECT * FROM newsletters LIMIT 1") or die ("Could not query because: ".mysql_query());
      while($r = mysql_fetch_array($select)) {
    $txt1 = stripslashes($r['txt1']);
    $txt1 = stripslashes($r['txt1']);
    $txt2 = stripslashes($r['txt2']);
    $txt2 = stripslashes($r['txt2']);
    $txt3 = stripslashes($r['txt3']);
    $txt3 = stripslashes($r['txt3']);
    $txt4 = stripslashes($r['txt4']);
    $txt4 = stripslashes($r['txt4']);
    $txt5 = stripslashes($r['txt5']);
    $txt5 = stripslashes($r['txt5']);
    $txt6 = stripslashes($r['txt6']);
    $txt6 = stripslashes($r['txt6']);
    $txt7 = stripslashes($r['txt7']);
    $txt7 = stripslashes($r['txt7']);
              echo $r['date'].' '.$r['month'].' '.$r['year'];
            echo '<br /><div class=title2><b>'.$r['select1']."</b></div> ".$txt1.
            '<br /><div class=title2><b>'.$r['select2'].'</b></div> '.$txt2.
            '<br /><div class=title2><b>'.$r['select3'].'</b></div> '.$txt3.
            '<br /><div class=title2><b>'.$r['select4'].'</b></div> '.$txt4.
            '<br /><div class=title2><b>'.$r['select5'].'</b></div> '.$txt5.
            '<br /><div class=title2><b>'.$r['select6'].'</b></div> '.$txt6.
            '<br /><div class=title2><b>'.$r['select7'].'</b></div> '.$txt7;
    }
if (mysql_affected_rows() == 0) {
    print "No newsletters to be displayed.";
}
mysql_close();
[/code]

For showing the newsletters dates:
[code=php:0]
$id = $_GET['id'];
      $select = mysql_query("SELECT * FROM newsletters WHERE id = '$id'") or die ("Could not query because: ".mysql_query());
      while($r = mysql_fetch_array($select)) {
        echo '<a href=newsletter.php?date='.$r['date'].'&month='.$r['month'].'&year='.$r['year'].'>'.$r['date'].' '.$r['month'].' '.$r['year'].'</a>';
    }

mysql_close();
[/code]

For showing the newsletter that has been selected:
[code=php:0]
$id = $_GET['id'];
      $select = mysql_query("SELECT * FROM newsletters WHERE date = '$date' AND month = '$month' AND year = '$year'") or die ("Could not query because: ".mysql_query());
      while($r = mysql_fetch_array($select)) {
    $txt1 = stripslashes($r['txt1']);
    $txt1 = stripslashes($r['txt1']);
    $txt2 = stripslashes($r['txt2']);
    $txt2 = stripslashes($r['txt2']);
    $txt3 = stripslashes($r['txt3']);
    $txt3 = stripslashes($r['txt3']);
    $txt4 = stripslashes($r['txt4']);
    $txt4 = stripslashes($r['txt4']);
    $txt5 = stripslashes($r['txt5']);
    $txt5 = stripslashes($r['txt5']);
    $txt6 = stripslashes($r['txt6']);
    $txt6 = stripslashes($r['txt6']);
    $txt7 = stripslashes($r['txt7']);
    $txt7 = stripslashes($r['txt7']);
              echo $r['date'].' '.$r['month'].' '.$r['year'];
            echo '<br /><div class=title2><b>'.$r['select1']."</b></div> ".$txt1.
            '<br /><div class=title2><b>'.$r['select2'].'</b></div> '.$txt2.
            '<br /><div class=title2><b>'.$r['select3'].'</b></div> '.$txt3.
            '<br /><div class=title2><b>'.$r['select4'].'</b></div> '.$txt4.
            '<br /><div class=title2><b>'.$r['select5'].'</b></div> '.$txt5.
            '<br /><div class=title2><b>'.$r['select6'].'</b></div> '.$txt6.
            '<br /><div class=title2><b>'.$r['select7'].'</b></div> '.$txt7;
    }
if (mysql_num_rows($select) == 0) {
    print "There is no newsletter.<br />";
}
mysql_close();
[/code]

Am I doing this the right way?

[anatak]

looks like you are doing it right
I would put the while loop into the if loop
if (mysql_affected_rows() == 0) {
    print "No newsletters to be displayed.";
}else{
while loop {}
}

because if you don't have any rows your script will complain I think.

anatak

PS
why do you do everything two times ?
$txt1 = stripslashes($r['txt1']);
    $txt1 = stripslashes($r['txt1']);

[cturner]

Am I suppose to put something in the while loop. Sorry I am a bit confused about this.

[chronister]

The code looks right, are you getting an error?? What exactly is the problem?



anatak is saying to take the if statement that's at the bottom, and move it so it wraps around the while loop so the while loop only executes if there are results to be dislpayed.
[code]
if (mysql_affected_rows() == 0) {
    print "No newsletters to be displayed.";
}else{

$id = $_GET['id'];
      $select = mysql_query("SELECT * FROM newsletters WHERE date = '$date' AND month = '$month' AND year = '$year'") or die ("Could not query because: ".mysql_query());
      while($r = mysql_fetch_array($select)) {
    $txt1 = stripslashes($r['txt1']);
    $txt1 = stripslashes($r['txt1']);
    $txt2 = stripslashes($r['txt2']);
    $txt2 = stripslashes($r['txt2']);
    $txt3 = stripslashes($r['txt3']);
    $txt3 = stripslashes($r['txt3']);
    $txt4 = stripslashes($r['txt4']);
    $txt4 = stripslashes($r['txt4']);
    $txt5 = stripslashes($r['txt5']);
    $txt5 = stripslashes($r['txt5']);
    $txt6 = stripslashes($r['txt6']);
    $txt6 = stripslashes($r['txt6']);
    $txt7 = stripslashes($r['txt7']);
    $txt7 = stripslashes($r['txt7']);
              echo $r['date'].' '.$r['month'].' '.$r['year'];
            echo '<br /><div class=title2><b>'.$r['select1']."</b></div> ".$txt1.
            '<br /><div class=title2><b>'.$r['select2'].'</b></div> '.$txt2.
            '<br /><div class=title2><b>'.$r['select3'].'</b></div> '.$txt3.
            '<br /><div class=title2><b>'.$r['select4'].'</b></div> '.$txt4.
            '<br /><div class=title2><b>'.$r['select5'].'</b></div> '.$txt5.
            '<br /><div class=title2><b>'.$r['select6'].'</b></div> '.$txt6.
            '<br /><div class=title2><b>'.$r['select7'].'</b></div> '.$txt7;
    }



}
[/code]
You may need to change it a little, but that is the basics of what anatak is saying. IF you put wrap the code in an if statement, then you can avoid processing the while loop(and any errors that may happen) if there are no results