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_connect('localhost', '*****', '********');
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
function db_connect()
{
$conn = mysql_connect('localhost', '*****', '********');
if (!$conn) {
exit('Error: Could not connect to database. Please try again later.');
}
if (!mysql_select_db('topclass_journals')) {
exit('Error: Could not locate database. Please try again later.');
}
return $conn;
}
$conn = db_connect();
$query = "SELECT DISTINCT YEAR(publicationdate) FROM agricultural_research_journal ORDER BY id DESC";
$result = mysql_query($query);
while ($story = mysql_fetch_assoc($result))
{
$year = $story['year'];
echo '<table width = "385" border="1" align = "center">';
echo '<tr>';
echo '<td>';
echo "<a href='page.php?year=$year'>$year</a>";
echo '</td>';
echo '</tr>';
echo '';
echo '<tr><td align="right">';
echo '</table>';
}
?>
A few pointers:
Don’t do error handling of the database connection outside the connect function, because then you have to it every single time. Best practice is to put the connection function in a separate file and then include and call it when needed. That way if it ever needs changing you can change just that file.
Use mysql_connect instead of mysql_pconnect. The latter hogs resources like there is no tomorrow.
Instead of mysql_fetch_array use mysql_fetch_assoc so you just get the column names as key and not numerical keys as well; saves memory
Instead of getting the number of results and then looping through that you can also use while ($story = mysql_fetch_assoc($result));, since [fphp]mysql_fetch_assoc[/fphp] returns false when there is nothing more that fetch; that terminates the loop.
There is a difference between ++$i and $i++. With $i++ it will return the value of $i and then increase $i by one. With ++$i it first increases $i by one and then returns it.
So
What you had, ++$i is not the correct one to use because it skips the first row (starts at row 1 instead of row 0 – in php as in most programming languages arrays start at 0, not at 1).
Instead of getting the year in PHP, let MySQL figure it out, and then use DISTINCT (as @tom8; said) to only get the unique results.
You may want to look at MySQLi or (even better) PDO, since the plain MySQL extension is pretty old and not used very often anymore.
Thanks for the suggestions ScallioXTX. However, when I run your codes, I do not get anything in the output. While your suggestion works perfectly in the mysql query, I find it difficult to translate the results to the php codes. I will appreciate it if you can look at your and provide solutions for me.
Thanks tom8 for your suggestion. The distinct command is working well in mysql query though I am having a difficulty in calling this in my php codes. Any further suggestion in this regard please.
The problem had been solved. This is the code I used:
<?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);
