View Single Post
Old 07-22-2013, 05:43 AM   #9
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

also, I was just thinking, unless someone is actively maintaining the database to crop off old stuff, you may want to put a limit on the events. if they are all future events, you can cut off all old events using this SQL:

it's easy enough to use explode() to convert a date and time into mysql format and back. only thing to take into account is 12-hour crossings. results in about 50-100 lines of code.

I would not use unix timestamps personally, I just use the standard SQL DATE or DATETIME datatype which is based on YYYY-MM-DD HH:MM:SS

Code:
DELETE FROM abc_events WHERE somedtcolumn < CURDATE()
at the top of your page to eliminate old events (eliminates chron jobs), and then it's as simple as

Code:
SELECT *,MONTH(somedtcolumn) AS mo,MONTHNAME(somedtcolumn) AS moname FROM abc_events ORDER BY somedtcolumn,id
PHP Code:
<style>
.moyr {
    color:d7cb51;/*gold*/
}
.daytime {
    color:#605953;/*medium gray*/
}
.eventtitle {
    color:#807F84;/*light gray*/
}
#evlist ul {
    /*having this on your 1st-level list (moyr) is OPTIONAL, don't like the style */
    /*color:#581921;*//*dark red*/
    /*list-style-type:square;*/
    /*cut out your list bullet image, save it to disk as png, and put url here */
    /*list-style-image:url(images/redfade.png);*/
    
    /* what's below is purely optional, if you want more density */
    padding-top:0px;
    padding-bottom:0px;
    margin-top:0px;
    margin-bottom:0px;
}
#evlist li > ul {
     /*style that applies to 2nd-level list (and everything under) */
    /*cut out your list bullet image, save it to disk as png, and put url here */
    color:#581921;/*dark red*/
    list-style-type:square;
    /*list-style-image:url(images/redfade.png);*/
}
</style>

<?php

function MysqlDATETIMEToEventTimeFormat($s) {
    
//time input is expected to be in MYSQL DATE or DATETIME datatype format, YYYY-MM-DD HH:MM:SS
    
$date_time_arr explode(' ',$s);
    
$date_arr explode('-'$date_time_arr[0]);
    
$year $date_arr[0];
    
$month $date_arr[1];
    
$day $date_arr[2];
    
$time_arr explode(":",$date_time_arr[1]);
    
$hour=intval(time_arr[0]);
    
$minute=intval(time_arr[1]);
    
$second=intval(time_arr[2]);
    
$timestr="";//set variable in this function's scope
    
$daystr=$day;
    
//the hour needs to be swizzled and an am/pm appended.
    
if (0==$hour) { //12:00am
        
$timestr"12:" zeropad(minute,2) . "am";
    } elseif (
$hour>=&& $hour<=11) { //1-11:00am
        
$timestrstrval($hour) . ":" zeropad(minute,2) . "am";
    } elseif (
12==$hour) {//12:00pm
        
$timestr"12:" zeropad(minute,2) . "pm";
    } elseif (
$hour>=13 && $hour<=23) {//1-11:00pm
        
$timestrstrval($hour-12) . ":" zeropad(minute,2) . "am";
    }
    
$eventdatetimestr $daystr " | " $timestr;
    return 
$eventdatetimestr;
}

function 
MysqlDATETIMEToMonth($s) {
    
//time input is expected to be in MYSQL DATE or DATETIME datatype format, YYYY-MM-DD HH:MM:SS
    
$date_time_arr explode(' ',$s);
    
$date_arr explode('-'$date_time_arr[0]);
    
$year $date_arr[0];
    
$month $date_arr[1];
    
$day $date_arr[2];
    return 
intval($month);
}

function 
MysqlDATETIMEToEventMonthYear($s) {
    
$TheMonths = array("","January","February","March","April","May","June","July","August","September","October","November","December");
    
//time input is expected to be in MYSQL DATE or DATETIME datatype format, YYYY-MM-DD HH:MM:SS
    
$date_time_arr explode(' ',$s);
    
$date_arr explode('-'$date_time_arr[0]);
    
$year $date_arr[0];
    
$month $date_arr[1];
    
$day $date_arr[2];
    
$eventmonthyearstr $TheMonths[intval($month)] . $year;
    return 
$eventmonthyearstr;
}

function 
zeropad($n$numdigits) {
    
$nstr strval($n);//string version of number n
    
$totnumdigits max($numdigits,$nstr);
    
$newstr str_repeat("0",$totnumdigits strlen($nstr)) . $nstr;
    
//echo "$newstr\n";
    
return $newstr;
}

function 
handle_PDO_error($dbh) {
    
//when doing production, you can just as easily comment this out for security reasons...
    
echo "<p style='color:red'>PDO error:</p><pre>";
    
print_r($dbh->errorInfo());
    echo 
"</pre>";
}

/*** mysql hostname ***/
$hostname 'localhost';

/*** mysql username ***/
$username 'test';

/*** mysql password ***/
$password 'edbypass';

$dbh=0;//set $dbh in global scope
$sql_delete_old_events "
DELETE FROM 
    abc_events 
WHERE 
    start_date_time < CURDATE()
"
;
$sql "
SELECT 
    start_date_time,
    event_title,
    MONTH(start_date_time) AS mo,
    YEAR(start_date_time) AS yr FROM abc_events 
ORDER BY 
    start_date_time,
    id
"
;
//note that the word events is a keyword I think. can't use that for a table name.

//try {
    
$dbh = new PDO("mysql:host=$hostname;dbname=date"$username$password) or die(handle_PDO_error($dbh));
    
/*** echo a message saying we have connected ***/
    /*echo 'Connected to database<br>';
 
}
catch(PDOException $e) {
    echo $e->getMessage();
}*/

$dbh->query($sql_delete_old_events) or die(handle_PDO_error($dbh)); //eliminate old events before displaying new ones

$old_moyr="";
$flagHaveRows=false;
foreach (
$dbh->query($sql) as $row) {
    if (
$old_moyr != $row['mo'] . $row['year']) {//new month?
        
if (""==$old_moyr) {
            
//this block handles the start of the 1st-level list
            
            
echo "<ul id=\"evlist\" style=\"list-style-type:none\">";
            
//make a 2nd-level list now, 
            //no close li tag on purpose, will close after end of list so we can insert a nested ul list
            
echo "<li class=\"eventmoyr\">" MysqlDATETIMEToEventMonthYear($s); 
            echo 
"    <ul style=\"list-style-type:none;\">\n";//no bullet (you may choose to have one)
        
} else {
            
//this block handles all instances of beginning of 1st level lists
            
            //close 2nd-level list that is currently open
            
echo "    </ul>\n"//close the 2nd-level list
            
echo "</li>\n"//close list tag that contains the 2nd-level list
            
echo "<li style=\"list-style-type:none;\">" $row['event_title'] . "</li>\n";
            echo 
"    <ul><li class=\"eventmoyr\">" MysqlDATETIMEToEventMonthYear($s) . "\n";
        }
    }
    
//this code is executed for every 2nd-level list item, they are all the same.
    
echo "    <li><style class=\"daytime\">" MysqlDATETIMEToEventTimeFormat($row['start_date_time']) . "</span> <span class=\"eventtitle\">$row['event_title'] . "</span></li>\n";
    
    
$flagHaveRows=true;
    //update 
$old_moyr last so it holds old information next time around we can compare with
    
$old_moyr=$row['mo'] . $row['year'];
}
//we can't detect last row of foreach or while, so we have to do this at end, this will always be the case for doubly nested list where we are usually doing 2nd level.
//but we CAN detect if there were any rows by assigning a simple flag. 
//if it's set within the loop, we have rows and therefore a list to close.
if (!
$flagHaveRows) {
    echo "    
</ul>\n";//close 2nd level list
    echo "
</li>\n";//close 1st-lavel list that contained the 2nd-lebel list
    echo "
</ul>\n";//close the 1st-level list
}

?>
you should know that MYSQL interface is deprecated (means it is going away very quickly). code for PDO, it is much easier than MYSQLI.


I am not used to PDO, but I am going to have to. I miss mysql_num_rows(). I suppose I could simulate by querying the database again, but only with a loop. but this will work.
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!

Last edited by jmichae3; 07-22-2013 at 06:05 AM.. Reason: added red color and changed circle to square
jmichae3 is offline   Reply With Quote