Member Avatar for omoayan

I need someone to assist in finding out the error in this or correct the codes. I want the codes to give me years in the publicationdate from my DB. However, though I am getting the years, I am getting a particular year repeated. If I put journals in for every month of the year 2012 and I call this code, I will have 2012 repeated 12 times. My intention is just to have 2012 just printed once. I hope someone can help to correct this please.

<?php
    //to connect to the database
function db_connect()
{
$result = mysql_pconnect('localhost', 'alayande', 'nicholas');
if (!$result)
return false;
if (!mysql_select_db('topclass_journals'))
return false;
return $result;
}

$conn = db_connect();
if (!$conn)
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
mysql_select_db('topclass_journals');
$query = "select publicationdate  from agricultural_research_journal order by id desc";
$result = mysql_query($query);
$num_result = mysql_num_rows($result);

//to pick the dates
for ($i=0; $i <$num_result; ++$i)
 {
$story = mysql_fetch_array($result);
print '<table width = "385" border="1" align = "center">';
print '<tr>';
print '<td>';
$year = $story['publicationdate'];

$date = $year;
 $my_date = date('Y', strtotime($date));
  echo "<a href=page.php?year=$my_date>$my_date</a>";
print '</td>';
print '</tr>';
print '';
print '<tr><td align="right">';

print '</table>';
}
?>
  • 2 Contributors
  • 6 Replies
  • 166 Views
  • 15 Hours Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

You need to use a while loop as you're retrieving the first record every time with the 'foreach'.

Member Avatar for diafol

Ok, I'll get back to you - I've provided this solution a few times, just can't seem to find it in the forum. Anyway, I thought this was part of your previous thread. It seems to be carrying on from that. If you've finished with that thread, mark it solved (and perhaps put a link to this thread on it).

Back soon.

Edited by diafol
Member Avatar for diafol

Ok, not tested - didn't have your DB, so flying without wings...

<?php

//mysql connection here

$r = mysql_query("SELECT DISTINCT YEAR(publicationdate) AS yr, MONTH(publicationdate) AS mn, MONTHNAME(publicationdate) AS mnn FROM agricultural_research_journal ORDER BY yr DESC, mn DESC");
if(mysql_num_rows($r)){
    $yr = 0; $output = "";
    while($d = mysql_fetch_assoc($r)){
        if($d['yr'] != $yr){
            $output .= ($output == "") ? '' : "\n\t\t</ul>\n\t</div>";
            $output .= "\n\t<h3><a href=\"#\">{$d['yr']}</a></h3>\n\t<div>\n\t\t<ul>";
        }
        $output .= "\n\t\t\t<li>{$d['mnn']}</li>";
        $yr = $d['yr'];         
    }
    $output .= "\n\t\t</ul>\n\t</div>";
}

if(!empty($output)){
    $output = "\n<div id=\"accordion\">$output\n</div>";  
}else{
    $output = "\n<div id=\"msg\"><p>No publications</p></div>"; 
}
?>
<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.18/jquery-ui.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/black-tie/jquery-ui.css" media="all" rel="stylesheet" type="text/css" />
<script>
    $(function() {
        $( "#accordion" ).accordion({
            collapsible: true,
            autoHeight: false, 
            active: false
        });
    });
</script>
</head>
<body>
    <?php echo $output;?>
</body>
</html>
Edited by diafol
Member Avatar for omoayan

Thanks Diafol. I have tried your suggestion but I could not get it to work. However, I have re-written my mysql query and I get the years outputed correctly in mysql but cannot translate this to my php codes. When I run my php on my server, I get empty table whereas the years are to be inputed into the table. I will appreciate your suggestion. Thanks. The new codes I am using is below:

<?php
    //to connect to the database
function db_connect()
{
$result = mysql_connect('localhost', 'alayande', 'nicholas');
if (!$result)
return false;
if (!mysql_select_db('topclass_journals'))
return false;
return $result;
}

$conn = db_connect();
if (!$conn)
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
mysql_select_db('topclass_journals');
$query = "select distinct year (publicationdate) from agricultural_research_journal order by id desc";
$result = mysql_query($query);
$num_result = mysql_num_rows($result);

//to pick the dates
for ($i=0; $i <$num_result; $i++)
 {
$story = mysql_fetch_array($result);
print '<table width = "385" border="1" align = "center">';
print '<tr>';
print '<td>';
$my_date = $story ['YEAR'];
echo "<a href=page.php?year=$my_date>$my_date</a>";
print '</td>';
print '</tr>';
print '<tr><td align="right">';
print '</table>';
}
?>
Edited by omoayan
Member Avatar for omoayan

I have been able to resolve the issue. Thanks for the assistance. The codes I work with now is below:

<?php
    //to connect to the database
function db_connect()
{
$result = mysql_connect('localhost', 'alayande', 'nicholas');
if (!$result)
return false;
if (!mysql_select_db('topclass_journals'))
return false;
return $result;
}

$conn = db_connect();
if (!$conn)
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
mysql_select_db('topclass_journals');
$query = "select distinct year (publicationdate) from agricultural_research_journal order by id desc";
$result = mysql_query($query);
$num_result = mysql_num_rows($result);

//to pick the dates
for ($i=0; $i <$num_result; $i++)
 {
$story = mysql_fetch_assoc($result);
print '<table width = "385" border="1" align = "center">';
print '<tr>';
print '<td>';
$my_date = mysql_result($result, $i);
echo "<a href=page.php?year=$my_date>$my_date</a>";
print '</td>';
print '</tr>';
print '</table>';
}
?>
Member Avatar for diafol

Ok, mark the thread solved. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.