logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > PHP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 07-09-2013, 11:09 PM   #1
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default Select all events from Database and display separated by months

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
Attached Images
File Type: jpg events.jpg (33.5 KB, 6 views)
tux is offline   Reply With Quote
Old 07-11-2013, 02:31 AM   #2
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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>";  
}

		}
?>
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas

Last edited by edbr; 07-11-2013 at 03:24 AM..
edbr is offline   Reply With Quote
Old 07-11-2013, 09:21 AM   #3
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

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
tux is offline   Reply With Quote
Old 07-12-2013, 12:22 AM   #4
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 07-13-2013, 08:51 PM   #5
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

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.
tux is offline   Reply With Quote
Old 07-22-2013, 02:32 AM   #6
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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(){}?
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Old 07-22-2013, 03:07 AM   #7
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) FROM abc_events ORDER BY somedtcolumn,id
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Old 07-22-2013, 05:24 AM   #8
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
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
Old 07-22-2013, 06:05 AM   #10
jmichae3
 
Join Date: Dec 2010
Posts: 366
Default

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
__________________
------------
Jim Michaels
HTML Code:
improperly<strong>nested<em>elements</strong>cause</em>
browser confusion (I believe the term is 'tag soup')!
jmichae3 is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 07:38 AM.


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