SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting these codes to produce years just once

    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 Code:
        <?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>';
        }
        
    ?>
    Last edited by ScallioXTX; Jun 3, 2012 at 16:53. Reason: wrapped code in [php]...[/php]

  2. #2
    SitePoint Addict tom8's Avatar
    Join Date
    Mar 2012
    Location
    New Jersey
    Posts
    310
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Please check into using DISTINCT Command. It will output each year only once. This tutorial gives you an example of using it:

    http://www.plus2net.com/sql_tutorial/distinct_sql.php

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,083
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I made some adjustments, this should work:

    PHP Code:
    <?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:

    1. 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.
    2. Use mysql_connect instead of mysql_pconnect. The latter hogs resources like there is no tomorrow.
    3. 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
    4. Instead of getting the number of results and then looping through that you can also use while ($story = mysql_fetch_assoc($result));, since mysql_fetch_assoc returns false when there is nothing more that fetch; that terminates the loop.
    5. 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
      PHP Code:
      $i 0;
      echo 
      $i++; // 0
      echo $i// 1
      echo ++$i// 2 
      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).
    6. 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.
    7. 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.


    HTH
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    rémon, why would you return DISTINCT YEAR(publicationdate) and order the results by id?

    what if several ids have the same year? which id is that year gonna get sorted by?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);

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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i still think you should drop the ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •