Creating a "Year Created" list automatically.

sijis

I have a dvd list that i would like to organize it by the year the film/dvd was created. I would also like to set it up, like this:

1920 - 1929
Film 1
Film 2
1930-1939
Film 3
Film 4
and so on......

in the db (using mysql), i have a field named year of type date in the format: year-month-day (0000-00-00)

Now I could create a table containing the "header" information (eg. 1920-1929), loop through all my dvd_items and use and if/else statements to filter out where each title falls. I'm certain that would work, but i think that its unnecessary and its a ton of if/else statements.

I'm sure that their could be another, easier, way to accomplish this same task. Does anyone have another suggestion?

Thanks.

praxiz

Ofcourse there's an easier way. Its PHP!

Here's what I would do:
<?

$sDB = "dbname";
$sUser = "login";
$sServer = "localhost";
$sPass = "password";

$connection = mysql_connect("$sServer", "$sUser", "$sPass");
$bdd = mysql_select_db("$sDB",$connection);
$result = mysql_query("SELECT * FROM dvd_table ORDER BY year");

// or "ORDER BY year DESC", to make it go the other way around.

?>

<table>
<tr>
<td>name of movie</td>
<td>year</td>
</tr>

<?
if (mysql_num_rows($result) != 0) {
while ($myrow = mysql_fetch_array($result)) {
?>

        <tr>
            <td><? echo $myrow[name]; ?></td>
            <td><? echo $myrow[year]; ?></td>
        </tr>

<? }} ?>

</table>

sijis

yeah, i understand where you are comming from.
but my main issue is the Header part which contains the year range for the list of titles. (eg. 1920's-1929)

that way its a not just a long list dated by year, but a list with headings dated by year.

🙂
so something like this:
my idealistic code:
<table>

<tr><td>Year range<td></tr>
<tr><td>Name of Title<td/><td>Year</td></tr>

</tr>
</table>

my idealistic output:

<table>
<tr><td>1920-1929<td></tr>
<tr><td>A movie title</td><td>Year</td></tr>
<tr><td>A movie title2</td><td>Year</td></tr>
<p>
<tr><td>1930-1939<td></tr>
<tr><td>A movie title</td><td>Year</td></tr>
<tr><td>A movie title</td><td>Year</td></tr>
<p>
<tr><td>1940-1949<td></tr>
<tr><td>A movie title</td><td>Year</td></tr>
.....
</table>

sijis

i'll answer my own quesiton.
here's the code that does the trick.

/* 1900- 2009 */
$i = 1900;
while($i < date(Y)){
	$next = $i+9;

$list = mysql_query("SELECT * FROM Items WHERE left(Items.year,4) BETWEEN '$i' AND '$next' ORDER BY year", $dbServerConn);
echo "<b>$i-$next</b><br>";
while($rows = mysql_fetch_row($list)){
	echo "$rows[1] - title?<br>";
}
echo "<p>.";
$i = $i+10;
}

this could be useful to someone else. 🙂