My Changes Action


This is the development page for the my changes action.
 


The php code:
<?php

// actions/mychanges.php
// written by Carlo Zottmann
// http://wakkawikki.com/CarloZottmann

if ($user = $this->GetUser())
{
    $my_edits_count = 0;

    if ($_REQUEST["alphabetically"] == 1)
    {
        print("<strong>This is a list of pages you've edited, along with the time of your last change (<a href=\"".$this->href("", $tag)."\">order by date</a>).</strong><br /><br />\n"); 

        if ($pages = $this->LoadAll("SELECT tag, time FROM ".$this->config["table_prefix"]."pages WHERE user = '".mysql_real_escape_string($this->GetUserName())."' ORDER BY tag ASC, time DESC"))
        {
            foreach ($pages as $page)
            {
                if ($last_tag != $page["tag"]) {
                    $last_tag = $page["tag"];
                    $firstChar = strtoupper($page["tag"][0]);
                    if (!preg_match("/[A-Z,a-z]/", $firstChar)) {
                        $firstChar = "#";
                    }
       
                    if ($firstChar != $curChar) {
                        if ($curChar) print("<br />\n");
                        print("<strong>$firstChar</strong><br />\n");
                        $curChar = $firstChar;
                    }
   
                    // print entry
                    print("&nbsp;&nbsp;&nbsp;(".$page["time"].") (".$this->Link($page["tag"], "revisions", "history", 0).") ".$this->Link($page["tag"], "", "", 0)."<br />\n");
   
                    $my_edits_count++;
                }
            }
           
            if ($my_edits_count == 0)
            {
                print("<em>You have not edited any pages yet.</em>");
            }
        }
        else
        {
            print("<em>No pages found.</em>");
        }
    }
    else
    {
        print("<strong>This is a list of pages you've edited, ordered by the time of your last change (<a href=\"".$this->href("", $tag, "alphabetically=1")."\">order alphabetically</a>).</strong><br /><br />\n");  

        if ($pages = $this->LoadAll("SELECT tag, time FROM ".$this->config["table_prefix"]."pages WHERE user = '".mysql_real_escape_string($this->GetUserName())."' ORDER BY time ASC, tag ASC"))
        {
            foreach ($pages as $page)
            {
                $edited_pages[$page["tag"]] = $page["time"];
            }

            $edited_pages = array_reverse($edited_pages);

            foreach ($edited_pages as $page["tag"] => $page["time"])
            {
                // day header
                list($day, $time) = explode(" ", $page["time"]);
                if ($day != $curday)
                {
                    if ($curday) print("<br />\n");
                    print("<strong>$day:</strong><br />\n");
                    $curday = $day;
                }

                // print entry
                print("&nbsp;&nbsp;&nbsp;($time) (".$this->Link($page["tag"], "revisions", "history", 0).") ".$this->Link($page["tag"], "", "", 0)."<br />\n");

                $my_edits_count++;
            }
           
            if ($my_edits_count == 0)
            {
                print("<em>You have not edited any pages yet.</em>");
            }
        }
        else
        {
            print("<em>No pages found.</em>");
        }
    }
}
else
{
    print("<em>You're not logged in, thus the list of pages you've edited couldn't be retrieved.</em>");
}

?>



The above code doesn't sort the pages properly when sorting by time.
I have re-coded the action like this:

<?php

// actions/mychanges.php
// written by Carlo Zottmann
// http://wakkawikki.com/CarloZottmann
// re-coded by Timo Kissing
// http://wikka.jsnx.com/TimoK
// Thanks a lot to JavaWoman for the help!
// http://wikka.jsnx.com/JavaWoman

if(!function_exists("print_edit")) {
    function print_edit(&$last, &$current, &$tag, $time) {
    if ($current != $last) {
            if ($last) {
                print("<br />\n");
            }
            print("<strong>$current</strong><br />\n");
            $last = $current;
        }
        $rev_link = "<a href='".$baseurl.$tag."/revisions'>history</a>";
        $page_link ="<a href='".$baseurl.$tag."'>".$tag."</a>";
        print("&nbsp;&nbsp;(".$time.") (".$rev_link.") ".$page_link."<br />\n");
    }
}
if ($this->GetUser()) {
    $user = $this->GetUserName();
    global $baseurl;
    $baseurl = $this->config["base_url"];
    $str_q1 = "SELECT DATE_FORMAT(time,'%Y-%m-%d') as pdate, ";
    $str_q1.= "DATE_FORMAT(time,'%H:%i:%s') as ptime, ";
    $str_q1.= "tag FROM ". $this->config["table_prefix"];    
    $str_q1.= "pages WHERE user = '". mysql_real_escape_string($user);
   
    if ($_REQUEST["alphabetically"] == "1") {
        $orderbyalpha = TRUE;
    } elseif ($_REQUEST["alphabetically"] == "0") {
        $orderbyalpha = FALSE;
    } elseif (is_string($vars["sorting"])) {
        if ($vars["sorting"] == "alpha" || $vars["sorting"] == "alphabetically") {
            $orderbyalpha = TRUE;
        }
    } else {
        $orderbyalpha = FALSE;
    }
   
    if ($orderbyalpha) {
        $str_sorting = " along with the time of your last change ";
        $str_linktxt = "order by date";
        $str_linkpar = "alphabetically=0";
        $str_q2 = "' ORDER BY tag ASC, time DESC";
    } else {
        $str_sorting = " ordered by the time of your last change ";
        $str_linktxt = "order alphabetically";
        $str_linkpar = "alphabetically=1";
        $str_q2 = "' ORDER BY pdate DESC, tag ASC, ptime DESC";
    }
   
    print("<strong>This is a list of pages you've edited,");
    print($str_sorting."(<a href='".$this->href("", $tag, $str_linkpar));
    print("'>".$str_linktxt."</a>).</strong><br /><br />\n");
   
    if ($pages = $this->LoadAll($str_q1.$str_q2)) {
        if ($orderbyalpha) {
            foreach ($pages as $page) {
                if ($last_tag != $page["tag"]) {
                    $last_tag = $page["tag"];
                    $firstChar = strtoupper($page["tag"][0]);
                    if (!preg_match("/[A-Z]/", $firstChar)) {
                        $firstChar = "#";
                    }
                    print_edit($last,$firstChar,$page["tag"],$page["pdate"]." ".$page["ptime"]);
                }  
            }
        } else {
            foreach($pages as $page) {
                if ($last_tag != $page["tag"]) {
                    $last_tag = $page["tag"];
                    print_edit($curDay,$page["pdate"],$page["tag"],$page["ptime"]);
                }
            }
        }
    } else {
        print("<em>No pages found.</em>");
    }  
} else {
    print("<em>You're not logged in, thus the list of pages you've edited couldn't be retrieved.</em>");
}

?>


For a comparison of the both versions see TimosChanges. That page uses the above code, but statically assumes TimoK as user, no matter if you are logged in or not.

Thanks to JavaWoman who helped me a lot tonight with questions and ideas. Without you I couldn't have done this.

The above code seems stable, safe and fast (225 out of ~260 rows fetched in 0.07 seconds compared to 0.25 seconds with the old code), but it's 1:35AM as I am writing this, so I would be happy if some others could test it on their own wikkas and let me know if there are any problems.


CategoryUserContributions
Comments
Comment by JavaWoman
2005-04-05 09:23:27
The incorrect ordering is a long-standing (reported) bug. However, looking at your code and your sample page, your new version has a problem as well: now the pages are no longer sorted alphabetically *within* a date, making them hard to find if there are many edits.

I think the real problem is in the array manipulation in the original code - maybe I'm too tired but I just don't see why any sorting in PHP is even necessary when MySQL can do all the work with a proper select statement.
Comment by TimoK
2005-04-05 10:36:23
The problem is that we want to order by day, then by tag, but in mysql we only have a timestamp. I can't think of any solution (in MySQL(3)) to get this done. But I am not a SQL guru either, so there might still be a solution.
I played a bit with the php-code and came to a solution, see the above change. This is by no means optimized for performance, I will do that later.
Comment by JavaWoman
2005-04-05 20:45:21
OK, my laptop is broken so I don't actually have a Wikka database to play with at the moment, but MySQL has a very large library of built-in functions, including a rich set of date and time functions - see http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html .

So - untested - the SELECT should look something like this:
SELECT tag, DATE(time) as pdate, TIME(time) as ptime FROM pages WHERE 'user' = $user ORDER BY pdate DESC, tag ASC;

Then just loop through the result set printing a new date header only when pdate is different from the previous value - no array manipulations in PHP apart fom looping through it with a foreach.
Comment by TimoK
2005-04-06 00:26:28
DATE() is available as of MySQL 4.1.1.
TIME() is available as of MySQL 4.1.1.
Since many hosters (at least here in germany) are still using MySQL 3 this would mean breaking the compability with a lot of (potential) installations. Even I don't have MySQL4 running yet and I am my own root - but the stable packages for Fedora Core 2 still contain MySQL 3 and I did'nt bother to upgrade yet.
But: EXTRACT() was added in MySQL 3.23.0.
I am not at home, but I will look into it tomorrow evening.
Comment by JavaWoman
2005-04-06 18:14:53
Timo, you're absolutely right - I completely missed the version info with DATE() and TIME(), probably because I was so sure they would be there. But it looks like EXTRACT() can do the job nicely. I agree we should stay compatible with MySQL 3.23 as longs as it's still widely used.
Comment by DarTar
2006-06-11 08:42:47
Related ticket: http://wush.net/trac/wikka/ticket/50
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki