SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    While loop error - doubles the output from a MySQL table - yikes!

    Hi,

    Would appreciate any help with this.

    I have the following SQL table:

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `enews` (
      `id` smallint(6) NOT NULL auto_increment,
      `title` varchar(100) NOT NULL default '',
      `descr` text NOT NULL,
      `img1th1` varchar(100) NOT NULL default '',
      `img1th2` varchar(100) NOT NULL default '',
      `date` date NOT NULL default '0000-00-00',
      `slug` varchar(255) NOT NULL default '',
      `user_id` smallint(6) NOT NULL default '0',
      PRIMARY KEY  (`id`)
    ) TYPE=MyISAM  AUTO_INCREMENT=4 ;
     
    --
    -- Dumping data for table `enews`
    --
     
    INSERT INTO `enews` (`id`, `title`, `descr`, `img1th1`, `img1th2`, `date`, `slug`, `user_id`) VALUES
    (1, 'This is a test news item 3', 'Bla bla bla test 4', 'newsitem.jpg', '', '2009-01-13', '', 1),
    (2, 'test this works ok 2', 'testertestersffsdf', '', '', '2009-03-23', 'test-this-works-ok-2', 1),
    (3, 'test 1', 'test 2', '', '', '2009-03-11', 'test-1', 1);


    I want to retrieve the data from this SQL table, so ultimately my HTML will look like:


    HTML Code:
    <div id="archive_bg" style="float: left;">
    
    	<dt><a href="#"> March 2009</a></dt>
    
    		<dd>
    
    			<ul>
    			<li><a href="/blog/test-this-works-ok-2">test this works ok 2</a><br /><span>March 2009</span></li>
    			<li><a href="/blog/test-1">test 1</a><br /><span>March 2009</span></li>
    			</ul>
    
    		</dd>
    		
    	<dt><a href="#"> January 2009</a></dt>
    
    		<dd>
    
    			<ul>
    			<li><a href="/blog/">This is a test news item 3</a><br /><span>January 2009</span></li>
    			</ul>
    
    		</dd>
    		
    	</dl>
    				
    </div>
    I initially thought I'd cracked it, using the following PHP but it doesn't quite work. It seems if a certain month has 2 records it will output the entire <dt> element twice

    PHP Code:
    echo '<div id="archive_bg" style="float: left;">

                    <dl>'
    ;

                            
    $res mysql_query("SELECT enews.title, enews.date FROM enews ORDER BY enews.date DESC");

                            while (
    $row mysql_fetch_array($res)) {

                            
    //Check if this row matches the one of the row last displayed
                              
    if (empty($last) || $last != $row['date']) {
                                
    $last $row['date'];
                              }                        

                                echo 
    '<dt><a href="#"> '.date("F Y",strtotime($row['date'])). '</a></dt>

                                <dd>

                                    <ul>'
    ;

                                    
    $dates date("m",strtotime($row['date']));

                                    
    $res2 mysql_query("SELECT * FROM enews WHERE MONTH(date) = $dates"); ***************************************

                                    while (
    $row2 mysql_fetch_array($res2)) {

                                        echo 
    '<li><a href="/blog/'.$row2['slug'].'">'.$row2['title'].'</a></li>';

                                        }

                                    echo 
    '</ul>

                                </dd>'
    ;
                                
                            }

                    echo 
    '</dl>
                    
                </div>'

    I'm convinced the problem comes from the while loop in the middle(marked with a few *'s).
    Code MySQL:
     

    I'm sure this is a very easy fix. Can someone point me in the right direction?



    Many thanks.

  2. #2
    Trash Boat mkoenig's Avatar
    Join Date
    Aug 2007
    Posts
    1,232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see anything wrong right off hand?

    It looks like you've renamed the results results2 and what not. So it doesn't stop the current query.

    I would consider removing as much as possible, making it as simple as possible and work my way forward in steps. thats the only thing that really helps me.

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good shout.

    I did think of removing most of the markup to make it easier to debug.

  4. #4
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is with your logic.

    What you intended to do is group the articles based on their month.
    What you are actually doing is looping through every article, each time finding all articles in the same month. So if you have two articles in the same month, then you are going to have 2 lists on your page showing articles in that month.

    Also, what happens with articles in different years? You'll be pulling articles from one month in different years...

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Maybe something like this would be better suited?

    PHP Code:
    <?php
    $sSQL 
    "SELECT slug, `date`, DATE_FORMAT(`date`, '%M') AS `month` FROM news ORDER BY `date` DESC";
    $rResult mysql_query($sSQL);
    while (
    mysql_fetch_assoc($rResult) == $aRow)
    {
        
    $aNewsCollection[$aRow['month']][] = array(
            
    'slug'    =>    $aRow['slug'],
            
    'date'    =>    $aRow['date']
        );
    }
    /*
    $aNewsCollection = array(
        'April' => array(
            1 => array(
                'slug' => 'foo',
                'date' => '2006-04-12 13:47:44'
            ),
            2 => array(
                'slug' => 'bar',
                'date' => '2006-04-12 13:47:44'
            )
        ),
        'July' => array(
            1 => array(
                'slug' => 'foo',
                'date' => '2006-07-12 13:47:44'
            ),
            2 => array(
                'slug' => 'bar',
                'date' => '2006-07-12 13:47:44'
            )
        ),
    );
    */
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Here's what I'd do.

    First, get all articles from the database, ordering them by month. Put them into an array.

    Then PHP already has a sorted array to deal with, and doesn't need to put extra logic into loops.

    For example:
    PHP Code:
                echo '<div id="archive_bg" style="float: left;">
                    <dl>'
    ;
                            
    $res mysql_query("SELECT title, slug, date, YEAR(date) NewsYear, MONTH(date) NewsMonth FROM enews ORDER BY date DESC");
                            
    $Records = array();
                            while (
    $row mysql_fetch_assoc($res)) {
                                if(!
    array_key_exists($row['NewsYear'], $Records)) $Records[$row['NewsYear']] = array();
                                if(!
    array_key_exists($row['NewsMonth'], $Records[$row['NewsYear']])) $Records[$row['NewsYear']][$row['NewsMonth']] = array();
                                
    $Records[$row['NewsYear']][$row['NewsMonth'][] = array('title' => $row['title'], 'slug' => $row['slug'], 'date' => $row['date']);
                            }
                            foreach(
    $Records as $Year => $Months){
                                foreach(
    $Months as $Month => $Items){        
                                    echo 
    '<dt><a href="#"> '.date('F Y',strtotime($Items[0]['date'])). '</a></dt>';
                                }
                                echo 
    '<dd><ul>';
                                foreach(
    $Items as $Item){
                                    echo 
    '<li><a href="/blog/'.$Item['slug'].'">'.$Item['title'].'</a></li>';
                                }
                                echo 
    '</ul></dd>';
                            }
                    echo 
    '
            </dl>
                </div>'

    Untried, untested - but that's the kind of theory I'm on about.

    Off Topic:

    BEAT ME BY ONE BLOOMIN' MINUTE!

    Same theory involved too!
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, what a response, from the big guns too!

    Anthony, how do I then add the markup to the while loop of the script you have made? I think I might be missing something obvious.

    Thanks for the reply arkinstall too and the code you have put together. Sadly, I was getting a parse error on this line:
    PHP Code:
    $Records[$row['NewsYear']][$row['NewsMonth'][] = array('title' => $row['title'], 'slug' => $row['slug'], 'date' => $row['date']); 
    Man, I really need to practice more.

  8. #8
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:

    Hehehe, it's always nice to beat Jake, maybe it's because he's always right first go!


    To iterate through the results you would just...
    PHP Code:
    <?php
    foreach ($aNewsCollection as $sMonth => $aNewsItems)
    {
        
    printf(
            
    '<h4>%s</h4>',
            
    $sMonth
        
    );
        foreach(
    $aNewsItems as $aNewsItem)
        {
            
    printf(
                
    '<strong>%s: </strong><p>%s</p>',
                
    $aNewsItem['date'],
                
    $aNewsItem['slug']
            );
        }
    }
    ?>
    Obviously you would request more information in your query than I have.

    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #9
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again Anthony.

    Seems to be just a wee error:

    PHP Code:
    WarningInvalid argument supplied for foreach() in /includes/template.inc.tpl on line 262 
    There's no other info on the error sadly

  10. #10
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by invision2 View Post
    Thanks for the reply arkinstall too and the code you have put together. Sadly, I was getting a parse error on this line:
    PHP Code:
    $Records[$row['NewsYear']][$row['NewsMonth'][] = array('title' => $row['title'], 'slug' => $row['slug'], 'date' => $row['date']); 
    Man, I really need to practice more.
    Sorry about that; bracket missing!

    PHP Code:
    $Records$row['NewsYear'] ][ $row['NewsMonth'] ][] = array('title' => $row['title'], 'slug' => $row['slug'], 'date' => $row['date']); 
    The reason I have the year as a parent to the month array is because, if you just take the month as an identifier, then articles from, say, February 2009 will be in the same place as February 2008, etc. By taking the year into account, you're separating them by year.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  11. #11
    SitePoint Evangelist simshaun's Avatar
    Join Date
    Apr 2008
    Location
    North Carolina
    Posts
    438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'll need to modify this a bit, but..
    PHP Code:
    <?php
    /** Used this array for testing.
    $enews = array(
        '2009' => array(
            'January' => array(
                array('slug' => 'Test', 'title' => 'Jan Article 1')
                ,array('slug' => 'Test 2', 'title' => 'Jan Article 2')
            )
            ,'February' => array(
                array('slug' => 'Test', 'title' => 'Feb Article 1')
                ,array('slug' => 'Test 2', 'title' => 'Feb Article 2')
            )
        )
    );
    exit;
    */


    $enews = array();
    $result mysql_query("
        SELECT
            slug
            ,title
            ,DATE_FORMAT(date, '%Y') AS year
            ,DATE_FORMAT(date, '%M') AS month
        FROM
            enews
        ORDER BY
            date DESC
            ,title
    "
    ) or die(mysql_error());
    if (
    mysql_num_rows($result) > 0):
        while (
    $row mysql_fetch_assoc($result)) {
            
    $enews[$row['year']][$row['month']][] = array('slug' => $row['slug'], 'title' => $row['title']);
        }
    endif;

    if (
    count($enews) > 0) {
        foreach (
    $enews AS $year => $months) {
            foreach (
    $months AS $month => $articles) {
                echo 
    '<a href="#"> ' $month ' ' $year '</a>';
                echo 
    '<ul>';
                foreach (
    $articles AS $article) {
                    echo 
    '<li><a href="/blog/'$article['slug'] .'">'$article['title'] .'</a></li>';
                }
                echo 
    '</ul>';
            }
        }
    }
    ?>

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Arkinstall,

    I tried the code you supplied, but seem to have a slight issue.

    It only displays the post slug from January, and not March's 2 slugs.

    My output is:

    HTML Code:
    <div id="archive_bg" style="float: left;">
                    <dl><dt><a href="#"> March 2009</a></dt><dt><a href="#"> January 2009</a></dt><dd><ul><li><a href="/blog/">This is a test news item 3</a></li></ul></dd>
            </dl>
                </div>
    But when I put the query into phpMyAdmin, I get 3 results(which I expected).


    Is there a wee tweak required?


    Thanks again for all your help.

  13. #13
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got a bit of a mess-up with the curly brackets when I edited it.

    This should work :
    PHP Code:
    echo '<div id="archive_bg" style="float: left;"><dl>';
    $res mysql_query("SELECT title, slug, date, YEAR(date) NewsYear, MONTH(date) NewsMonth FROM enews ORDER BY date DESC");
    $Records = array();
    while (
    $row mysql_fetch_assoc($res)) {
        if(!
    array_key_exists($row['NewsYear'], $Records)){
            
    $Records[$row['NewsYear']] = array();
        }
        if(!
    array_key_exists($row['NewsMonth'], $Records[$row['NewsYear']])){
            
    $Records[$row['NewsYear']][$row['NewsMonth']] = array();
        }
        
    $Records[$row['NewsYear']][$row['NewsMonth']][] = $row;
    }
    foreach(
    $Records as $Year => &$Months){
        foreach(
    $Months as $Month => &$Items){        
            
    printf('<dt><a href="#">%s</a></dt>'date('F Y',strtotime($Items[0]['date'])));
            echo 
    '<dd><ul>';
            foreach(
    $Items as &$Item){
                
    printf('<li><a href="/blog/%s">%s</a></li>'$Item['slug'], $Item['title']);
            }
            echo 
    '</ul></dd>';
        }
    }
    echo 
    '</dl></div>'
    By the way, do you really need that <div /> around the <dl />? If you apply the ID and the style to the <dl />, you might not even need the <div /> to be there.
    Last edited by Jake Arkinstall; Apr 16, 2009 at 10:51. Reason: Data was already sorted by the query - no need to use ksort()
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  14. #14
    SitePoint Wizard
    Join Date
    Mar 2008
    Location
    United Kingdom
    Posts
    1,285
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    Thanks again.

    Think we're 98&#37; of the way there.

    It seems to miss the '</ul></dd>'

    bit after the last <li> for the month.

    update

    Cool, just fixed it: Moved the echo '</ul></dd>'; to after the first curly bracket after foreach.


    Thanks for all your help with this arkinstall and everyone else too, brilliant stuff!

  15. #15
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Fixed above.

    Again, bracket placement.

    Off Topic:

    Joke: what you get when you have a lack of both sleep and coffee!
    Answer: Me and my terrible bracket placements!!!!


    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


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
  •