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(" (".$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(" ($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>");
}
?>
// 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(" (".$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(" ($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(" (".$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>");
}
?>
// 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(" (".$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
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.
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.
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.
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.