Birthday list extension not looping through database

[ernestmcd]

I'm trying to repair a birthday list extension extension originally written by Yanick Witchi of Switzerland. I had downloaded the extension for a project where a client wished to show a list of member birthdays on their website.

Here's what I am having problems with:

1. The extension has three main files that drive the code:

ModuleListBirthdays.php
templates/lb_default.xhtml
templates/mod_yw_listbirthdays.xhtml

On an example website, I have the extension loaded with a sample database that has about 900 member records in it. I can get the extension to show only one birthday on the date of the birtday. Aparrently the select statement in the ModuleListBirthdays.php file does not loop through the records properly.

Example is at

http://74.53.25.34/~barbuild/home-page.html

and it shows one birthday on May 5 when it should be showing 2 birthdays.

Yanick's code does have a nice feature that calculates the person age on their birthday from the Unix timestamp.

2. My sister who is pretty good with PHP/MySQL (better than me anyway) has helped me at least identify a couple of issues I would like to see if anyone help with.

3. The extension code in the ModuleListBirtdays.php and the template lb_default.xhtml doesn't loop properly. We do know that the array created by the php file displays the last record meeting the select criteria and it appears to loop, but we believe that is is overwriting the first record added to the array and not adding a subsequent record to the next row. Consistently the one record displayed has a higher number for the Contao member record ID than the previous record(s) being selected.

4. The developer provided a sample front end template lb_default.xhtml but I quickly figured out that the sample did not work until I modified it. The original code simply blew up and showed a white screen of death.

5. I would like ulitmately to be able to show all the birthdays in a given month, but not sure this is possible with this code.

If anybody can help, I would appreciate it if they could look at this code and see if they can find out why this extension doesn't loop through the records and only displays one member record.

I've attached the three files and will be happy to respond to questions if anyone has a suggestion on how to fix this.

Ernest McDermon
Snellville, GA

[ga.n]

Did you try to execute the query directyl (i.e. through mysql shell or tools like phpmyadmin)?

SELECT
*, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),FROM_UNIXTIME(dateOfBirth))))) AS lb_age													
FROM
tl_member
WHERE
lb_showBirthday=1
AND
MONTH(FROM_UNIXTIME(dateOfBirth)) = MONTH(NOW())
AND DAY(FROM_UNIXTIME(dateOfBirth)) = DAY(NOW())

(did you set the showBirthday flag in the backend?)

once you are sure the query are showing the expected results, you can remove the last line to show all the birthday of the month.

if it still does not work it may be better to provide a zip with the content of the extension directory and a dump of the sample table, this will greatly facilitate anyone who wants to help

[ernestmcd]

Thanks for the suggestion, and here's where we are at:

1. The extension does the following things correctly:

The SELECT statement works properly and returns a data set in MySQL for the day assuming the tl_member table has records in it for the current date and the checkbox "Show my birthday" is checked:

SELECT *, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),FROM_UNIXTIME(dateOfBirth))))) AS lb_age	FROM tl_member WHERE lb_showBirthday=1 AND MONTH(FROM_UNIXTIME(dateOfBirth)) = MONTH(NOW()) AND DAY(FROM_UNIXTIME(dateOfBirth)) = DAY(NOW());

The SELECT statement with the call to the current month

SELECT *, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),FROM_UNIXTIME(dateOfBirth))))) AS lb_age	FROM tl_member WHERE lb_showBirthday=1 AND MONTH(FROM_UNIXTIME(dateOfBirth)) = MONTH(NOW());

returns a data set with all member birthdays for the current month.

2. The sample template (lb_default.tpl / lb_default.xhtml) does not work when ucommented. It throws a white screen. If I show this code for the lb_default.xhtml

<ul>
   <?php foreach($this->arrData as $birthday): ?>[*]
       		 
               <?php echo $birthday['firstname']; ?>
               <?php echo $birthday['lastname']; ?>
               <?php echo $birthday['age']; ?> 
        	
                      
  <?php endforeach; ?>[/list]



Modify the birthday template to your needs!</p>

it returns one record correctly displayed as "John Doe 40" (age appears to be calculated correctly).

3. The sample template to display the result set on the frontend of the website will only display 1 record from the data set, always the record with the highest id number. We believe that the template is overwriting the data set and completing on the last record when multiple records are present.

4. The original developer Yanick Witschi ("yw_") has elected to no longer maintain or update this code and has released the copyright to me if I wanted to copy/edit/update this code. If one of you can fix this, then I would assume that Yanick would extend the same courtesy to you if you wanted to publish this to the extension repository as a working extension. I think having a functioning extension for birthdays would be useful in a number of situations.

5. My objective is to be able to show:

A list of today's birthdays AND
A list of all the birthdays for the current month if possible with the birthdate above the name and age.

6. I've attached the original extension in a zip directory if anyone wants to give this a go.

Any suggestions or ideas are certainly welcome. We're still poking at this, and if we have any success I will update this thread.

Ernest McDermon
Snellville, GA

[ramjet]

Do you have the original module ? (unedited by you) It doesn't seem to be in the repository

[ernestmcd]

Hi Ramjet!

Yes, the original module is attached at the bottom of my previous post as a zip file. You can download it from there. The developer removed the extension from the repository at my request because it was not working at all and he will not maintain this extension going forward.

The main problem seems to be that the array has a complex design and doesn't loop at all on the template lb_default.xhtml used to display the results. Also this particular code has a nasty habit of throwing white screen of death errors at every opportunity.

I've been working on it this evening without success. :-( Any help you can offer would be appreciated!

Ernest McDermon

[ramjet]

that zip contains two modules, both with extra class and template files by the looks of it (revisions???).
I would need the original module

[ernestmcd]

that zip contains two modules, both with extra class and template files by the looks of it (revisions???).
I would need the original module

Oops, I included both the original and the working copy. Here's the original extension without .xhtml files that I created. I created the .xhtml files to run under 2.11.2.

[ramjet]

Try this
[attachment=0:3c3gorzt]yw_listbirthdaysLOOPS.zip[/attachment:3c3gorzt]

I think the reason your template file whitescreened was the EOL(end of line) was Macintosh .
I've changed all file encoding to Unix/Ansi, and fixed the ModuleListBirthdays class so it loops.
Try from there, with gans advice you can figure the rest, just ask if u get stuck.

[ernestmcd]

I think the reason your template file whitescreened was the EOL(end of line) was Macintosh .
I've changed all file encoding to Unix/Ansi, and fixed the ModuleListBirthdays class so it loops.
Try from there, with gans advice you can figure the rest, just ask if u get stuck.

Thanks for the quick turn on the module and the excellent work on the Macintosh character(??). I'm on a PC and I guess the original developer must have been working on a Mac.

Here is what is happening now using this code as the lb_default.xhtml template:

<ul> 
<?php foreach($this->arrData as $birthday): ?>
       [*]
              <?php echo $birthday['firstname']; ?>
              <?php echo $birthday['lastname']; ?>
              <?php echo $birthday['age']; ?>
         
       
 <?php endforeach; ?>[/list]


Modify the birthday template to your needs xhtml!</p>

If there is ONE birthday in the database for today, the record displays as



If there are TWO birthdays in the database for today, the screen goes to white and one record is displayed:



If there are NO records for today, the template appears correctly with the statement "There are no birthdays today."

Do you have any suggestions on why the template doesn't display the loop results properly if more than one record is being selected by the SQL statement?

Ernest McDermon
Snellville, GA

[ramjet]

Must be the select statement.
If the birth date is 1970 or later it will show, if not it won't. (try two test birthdates of 1970 plus to see).
My tests were two and four years old, and worked - but I just tried and pre-1970 they don't.
I think a different method of working out the selection/ages is needed.

If there are TWO birthdays in the database for today, the screen goes to white and one record is displayed:

Not sure why you're getting raw html rather than styled on the one record output though - viewing the page source may shed some light.
EDIT: I think you need to get your page menu sorted first - it has html errors (use a validator to see). This will affect everything else on the page.


0 as a timestamp represents Jan 1 1970. I'm guessing the select isn't processing negative timestamps (although I haven't checked how tl_member stores the doB field). I have no time this weekend, but thats a clue - I'll have anothr look when I can.

EDIT: The FROM_UNIXTIME() function in the mysql query doesn't support pre-1970 dates. You'll need to research another way of querying.

[ernestmcd]

Thanks, that response is helpful and explains a lot. I was starting to think that the extension had a problem with the negative tstamp dates. What was IBM thinking about when they started their epoch in 1970? Dumb! (Why doesn't Contao dump the unix tstamp and go with standard date formats in MySQL?)

I'm familiar with getting the code validated, but right now I suspect that the issues with the extension are not associated with the validation, the code is brain damaged like not being able to pick up a date prior to 1970.

I'll get with my sister to see about recoding the date calculations to fix that issue; I suspect that is going to be 90 percent of the problem here and the loop may fail on that issue alone if it can't pick up the date properly.

I'll let you know what we come up with on this.

Thanks again, appreciate the advice and assistance!

Ernest McDermon
Snellville, GA

[ramjet]

Try this...
[attachment=0:23zpecv1]yw_listbirthdaysQUERYALL.zip[/attachment:23zpecv1]

As far as I can tell, it is IMPOSSIBLE to do the query via MYSQL (as it cannot calculate using negative timestamps - which dateOfBirth is stored as).
So the only way I see is to query all members who are ticked (lb_showBirthday=1), and use PHP instead to compare day/month and calculate the return.
Same day and month as today = birthday.
Age = this year minus birth year.

This will have heaps more overhead...that may or may not matter. I've only tried on 2 members, test on your 900.

To do birthdays this calendar month, you would compare to just the month, not month and day as I have.....

[ramjet]

I just noticed too the templates are wrong.
The ul /ul needs to shift to the other template to wrap everything, and the a needs to be within the li (not the li within the a) to be valid.

Also I ordered the mysql query by firstname, this may not suit.

[ramjet]

Better...
[attachment=0:7ctnlgiy]yw_listbirthdaysBETTER.zip[/attachment:7ctnlgiy]
The way the class and templates work now - all you need to do is to arrange

$this->id
$this->username
$this->firstname
$this->lastname
$this->dateOfBirth (formated re date format string in Contao::Settings eg: 5/7/80)
$this->age
EDIT: added....
$this->birthday (eg: 4 May)

however you like in the lb_default template (which processes each li list item) ... and wrap whichever parts you want as the link text in the beginning/end wrappers for the profile link...

$this->profilelinkstart
$this->profilelinkend

(see the lb_default template - both username and age are inside the link wrappers, so become the link text if lb_showprofilelink is checked in the module)

[ernestmcd]

Hi Ramjet!

You ROCK! I believe that it is working now. The changes you posted seem to have solved the issue of the negative UNIX timestamp being skipped over in the loop and the changes to the template now work as one would expect. If I add a sample date to a member record either positive or negative integer the loop picks it up and displays it properly. on the test website. I will keep testing it, but right now I believe it is working properly.

If you are interested, since the code is changed significantly, you might want to post this as a new extension in the catalog so that others can use it. Yanick Witshi was the original developer but he has indicated that he will no longer maintain it and has released the copyright to me (and by inference you) if I wanted to use the code. Here's what he said in an email to me 4/20/2012:

If your sister can get the extension to work (which should certainly be possible somehow) you're free to reuse and copy the parts of my code you need and publish a new extension. If you need a German translation for it, I'll be happy to do that for you :-)

My sister has stopped her work on this since your solution works so well. You might contact Yanick directly as a courtesy to let him know about your revisions and interest in posting this as an extension under your name. I do think that this extension has value and could be used in a number of applications, I have some non-profit websites who might be interested in this for their member base.

If you do publish this extension, and look to update it, I think having a drop down template to have a front end memberlist (?) that could appear for all the birthdays in the current week or current month would be a good addition. If you need me to test this in support of you I will be happy to donate my time.

Again, thanks for the wonderful work on this.

Ernest McDermon
Snellville, GA

[ramjet]

Try this one.....
[attachment=0:12gvar63]yw_listbirthdaysMONTH.zip[/attachment:12gvar63]

Adds a switch in the module to ALSO show birthdays this month (or not).
You'll need to run install.php to update database, and set switch in module (default is off).

In lb_default template you can now also use $this->birthday to output eg: 5 May, and also compose seperate list item outputs for "Happy Birthday!" and "Birthdays this month". (see template - first part is day, second part is month)

Language outputs are now in spans ("no birthdays" dependant on whether months are listed or not) in mod_yw_listbirthdays template

Classes have changed - divs are daybirthdays,monthbirthdays,nobirthdays.

Couple of extra language files added in default.php and modules.php (not translated into German)

The thing I can't test, and am curious about, is output speed if you have your 900 members checked (Show my birthday).


I need to think about repository/maintaining module. I'll let you know soon (got your pm, i'll email you).
Let me know if this version does the job. (Birthdays this week complicates things too much).

[ernestmcd]

Thanks Ramjet!

Its late here and I'm getting ready to head to bed. I've already downloaded the new flavor and will test it early in the morning. I'm usually up very early in the morning to get my day started.

So far speed is fine on the extension, I don't see any real issues there...900 records is not all that big in the scheme of things at server speed to generate the list, and then its only 0 - 3 birthdays (give or take) for a given day, and probably 50 - 80 in a month's list. There are 900 records, but not everyone has the birthday "turned on" to display.

Stay tuned, more to follow tomorrow.

Ernest McDermon
Snellville, GA

[ramjet]

Cool, but it still has to test every record to see who is turned on, then test the dates of all turned on records, then process all month matches, then process all month match profilelinks, and call the default template for each month match......
Yanicks way was far more efficient, but because of how FROM_UNIXTIME() works, unfortunately impossible with timestamps.
I'm expecting its fine, but I can't test that myself.