[SOLVED] News Navigation

[Ne0_Dev]

Hi Guys,

 

I was just wondering if anyone can help me out on the best way (if possible) to achieve the following:

 

I have a mysql database table that holds news articles with the following fields:

 

id

Title

createdon

details

 

My question is how can I generate an unordered list using the year stored in the "createdon" field as the top level heading (h3) and the title as links to the article details in the <li> elements.  The idea being that when a new article is addded it appears under the correct year and when we move into a new year a new menu is automatically created.  An example of the list structure can be seen below:

 

<h3 class="menuheader expandable">2008</h3>

<ul class="categoryitems">

 

      <li><a href="news.php?article_id=19">Marketing to IT & Telecoms decision makers </a></li>

        <li><a href="news.php?article_id=18">Revised Guide to European Data Laws for Direct Marketing</a></li>

        <li><a href="news.php?article_id=9">Mobilising the Enterprise Report</a></li>

        <li><a href="news.php?article_id=8">New Global Corporations...</a></li>

    </ul>

 

<h3 class="menuheader expandable">2007</h3>

 

<ul class="categoryitems">

    <li><a href="news.php?article_id=15">Business Applications in the UK and EIRE</a></li>

        <li><a href="news.php?article_id=14">Trends of Mobile Devices... </a></li>

        <li><a href="news.php?article_id=13">Decision Makers...</a></li>

        <li><a href="news.php?article_id=12">Mobile & Device Management...</a></li>

        <li><a href="news.php?article_id=10">IT and Telecoms...</a></li>

 

    </ul>

 

I hope this makes sense and any suggestions would be most appreciated.

[Buddski]

This might do the trick I havent tested it but give it a shot

 

<?php

$sql = "SELECT * FROM news ORDER BY year DESC";
$sql_query = mysql_query($sql);

$cur_year = null;

if (mysql_num_rows($sql_query) > 0) {
while ($data = mysql_fetch_array($sql_query)) {
	if ($cur_year === null) {
		// Output the First year
		echo '
		<h3 class="menuheader expandable">'.$data['createdon'].'</h3>
		<ul class="categoryitems">';
	}
	if ($data['createdon'] != $cur_year && $cur_year !== null) {
		// Check to see if THIS row is in the current year..if not close previous year and start new one
		echo '
		</ul>

		<h3 class="menuheader expandable">2008</h3>
		<ul class="categoryitems">';
	}
     echo '<li><a href="news.php?article_id='.$data['id'].'">'.$data['Title'].'</a></li>'."\n";
     $cur_year = $data['createdon'];
}
//close the last year
echo '</ul>';
} else {
echo 'No Rows';
}

?>

[Ne0_Dev]

Hi Buddski,

 

Many Thanks for your prompt reply it was most appreciated.  I have implemented your code suggestion and am now getting the following error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\cms\news.php on line 21

No Rows

 

Line 21 is

 

if (mysql_num_rows($sql_query) > 0) {

 

I have had a look myself but can't figure it out.

[Buddski]

is your mysql db table called news? that could be the problem

[Ne0_Dev]

the mysql table is called tbl_newsarticles, however i have adjusted the code accordingly to reflect this name.

 

The fields within the table are called:

 

id (Int) PK auto_inc

title (varchar)

details (text)

created (datetime)

 

Thanks.

[Buddski]

Replace

$sql_query = mysql_query($sql);

with

$sql_query = mysql_query($sql) or die(mysql_error());

 

And tell me what that says because the query is failing

[Ne0_Dev]

Hi Buddski,

 

I have added the error code and found that the query was failing at the "ORDER BY year DESC" line.  This was due to the fact that I had the "year" column called "created", so after ammendeding the query it started to run and output the elements in a list.  There is is only one other refinement I need to make and that is to get the articles with the same year to appear under the one heading in <li> tags.

 

Here's the code I have so far:

 

<?php

$sql = "SELECT * FROM tbl_newsarticles ORDER BY created DESC";
$sql_query = mysql_query($sql) or die(mysql_error());

$cur_year = null;

if(mysql_num_rows($sql_query) > 0) {
while ($data = mysql_fetch_array($sql_query)) {
	if ($cur_year === null) {
		// Output the First year
		echo '
		<h3 class="menuheader expandable">'.date('Y', strtotime($data['created'])).'</h3>
		<ul class="categoryitems">';
	}
	if ($data['created'] != $cur_year && $cur_year !== null) {
		// Check to see if THIS row is in the current year..if not close previous year and start new one
		echo '
		</ul>

		<h3 class="menuheader expandable">'.date('Y', strtotime($data['created'])).'</h3>
		<ul class="categoryitems">';
	}
     echo '<li><a href="news.php?article_id='.$data['id'].'">'.$data['title'].'</a></li>'."\n";
     $cur_year = $data['created'];
}
//close the last year
echo '</ul>';
} else {
echo 'No Rows';
}

?>

[Buddski]

All done and tested i missed the part were the db `created` field was a datetime not a year value  Ooops

<?php
$sql = "SELECT * FROM tbl_newsarticles ORDER BY created DESC";
$sql_query = mysql_query($sql) or die(mysql_error());

$cur_year = null;

if(mysql_num_rows($sql_query) > 0) {
while ($data = mysql_fetch_array($sql_query)) {
	$date_str = date('Y', strtotime($data['created']));
	if ($cur_year === null) {
		// Output the First year
		echo '
		<h3 class="menuheader expandable">'.$date_str.'</h3>
		<ul class="categoryitems">';
	}
	if ($date_str != $cur_year && $cur_year !== null) {
		// Check to see if THIS row is in the current year..if not close previous year and start new one
		echo '
		</ul>

		<h3 class="menuheader expandable">'.$date_str.'</h3>
		<ul class="categoryitems">';
	}
     echo '<li><a href="news.php?article_id='.$data['id'].'">'.$data['title'].'</a></li>'."\n";
     $cur_year = $date_str;
}
//close the last year
echo '</ul>';
} else {
echo 'No Rows';
}

?>

[Ne0_Dev]

Buddski,

 

Thanks for your help on this it has been mosted appreciated as I am still getting to grips with php and whilst I knew what I wanted to achieve, I couldn't work out the correct syntax!

 

I've tested the code and all works great, Thanks again!

[Buddski]

No worries at all, Glad to help.