SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping and sorting techniques

    Hiya everyone,

    I was loooking all over yesterday and I havent found any information on how to group news by day efficiently. When I was a newbie coder many moon ago, I cameup with a makeshift solution that looks like its not exactly efficient. I will paste the code so you see what I mean...

    What this does is groups news by day in descending order, only going 3 days back. Keep in mind this is really old code too, not sure what more efficient methods are out there. I have tried to use advanced SQL queries, but many of the suggestions I have tried have had shady results. Below the code is a table structure so you know what kind of data is in there.

    PHP Code:
    $newdate = "0";

    $d = "2";

    for ($i = 0; $i <= $d; $i++)
    {
        
        $disp = mysql_db_query("database", "SELECT post_date FROM mc_news WHERE TO_DAYS(FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(post_date,'%Y-%m-%d')) = '$i' LIMIT 1");
        $dispb = mysql_fetch_array($disp);
        do {
            if ($dispb == "") 
            { 
                $d++;
                break; 
            }
    ?>
    <div class="bar"><span class="dateline"><? echo strftime("%A, %B %d",$dispb["post_date"]); ?></span></div>
    <?
        $result 
    mysql_db_query("database""SELECT * FROM mc_news WHERE TO_DAYS(FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(post_date,'%Y-%m-%d')) = '$i' ORDER BY ID DESC");
        while(
    $row mysql_fetch_array($result))
        {
            
            
    $newsID $row["ID"];
            
    // $newsID = $newsID;
            
    $text str_replace("\t","&nbsp;",str_replace("\n","<br>",$row['body']));
    ?>
        <div class="headlines">
        <? echo $row["headline"]; ?>
        </div>
        <div class="smalltext">
        <? echo strftime("%A, %B %d @ %R %Z",$row["post_date"]); ?> [<a href="mailto:<? echo $row["user_email"]; ?>"><? echo $row["user_name"]; ?></a>] <a href="index.php?zone=main&page=comment.php&nid=<? echo $newsID?>"><? $counted mysql_db_query("mapcore""SELECT count(*) AS count FROM mc_comment WHERE news_id = '$newsID'"); $counter mysql_fetch_array($counted); echo $counter["count"]; ?> comments</a>
        </div>
        <div class="justtext">
        <? echo $text?>
        </div>

        <hr noshade color="#2F4A6E">             
    <?
        
    }
    ?>

    <br>
    <?
    } while (0);
    }
    CREATE TABLE mc_news (
    ID int(5) NOT NULL auto_increment,
    user_id int(5) NOT NULL default '0',
    post_date int(10) unsigned NOT NULL default '0',
    headline varchar(64) default NULL,
    body text,
    user_name varchar(128) NOT NULL default '',
    user_email varchar(128) NOT NULL default '',
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;

    Keep in mind that post_date is a unix timestamp (date/time in seconds) and not a mysql datetime function.

    Any resources someone could point me to would be helpful, thanks an advance!
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you have encountered the fundamental problem with not using a database's native date/time format - the inability to access any date/time functions within an SQL query.

    One reviewer of the chapters of the graphing book I wrote made a comment that he never used MySQL datestamps, becuase you had to format the date before inserting. However, the examples I showed were aggregating about 20,000 rows into 12 monthly values, which I did with aggregate functions in an SQL statement, whereas if the dates were in Unix timestamps, the only choice would have been to retrieve all 20,000 rows and aggregate in PHP. Not the most efficient solution I believe .
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its no problem to use UNIX_TIMESTAMP functions too, those are built into MySQL...
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  4. #4
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't use MySQL that much, but you should be able to code some kind of a days(now() - postdate) as an integer. Then just iterate over the result set as an array and do whatever your grouping code is every time the days old number changes.

  5. #5
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MySQL documentation still is'nt that great yet and trying to figure out the group by stuff for my situation is a little tricky.
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  6. #6
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was not under the impression you really wanted to "GROUP BY" in the SQL, rather that you wanted the result to be grouped in table cells or divs or whatnot. GROUP BY would be useful if you wanted to use aggregate functions like min, max, sum or avg, but not for text values like new headlines.

    Maybe I misinterpreted what you were looking for?

  7. #7
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to group the news by day, limiting it to say.. 3 days.

    Example:

    Wednesday July 09, 2003
    Some news here...
    ---------
    Some more news...

    Tuesday July 08, 2003
    Some news for tuesday...
    ----------
    Even more news...

    Monday July 07, 2003
    News for monday...
    ----------
    Even more news for monday...

    Thats how I want it organized. Sites like bluesnews have done this technique and its quite practical considering the volume of news my site will do, organizing it by day would be the best solution.
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  8. #8
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then something along the lines of my Post #4 above should work, no SQL GROUP BY required for what you are trying to do.

  9. #9
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you could do something like:
    PHP Code:
    $lastdate '';
    $result mysql_db_query("database""SELECT * FROM mc_news WHERE TO_DAYS(FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(post_date,'%Y-%m-%d')) <= 2 ORDER BY ID DESC" );
    while(
    $row mysql_fetch_array($result)) { 
      if (
    $lastdate != strftime("%A, %B %d",$row["post_date"])) {
        
    $lastdate strftime("%A, %B %d",$row["post_date"]);
        echo 
    "News for $lastdate";
      }
      echo 
    $row["headline"];


  10. #10
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks sweatje
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer


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
  •