Dreamweaver Club Forums

Dreamweaver Club Forums (http://www.dreamweaverclub.com/forum//index.php)
-   PHP (http://www.dreamweaverclub.com/forum//forumdisplay.php?f=188)
-   -   Select all events from Database and display separated by months (http://www.dreamweaverclub.com/forum//showthread.php?t=45820)

tux 07-10-2013 12:09 AM

Select all events from Database and display separated by months
 
1 Attachment(s)
Hi All, need a bit of help with this.

I want to display a list of events from my database but separate them into sections for each month. ( see attached screen shot )

What would my logic be to accomplish this. I know how to get this result for the list from the DB but not sure how to separate the months in the same query, or would I have to query for each month? Or can I use a for each loop somehow.

My table currently has eventID, title, eventDate (in this format yyyy-mm-dd), timestamp

Hope this makes sense. Cheers, Paul

edbr 07-11-2013 03:31 AM

i was waiting to see if any one came up with a soltion as i have always use separate elect for each category but feel there must be a better way but havent managed to work one out.
this woks with if conditions or better yet switch so maybe you can go from here.
Code:

<!DOCTYPE HTML>


<?php
/*** mysql hostname ***/
$hostname = 'localhost';

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

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

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=date", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';

    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM event Order by eventdate desc";
 
}
catch(PDOException $e)
    {
    echo $e->getMessage();
    }


  foreach ($dbh->query($sql) as $row)
        {

if( $row['eventdate'] == "January" ) {
echo $row['title']."<br>"; 
}

 if( $row['eventdate'] == "February" ) {
echo $row['title']."<br>"; 
}

                }
?>


tux 07-11-2013 10:21 AM

Hi Edbr,

Yeah, I have been trying to figure out a way too. Your idea is good, I will give it a try. I have been trying to utilise the array_combine function and having just 2 DB calls, 1 to get the month and year and one to get the events. Then array_combine will make the month and year array the key in a new array to the event results.

I need a way to remove all the duplicate months though in the first array as there is always several entries for each month. Then I also, as I am still a nit rusty, need to make an array of each mysql result. Cant remember how?

Cheers mate, Paul

edbr 07-12-2013 01:22 AM

im really busy lately but i do want to return to this. I use similar in the menu on my villa site but i cheated and used a condition for each neaer ie area then a different select to return all villas in that area. it works of course so i have left it at if it aint broke....... its just messy code and annoys me if i think about it too much

tux 07-13-2013 09:51 PM

Okay I have managed to come up with a solution for this. It gives me the result I need, whether or not it is the best solution I don't know. If anyone has an improved way do please let me know.

PHP Code:

<?php

require('connections/connection.php');//your own database connection script here///////////////////////////////////

/////Select statement to get the months and years for the events///////////////////////////////////////////////////
$m_query             =    "SELECT DATE_FORMAT(from_unixtime(eventTimestamp),'%M %Y') AS eventMY FROM events WHERE eventTimestamp >= UNIX_TIMESTAMP() ORDER BY eventTimestamp ASC ";
$m_result            =    mysql_query($m_query) or die('cannot get results!');//Month and Year from timestamp in DB//
while($monthyear    =    mysql_fetch_assoc($m_result)){
$rows[]                =    $monthyear['eventMY'];//Declare Array//////////////////////////////////////////////////////
}
$MY                    =    array_values(array_unique($rows));//Duplicate Months removed and array keys reset//////////

/////Select statement to get events////////////////////////////////////////////////////////////////////////////////
$e_query             =    "SELECT eventTitle, eventLink, DATE_FORMAT(from_unixtime(eventTimestamp),'%D') AS eventDay, DATE_FORMAT(from_unixtime(eventTimestamp),'%M %Y') AS eventMY FROM events WHERE eventTimestamp >= UNIX_TIMESTAMP() ORDER BY eventTimestamp ASC ";
$e_result            =    mysql_query($e_query) or die('cannot get results!');
while(
$events        =    mysql_fetch_assoc($e_result)){
$event[]                =    $events;
}

/////Display Month and Year with events for each Month below///////////////////////////////////////////////////////
foreach($MY as $my){
echo 
"<strong>".$my."</strong><br>";
foreach(
$event as $e){
if(
$e['eventMY'] === $my){
echo 
$e['eventDay'].", ".$e['eventTitle'].".<br>";
}
}
}
?>

And an example of the output.....

Quote:

October 2013
1st, Lecture: Nepal with Stuart Bennet.
8th, Titlesizer Training and Competition Clarification.
15th, 1st League Print Competition, Judge, Martin Wedge.
22nd, Screen Calibration and Printer Profiles / Practical 1 Print Mounting.
29th, 1st League DIGI Competition incorp. Gordon Richards with Judge, Brian Cope.
November 2013
5th, Lecture: Update on the White Lions of Timbuvarti with Karen Dudley.
12th, 2nd League Print Competition, Judge, Gary Early.
19th, Practical 2, TBC / DVD Presentation.
26th, 2nd League DIGI Competition incorp. Peter Peel (mono).
I hope this is useful to others.

jmichae3 07-22-2013 03:32 AM

edbr, I have a question about PDO - I could probably just as well look this up, but in many exampes of MYSQLI I saw mysql_open() or die("OOPS!" . mysql_error()); type of code. can this same thing be applied to

my uinderstanding of variable scope is that vars created in curly braces only exist in those curly braces, which is why vars created inside functions are local - it's the curly braces. at least in C/C++. I thought that was true in php and js also.

in PHP, is it possible to apply the first thing I mentioned to PDO::mysql so you don't have to put it in try{}catch(){}?

jmichae3 07-22-2013 04:07 AM

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) FROM abc_events ORDER BY somedtcolumn,id

edbr 07-22-2013 06:24 AM

Quote:


in PHP, is it possible to apply the first thing I mentioned to PDO::mysql so you don't have to put it in try{}catch(){}?
yes you can use without try and exeption, i just pasted my bog standard testing commection

jmichae3 07-22-2013 06:43 AM

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.

jmichae3 07-22-2013 07:05 AM

I updated the code several times. I abstracted the list-style-type styling into the CSS. it can be different in the 2nd-level list if you use # li > ul


All times are GMT. The time now is 01:42 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com