Address List with my modifications

Posted: Thu Apr 27, 2006 9:53 am

Thank you Raven for your most brilliant admin add-on, Address List. I don't know about others, but for me having access to user information like ID and email is an invaluable tool.

Previously I had a script that outputted ID, username, realname, email, regdate and last visit. It wasn't sortable and only useful when using the "find on this page" function in IE.

So, I combined my script and Raven's script and now have a sortable list which includes regdate and lastvisit. It works great and this is what it looks like:

Code:

<?php


if (!eregi("modules.php", $_SERVER['PHP_SELF'])) {


    die ("You can't access this file directly...");


}





/**********************************/


/*  Module Configuration          */


/* (right side on)                */


/* Remove the following line      */


/* will remove the right side     */


/**********************************/


//define('INDEX_FILE', true);





require_once("mainfile.php");


$module_name = basename(dirname(__FILE__));





include("header.php");





switch ($order) {


        case "nickname":


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY username, name, user_email, user_id, user_regdate, user_lastvisit");


            break;


        case "name":


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY name, username, user_email, user_id, user_regdate, user_lastvisit");


            break;


        case "user_email":


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY user_email, username, name, user_id, user_regdate, user_lastvisit");


            break;


        case "user_regdate":


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY user_regdate, username, name, user_id, user_email, user_lastvisit");


            break;


        case "user_lastvisit":


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY user_lastvisit, username, name, user_id, user_email, user_regdate");


            break;


        default:


            $result = mysql_query("SELECT * FROM ".$user_prefix."_users ORDER BY user_id, name, username, user_email, user_regdate, user_lastvisit");


                   break;


}





title("Status List");


$color1 = "#F0F8FF";


$color2 = "";


$row_count = 0;





OpenTable();


        echo "<div align=\"center\">\n";


        echo "<table border=\"0\" cellpadding=\"4\" cellspacing=\"0\">\n";


        echo "<tr><td align=\"left\"><a href=\"modules.php?name=$module_name&amp;order=user_id\"><b>#</b></a></td><td align=\"left\" ><a href=\"modules.php?name=$module_name&amp;order=nickname\"><b>Nickname</b></a></td><td align=\"left\" ><a href=\"modules.php?name=$module_name&amp;order=name\"><b>Name</b></a></td><td align=\"left\" ><a href=\"modules.php?name=$module_name&amp;order=user_email\"><b>Email Address</b></a></td><td align=\"left\"><a href=\"modules.php?name=$module_name&amp;order=user_regdate\"><b>RegDate</b></a></td><td align=\"left\"><a href=\"modules.php?name=$module_name&amp;order=user_lastvisit\"><b>LastVisit</b></a></td></tr>\n";


        $i=0;


        while($details = mysql_fetch_array($result)) {


        $last_visit_str = ($details["user_lastvisit"] ==0 ? "Never did login" : date ("y.m.d h:i:s A", $details["user_lastvisit"]));


                $i++;


                $row_color = ($row_count % 2) ? $color1 : $color2;


                echo "<tr><td bgcolor=\"$row_color\" align=\"left\">$details[user_id]</td><td bgcolor=\"$row_color\" align=\"left\">$details[username]</td><td bgcolor=\"$row_color\" align=\"left\">$details[name]</td><td bgcolor=\"$row_color\" align=\"left\"><a href=\"mailto:$details[user_email]\">$details[user_email]</a></td><td bgcolor=\"$row_color\" align=\"left\">$details[user_regdate]</td><td bgcolor=\"$row_color\" align=\"left\">$last_visit_str</td></tr>\n";


                $row_count++;


        }


        echo "</table>\n";


//        echo "</form>\n";


        echo "</div>\n";


CloseTable();


include("footer.php");


?>

The only issue I have is with sorting the regdate ... because the regdate is a text string, ie: Apr 27, 2006, sorting works by listing all the Apr regdates, then all the Aug dates, etc..
for example:
Apr 27, 2003
Apr 28, 2004
Apr 29, 2005
Aug 1, 2003
Aug 1, 2004
Aug 1, 2005

Obviously the column is being sorted according to the string and not the actual regdate.
So how do I fix this? Would the strtotime() be useful? And if so, how is it done?

Thanks for any and all clues and have a happy nuking day.

registered · Posted: Thu Apr 27, 2006 3:15 pm

Hmm you may be able to get MySQL to do the work for you
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
Something like

Code:




SELECT Date(user_reg) as user_reg2 ORDER BY user_reg2;

Or possibly using the UNIX_TIMESTAMP() function