Print Page - Events Output - Handle "empty" Dates

Title: Events Output - Handle "empty" Dates
Post by: fedoracore on February 18, 2007, 09:24:51 PM

hi there. hey, VGR-- your feedback on the "INTERVAL" SQL, among other stuff-- really helped me out. actually, the last i was involved in "building" any app stuff, i had finally begun to see the true power which lies in the database end of the apps. i don't know if most beginners are like this or not, but i know i always have been very focused on the PHP side of things. it's times like this when i wonder how much work i've put on myself when so much could have been done "automatically". it's really quite amazing!

anyway-- so check out what i came up with after following some stuff i saw in the new MySQL CookBook (http://tinyurl.com/2wqxjp), and then just playing around w/ the command line (as per your suggestion, and from the positive experience i had last time i tried it!).. actually, been using the MySQL Query Browser (http://www.mysql.com/products/tools/query-browser/), but it's quit the same idea-- plus it has an excellent reference integrated which is how i learned to do the DATE_FORMAT-ing

here's my SQL, heavily inspired by your own, obviously. ;)

Code:

$this->allque = "SELECT DATE_FORMAT(calendar, '%a %b %D %y') AS 'dayof', room, event, detail FROM $tableall WHERE calendar >=now() AND calendar < now() + INTERVAL 14 DAY ORDER BY calendar ASC;";
//ORDER by $field $order

here is the output i've got. though i've got room for, and want to be able to populate "two weeks" worth of data, there will not always be events during that period. the output from my "test" data is as follows:

Code:

Array
(
[0] => Array
(
[dayof] => Mon Feb 19th 07
[room] => times_lounge
[event] => Sunday Ends All
[detail] => Sundays truly suck-- but NOT AT THE LOUNGE, Baby!!
)

[1] => Array
(
[dayof] => Tue Feb 20th 07
[room] => times_livemusic
[event] => Tuesday's Gone
[detail] => Tuesday's Gone with the wind
)

[2] => Array
(
[dayof] => Sun Feb 25th 07
[room] => times_wholeclub
[event] => Bob's Birthday Party
[detail] => Some Details about Bob- he is such a cool dude.
)

[3] => Array
(
[dayof] => Wed Feb 28th 07
[room] => times_dancers
[event] => Pretty Girls Dancing
[detail] => The Famous Club ZZZ "Zap" Girls perform, debuting the Spring 2007 newcommers!
)

[4] =>
)

i'm thinking of several different ways i might handle this data so that it is output into the appropriate "cells" of the table.
the "hard" way (i think) would be to work some kind of switch statement to determine if there is an event on, for example, the first monday, right? and i could do that for each of the 14 dates of the two weeks-- if empty, no output, but wouldn't there be a way to do it based on some sort of for-each loop and (or even to use a modulous to print the table-- i really am stretching it there-- but i'm pretty sure i've seen modulous used to print tables, depending upon data used, and parameters, etc.)

i dunno-- i figure you've probably handled this particular problem a number of ways. i don't mean to exploit your generosity-- but i DID want to show you at least my success w/ what you gave me. i appreciate any advice you might lend from here on out.

thanks so much!!!
-jeff

EDIT:
i may be wrong, but i think the [php] tags are NOT word-wrapping. i finally gave up and used "code" instead. ;)

EDIT2:
the thread [ elsewhere (http://www.phpbuilder.com/board/showthread.php?s=&postid=10593202#post10593202) ] where i originally got the idea about the modulous and the html tables (http://www.phpbuilder.com/board/showthread.php?s=&postid=10593202#post10593202), etc. i haven't examined it, but i wanted to come back and share the link before i forgot. :)

Title: Re: Events Output - Handle "empty" Dates
Post by: VGR on February 19, 2007, 01:50:42 AM

ok, nice.

Firstly, you could at no cost select also the '%a' date format (search, there is a numerical vlue intraweek day somewhere) so that you could "know" that thsi day is Monday without having to parse the substr(0,3) of 'theday' for 'Mon' - performance and easiness

Then, accessing the datz is really depending on the order of your matrix/2D-array for the lines...

Except in FORTRAN, you read data line per line in a given vector, this means using stupid mysql_fetch_row(), as name implies, you already have he data for the first line to output (you've the columns available). A simple test on isset() on the row's value of the day as numerical index compared to "1" (if that means "first Monday" for you) is enough to "generate" empty DHTML rows, then the extracted data in the correct row, then loop DB read.
Cool and easy.

***if*** you intend to output the event's data in column (or an other fancy way), ***then*** you need to extract ALL the DB data to memory and "parse" (I don't remember the English for "parcourir") them for output - same logic as above, roughly -

here's an example, given you output in columns and know you've two weeks to show, from Monday to Sunday two times.
Let's assume Monday=1 and Sunday=7 (as is usual if Bible-based)
algorithm :
- read all DB data for X events (X<=14) in the period via the above query using INTERVAL 14 DAY - note that if you issue the query on a day<>'Monday', you will NOT have the past events in the current week, and will DO have the future events past the second week's Sunday - both are not a problem
- those data are stored in $events[$day_numeric] where day_numeric is 1..14 (sparse array, ie some indices can be unset)
- today is $today = index in the first week (ie, 3 if Wednesday) - you can get it by the same SELECT using date_format(now()) or in PHP using date('%someformat%')
- let's go :
- $dataindex=1; // this index "points to" the first $events row - this could be zero-based, depending on the way you fill in $events, cautiously or not ;-)
- for ($i=1;$i<$today;$i++) { // those are the "past" events we aren't interested in showing
- issue empty events row
//VGR REM if ever you manage to "align" your DB read on first week's Monday, then you will have the "past events in current weeks" also ; this in here do : if (isset($events[$dataindex])) unset($events[$dataindex]); // delete them from the set
}
// we get out with : $i=$today and $events[]=set of only the events to show (still sparse array)
// we don't have to care for count($events) being reached
foreach($events as $index=>$element) { // for all events left
if ($index<=14) { // remember we could have "too future events" - strict test "<15" is more efficient but less understandable
$lastindex=$index;
for ($i=$today;$i<$index;$i++) issue empty line - skip days
// issue today's event : $element ( = $events[$index])
$today=$index; // loop
} // else NOP
} // foreach event
// do a last loop from the $lastindex+1 to 14 for issuing remaining empty lines
// done - kind of ;-)

Title: Re: Events Output - Handle "empty" Dates
Post by: fedoracore on February 19, 2007, 04:50:26 PM

"parcourir" = "traverse", according to Google.com/translate

:)

WOW! again, you've given me a half-semester's 400-level college course here, i think. :wink: -- which is a good thing, of course. ... okay, maybe 200 level, but who's counting. :)

anyway-- tell you what, VGR, since you and i seem to have established a rapport for this challenge of mine, instead of going and trying to digest,etc., only because something tells me from for first comment that perhaps you didn't realize that i AM receiving from the DB the info that the date is a monday, or tues, etc (if that's what you were saying, i'm unsure) -- the DATE_FORMAT('datevalue','%a') (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format) tells me [dayof] => Tue -- maybe this is what you meant by

Quote from: VGR

at no cost select also the '%a' date format (search, there is a numerical vlue intraweek day somewhere) so that you could "know" that thsi day is Monday without having to parse

just wanted to make sure of that part. i haven't got to conqueroring much more of your feedback yet, but this is what i plan to try now. :)

i think i understand what you mean about the "DHTML" output. are you saying that for each row i "pull in" to the PHP, i can check, one-by-one for the criteria / parameters of whether i will show output, and somehow-- during this process, also print the table structure?
it defintely sounds "doable". i just have to modify some stuff-- and i think i can do that really easily. :)

thanks again.

Title: Re: Events Output - Handle "empty" Dates
Post by: VGR on February 20, 2007, 04:55:31 AM

I was more thinking of the %w format : Day of the week (0=Sunday..6=Saturday)

note that there is a DAYOFWEEK() function returning (1 = Sunday, 2 = Monday, …, 7 = Saturday) in stead (supposedly "ODBC standard" :D )

all are at http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html (http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html)

I was suggesting NOT to parse a string for 'Mon' or 'Tue' but directly use the integer as an index - it's faster. Anyway, if you use PHP, then associative arrays are everywhere and I'm not even sure a standard vector (scalar keys) is faster.

have fun.

Title: Re: Events Output - Handle "empty" Dates
Post by: fedoracore on February 20, 2007, 02:33:21 PM

awesome-- i was right about what you were saying, and you were right about my "not doing it" as you were thinking-- it's like i ended up doing what you were thinking by way of the mistake over the [ %a ], and in fact, i did use the
[ %w ] because, from your mention that it "wouldn't hurt" me to get it from the DB at the same time, i modified my SELECT statement to:

Code:

SELECT DATE_FORMAT(calendar, '%a %b %D %y') AS 'dayof', DATE_FORMAT(calendar, '%w') as 'weekday', room, event, detail FROM $tableall WHERE calendar >=now() AND calendar < now() + INTERVAL 14 DAY ORDER BY calendar ASC;

what i'm trying to do now, is the html part-- but i'm confused about the output i'm getting. i guess it's really a multi-dimensional array, right? or no? well-- here's what i have. it's not right, but maybe someone can tell me what i need to tweak in here to get the < td > 's to actually have the array data as shown in my first post of this thread. right now, i'm getting all of the "no event data" in all of my < td >'s. i'm not surprised at that, but it's the "undefined offset" which i can't think how to tackle.

$maindata = new calData();
$myArray = array();

for($i=0;$i<=$maindata->numrows;$i++) {
$twoWeeks[$i] = $maindata->selectPeriod($table,$field,$order,$i);
}

$tableArray = array();

foreach($twoWeeks as $eventKey => $eventDetails) {
$tableArray[$x]=$eventDetails[$eventKey];
}

$htmltable ="";
for($dys=1;$dys<=14;$dys++) {
if(empty($tableArray[$dys][$dys])) {
$htmltable .= '<td>no special event</td><br />';
} else {
$htmltable .= '<td>'.$tableArray[$dys]['event'].'</td><br />';
}

}// THE METHOD
function selectPeriod($tableall,$field,$order,$i) {

$this->allque = "
SELECT DATE_FORMAT(calendar, '%a %b %D %y') AS 'dayof', DATE_FORMAT(calendar, '%w') as 'weekday', room, event, detail FROM $tableall WHERE calendar >=now() AND calendar < now() + INTERVAL 14 DAY ORDER BY calendar ASC;";
//ORDER by $field $order
$this->allresult = mysql_query($this->allque) or die(mysql_error());
$this->numrows = mysql_num_rows($this->allresult);
$this->counter = array();
for($z=0;$z<=$this->numrows;$z++) {
$counter[$z]= mysql_fetch_array($this->allresult, MYSQL_ASSOC);
}

return $counter[$i];
} // END METHOD selectAll
EDIT:
waaaiiiitt a minute. i think i see what i might need to do (besides the fact that $array[$key][$key] isn't going to do me much good up there in my for-loop!... oops!)
i need to change that for-loop in the method to stop limiting the output to only those rows which have.... wait... no. i don't know. nevermind. i'm still confused! this is a mod of a different, nearly identical method (where i used 'SELECT * FROM' instead of the DATE_FORMAT query-- but i can't think precisely why i used that $counter[$i] instead of just using the regular "output". i know it was originally so i could return ALL of my data in One Multidimension array, for placement into-- i guess a drop-down list. (which i'm using elsewhere), but i can't think why i'd need it here-- and i't probably messing me up? hmm....

Title: Re: Events Output - Handle "empty" Dates
Post by: fedoracore on February 21, 2007, 11:16:17 AM

hey there, everyone...
thanks to VGR's generous, thoughtful help, and the insight he's given me, i've been able to actually make some progress on this project of mine. i knew i'd be able to do something with it-- but i think now, it's going to be much better.

VGR is helping me to go about the "Process of Application Creation" in a more efficient, productive, and ultimately, more stable manner. whether he realizes it or not ;)
[ testimonial - j.s. ]
:wink:

EDIT:
what i had here was just way too much to try to read-- even for me, and i know what's going on 'cause i've been at it all day!

let me try to K.I.S.S. on this on, best i can...

i think you need to see the current out put, which are two arrays.
the reason there are TWO arrays made from one db output of "two-weeks" work of data is that It is only Part of my GUESS at how to do this.
therefore, there are TWO arrays because one is a Filtered version of the previous-- showing only those of the 14 total iterations, only those which contained "data".

my challenge is to take the data from the Second, "filtered" array, and output it into < table > for viewing as if it were two weeks chopped from a calendar: 7 col's, 2 rows, (and a row for header, whatever-- irrelevant)

my challenge, additionaly-- force the output, taken from that "Second array" to print in NON-consecutive terms-- because there will be places where i must have gaps for the NON existing data.

i have succeeded on all levels except the final-- to print w/ the dates, landing on the proper day or the week, (ie.

= mon, [1] = tues, etc), and also allowing for example, if [3] = wed, but has no data, then i must have a "blank" cell.

i know i can do it "by hand", but it would be beautiful if i can find the right mix of array functions to do it. i've wrestled w/ array_multisort, array_filter, array_map, and the list goes on...

here's the output. i won't bother you with the code because i don't think it matters. if you know the array data, it should be enough to see what i need. if you want to see my code, i'm happy to put it here, but it's a mess. ;)

Code:

NOTE: i do not understand why my "sorted" array (array #2) is showing the empty vals at the end... so perhaps i have something not quite rigght there either)

thank you!!!

EDIT No.124.73:
okay-- i'm gettin closer!
actually-- i think i "have it", but the SELECT statement i'm using doesn't show "today" in the results.

so--the Problem. How to get "TODAY" to show from using the aformentioned Select statment? if i'm right, there should have been no "wednesdays" in the output above (no Feb 21). i'll have to verify that...
i'm using the select statement, but it only starts at Friday. it's "losing" today. (today being thursday as i type this)

i would pretty much have it, i think-- if i can get the database to do the "current" day on output-- or even the "previous" days of THAT week only (so there are 2 full weeks always displayed.) i realize that's a lot to ask, but i am curious.

EDIT: once more an edit-- but i wanted to show you what i believe to be my "solution", which is AMAZINGLY simple, IF it is going to work, which it appears it may. after struggling w/ this thing all day (literally, as it's what i do, fortunately have the ability to spend that kind of time), i finally recognized something which seemed to be consisten. look at the Keys of the Main array (twoWeeks). if any ANY time the main key (i.e. twoWeeks[key] ) is GREATER than that of the -twoWeeks['weekday']- you will see that this is part of the "second week", so i made this code, and it seems to be working:$maindata = new calData();
$myArray = array();

for($i=0;$i<=13;$i++) {
$twoWeeks[$i] = $maindata->selectPeriod($table,$field,$order,$i);
}
$weekOne = array();
$weekTwo = array();

foreach($twoWeeks as $daysKey => $eventDetail) {

// HERE IS THE MAGIC CODE WHICH SEEMS TO WORK FOR SEPARATING THE WEEKS:
if($daysKey > $eventDetail['weekday']) {
$weekTwo[$daysKey] = $eventDetail;
} else {
$weekOne[$daysKey] = $eventDetail;
}
}maybe it won't always work, but it looks as though it might. no?

Title: Re: Events Output - Handle "empty" Dates
Post by: VGR on February 22, 2007, 01:58:14 AM

ok , two fast remarks :

1) on the data. To debug them, do as you did, use print_r between two <pre> and </pre>

2) on your code :

Code:

$maindata = new calData(); //VGR REM what for ?
$myArray = array();
//VGR ADDed this call ONCE (see comment below on the was : line )
$hasLines=$maindata->selectPeriod($table,$field,$order); // REM remember a function returns something (for "void" use a procedure, not a function)
//EoAdd

for($i=0;$i<$maindata->numrows;$i++) { //VGR REM beware! you count from 0 to count(resultset) : one touch too much ! should be strict < here
//was : $twoWeeks[$i] = $maindata->selectPeriod($table,$field,$order,$i); //VGR REM you call the query as many times as you have rows in the resultset! BAAAAD...
$twoWeeks[$i] = $maindata->counter[$i];
}

$tableArray = array();
foreach($twoWeeks as $eventKey => $eventDetails) {
//VGR REM apply my algorith above : for the days in the current day to end of second week afterwards, check if line is set in $twoWeeks, then display event, else display empty line
$tableArray[$x]=$eventDetails[$eventKey];
}

$htmltable ="";
//VGR REM where do you issue the TABLE/TBODY ? and the TR /TR ?
for($dys=1;$dys<=14;$dys++) {
if(empty($tableArray[$dys][$dys])) {
$htmltable .= '<td>no special event</td><br />'; // same remark as below about <br>
} else {
$htmltable .= '<td>'.$tableArray[$dys]['event'].'</td><br />'; //VGR REM you CAN NOT issue <br> between /TD and TD, ie in the MIDDLE of a TABLE !!! In here, what you aim for is an hard end-of-line, ie for Windows "\r\n" so that the HTML source is arranged better. NOT a <br> which is PART of the HTML code itself !!!
}

}// THE METHOD

function selectPeriod($tableall,$field,$order) { //VGR REM modified parameters list
$this->allque = "SELECT DATE_FORMAT(calendar, '%a %b %D %y') AS 'dayof', DATE_FORMAT(calendar, '%w') as 'weekday', room, event, detail FROM $tableall WHERE calendar >=now() AND calendar < now() + INTERVAL 14 DAY ORDER BY calendar ASC;";
//ORDER by $field $order
$this->linkID = mysql_query($this->allque) or die(mysql_error()); //VGR REM renamed variable
$this->numrows = mysql_num_rows($this->allresult);
$this->counter = array();
for($z=0;$z<$this->numrows;$z++) { //VGR REM you could have done $counter[]=mysql_fetch_array(...) and AGAIN strict test
//VGR REM you should have $this->counter hereafter...
$this->counter[$z]= mysql_fetch_array($this->linkID, MYSQL_ASSOC); //VGR REM MYSQL_ASSOC is the default value anyway
} // for //VGR REM added comments on your blocks' end is always a good idea
return($this->numrows>0); //VGR REM now rdturning boolean meaning "are there any rows ?" (given you die() on a failed query ; personally I would have returned a boolean meaning "was the query ok?")
} // END METHOD selectPeriod //VGR REM fixed comment

Title: Re: Events Output - Handle "empty" Dates
Post by: VGR on February 22, 2007, 02:00:48 AM

I forgot to rename the mysql_num_rows($this->allresult) to $linkID ...

Title: Re: Events Output - Handle "empty" Dates
Post by: fedoracore on February 22, 2007, 11:19:46 PM

aside: OOPS! i nearly "removed the topic!" instead of hitting reply. ouch that would have been bad.

VGR, thank you very very very very much for your detailed explanation., and thorough examination of my code.

just quickly-- re: "where's my < table >, and why do i have the < br />?" -- because this is totally NOT something i would put into production-- i'm just "Sort-of" formatting it so i can see what happens-- so it looks just a little bit more like a table, than the < pre >, for example-- so that's all that means. ;)
-- much of that code is just "left-over" from where i might have output a "$key" or something, so i could see more of the inner workings of the array.

what do you mean by "function vs... procedure"? (i.e. i don't know term "procedure")

indeed, this illustrates my COMPLETE LACK OF any "formal training courses". much of this, i might have learned in a course -- as in "don't do query the DB again!!"... stuff like that-- i'd probably figure it out months, years later... but my concentration is so focused on "is it possible that i can make A + B = C?", that often-- my methods for making it happen, i never go back and re-examine them for efficiency-- but this, i think, would / could be learned -- in places like here for one thing-- w/ people like yourself who don't have an attitude problem-- often times-- respondants are so caught up in masturbating all over the thread , then rubbing your face in it-- it's difficult to get much learning that way-- even if there is some "good" code in there-- rarely do situations like that foster much "learning", if you ask me. someone such as yourself, on the other hand, is actively in pursuit of the learning process-- which again, if you ask me-- there isn't much point in anything else. i mean-- why have a discussion, if somewhere along the lines, someone isn't trying to "get to the root of the problem" -- whether your offering is the inquiry or the bequest.

anyway-- blah, blah...
i'm excited to have a better look at your rearrangement of my stuff here.

in all-- do you think i had a "fair" beginnings of a solution to the problem?
and what of my idea about the arrayKey vs the MySQL['weekday'] value? -- was i correct that it will work accurately each time?
(i can understand if you have no idea what i mean by that question just now... as i explain it poorly, but maybe you know what i'm asking-- i mentioned it above, briefly at the end... essentially-- my epiphony!)

;)

thanks again, man-- really!
:)

Title: Re: Events Output - Handle "empty" Dates
Post by: VGR on February 23, 2007, 01:21:01 AM

you best friend is the man who wrote "data structures + algorithms = programs". Read it, you can get it for less than 8 U$D on amazon ;-)
http://www.amazon.com/s?ie=UTF8&search-type=ss&index=books&field-author=Niklaus%20Wirth&page=1 (http://www.amazon.com/s?ie=UTF8&search-type=ss&index=books&field-author=Niklaus%20Wirth&page=1)

also search on your favourite wikipedia for "computer programming" and "pascal programming". You'll get the best basis for avoiding mistakes like executing a query N times when you need in fact N accesses to 1 set of data you can load at once ;-)

have fun

Experts Round Table Network

Databases => MySQL => Topic started by: fedoracore on February 18, 2007, 09:24:51 PM