Getting these codes to produce years just once

Question

omoayan 0 Newbie Poster

14 Years Ago

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>';
}
?>

php

2 Contributors
6 Replies
186 Views
15 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by Member #120589

Dani AI

Generated 6 Months Ago

Short summary and what fixed it: the repeated “2012” came from printing every result row as-is (one table row per journal). correctly nudged the logic toward iterating the result set, and eventually used a query that returns only one row per year — that removed duplicates. Common pitfalls in the thread were mixing different mysql_* fetch functions and not aliasing the YEAR() expression before trying to read it by name in PHP.

Practical points and troubleshooting not shown in the posts

Let the database return unique years (DISTINCT or GROUP BY YEAR(publicationdate)), or fetch all dates and deduplicate in PHP with an associative key set.
When selecting YEAR(publicationdate) give it an alias (AS yr) so $row['yr'] will exist; otherwise the associative key may be the full expression name.
Don’t order by id when only years are selected — use ORDER BY YEAR(publicationdate) DESC (or ORDER BY yr DESC if aliased).
Avoid mixing fetch APIs (mysql_fetch_assoc / mysql_result etc.). Use one consistent fetch method or a single fetchAll-style approach.
Escape output: wrap link text/attributes with htmlspecialchars (and urlencode the query value) to avoid broken HTML or XSS.

Small modernization and a simple pattern to follow
The mysql_* extension used in the thread is deprecated/removed in current PHP. Use PDO or mysqli and fetch the single column of years, then loop and print links. Example pattern (PDO, concise):

<?php
$pdo = new PDO('mysql:host=localhost;dbname=topclass_journals;charset=utf8','user','pass',[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$years = $pdo->query("SELECT DISTINCT YEAR(publicationdate) AS yr FROM agricultural_research_journal ORDER BY yr DESC")->fetchAll(PDO::FETCH_COLUMN);
foreach($years as $yr){
    echo '<a href="page.php?year='.urlencode($yr).'">'.htmlspecialchars($yr,ENT_QUOTES)."</a>\n";
}

These changes keep the SQL focused, the PHP simple, and avoid duplicate output while improving security and forward compatibility.

All 6 Replies

Member #120589

14 Years Ago

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

Member #120589

14 Years Ago

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 14 Years Ago by Member #120589

Reply to this topic

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.

Member #120589 · Answer 1 · 2012-06-03T21:22:16+00:00

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>

omoayan 0 Newbie Poster · Answer 2 · 2012-06-04T10:30:12+00:00

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>';
}
?>

omoayan 0 Newbie Poster · Answer 3 · 2012-06-04T11:42:57+00:00

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 #120589 · Answer 4 · 2012-06-04T11:47:09+00:00

Member #120589

14 Years Ago

Ok, mark the thread solved. :)

Getting these codes to produce years just once

Dani AI

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers