[SOLVED] A question of printing out from a database

[kristen]

Should be fairly simple... but I'm a beginner, and google isn't helping! 

 

I have a table called "Press Releases" that I connect to and then print out so that the releases from each year are separated from each other with a header specifying the year. Right now this is what I am using:

 

<h3>2008</h3>
<?php
$query = "SELECT id, title, date_format(date,'%M %d, %Y') as date, date_format(date, '%Y') as year FROM pr WHERE del='N' and post='Y' ORDER BY id DESC";
$result = prod_query($query);
while ($row = mysql_fetch_array($result)){ 
if ($row['year'] == '2008') { echo "<p><strong><a href=\"/news/press_releases/full.php?id=$row[id]\">$row[date]</a></strong>:  $row[title]</p>"; }
else { echo ''; }
} ?>

 

And so on, using that chunk of code (with a different value for $row[year]) several times to get the results from previous years. The code works fine, but I can't help but think there is a better way to go about it. The other problem is that in 2009, I'll need to manually add another chunk to get 2009 releases. Is there a way to make a loop or something to just print each year and it's releases?

 

Thank you!

[Goose87]

I am quite tired, to forgive me if i type something that makes no sense..but I'd advise going down the variable route.

 

i.e. have www.yourdomain.com?year=2008

 

then use the if(isset($_GET['year'])) {

 

 

then do a mysql_real_escape_string on it.

 

then i'd just put $year in the title, $year into the query ( so you only get ones from 2008 ).

 

Thats a response from a tired man let me know if that helps you at all.  You could always use $_POST if you wanted.

[kevin7]

<h3>2008</h3>
<?php
$query = "SELECT id, title, date_format(date,'%M %d, %Y') as date, date_format(date, '%Y') as year FROM pr WHERE del='N' and post='Y' ORDER BY id DESC";
$result = prod_query($query);
while ($row = mysql_fetch_array($result)){ 
if ($row['year'] == '2008') { echo "<p><strong><a href=\"/news/press_releases/full.php?id=$row[id]\">$row[date]</a></strong>:  $row[title]</p>"; }
else { echo ''; }
} ?>

 

 

this is how i will do it if i were you, it might not the most efficient way, i believe you can enhance it from the sql query.

 

this is what i would do, it's just a pseudocode, not real php.. : ) but i believe it will make sense..

 

$query="select distinct(year) from pr group by year order by year" // select all the year

while ($row=mysql_fetch_array(mysql_query($query)) {
  
   $query2 = "SELECT id, title, date_format(date,'%M %d, %Y') as date, date_format(date, '%Y') as year FROM pr WHERE del='N' and post='Y' and year=".$row['year']." ORDER BY id DESC";
   $result = prod_query($query2);
   
   while ($rowdata = mysql_fetch_array($result)){ 
         echo "<p><strong><a href=\"/news/press_releases/full.php?id=$row[id]\">$row[date]</a></strong>:  $row[title]</p>"; 
   }


}

 

 

i dont think the code is error free, but basically the idea it there, first loop, find all the years, and second loop find all the data to the corresponding year. As a result, you don't have to use if else statment to group all the data in a spicific year.

 

hope this'd help..

 

[kristen]

Thank you Kevin! It is SO close. Here is my current code:

$query = "select date_format(date, '%Y') as year from pr group by year order by year desc"; // select all the years
$result = prod_query($query);
while ($row = mysql_fetch_array($result)) {
echo "<h3>".$row[year]."</h3>";
$query2 = "SELECT id, title, date_format(date,'%M %d, %Y') as date, date_format(date, '%Y') as year FROM pr WHERE del='N' and post='Y' and year='".$row['year']."' ORDER BY id DESC";
echo $query2;
$result2 = prod_query($query2);
while ($row2 = mysql_fetch_array($result2)){    
echo "<p><strong><a href=\"/news/press_releases/full.php?id=$row2[id]\">$row2[date]</a></strong>:  $row2[title]</p>"; 
   }
}

 

You can see my test page here: http://www.naccrra.org/news/press_releases/pr_test.php

 

I believe that the problem is that I can't use year as a part of the WHERE part of the query because it was only just declared in the SELECT part of the query. Is this correct? Anyone have an idea on getting around this?

 

Thank you so much everyone.. I appreciate all of your input!

[GingerRobot]

Dont need the two queries. Just order by the year, then keep track of the year that the last iteration of the loop had. If it's different, then show the year:

 

<?php
$sql =  "SELECT id, title, date_format(date,'%M %d, %Y') as date, date_format(date, '%Y') as year FROM pr WHERE del='N' and post='Y' ORDER BY year,id DESC";
$result = mysql_query($sql) or die(mysql_error().'<br />Query was:'.$sql);
$last_year = '';
while(list($id,$title,$date,$year) = mysql_fetch_row($result)){
    if($last_year != $year){
        echo $year.'<br />';
        $last_year = $year;
    }
    echo $title.'<br />';
}
?>

 

Ill leave you to format.

 

[kristen]

PERFECT!

 

Thanks!