Birthday list

[neljan]

Hello

Would someone mind telling me how I can change this code so that it displays the next 5 birthdays instead of showing just the forthcoming birthdays for the next 7 days please?

$ergebnis=safe_query("SELECT nickname, userID, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%y') + 1 AS age FROM ".PREFIX."user WHERE IF(DAYOFYEAR(NOW())<=358,((DAYOFYEAR(birthday)>DAYOFYEAR(NOW())) AND (DAYOFYEAR(birthday)<=DAYOFYEAR(DATE_ADD(NOW(), INTERVAL 7 DAY)))),(DAYOFYEAR(BIRTHDAY)>DAYOFYEAR(NOW()) OR DAYOFYEAR(birthday)<=DAYOFYEAR(DATE_ADD(NOW(), INTERVAL 7 DAY)))) AND birthday !='0000-00-00 00:00:00' ORDER BY `birthday` ASC");

Thanks very much.

[ixalmida]

You've over-thought your query. You need some simplicity:

"SELECT nickname,
  userID,
  DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%y') + 1 AS age
FROM ".PREFIX."user
WHERE  birthday > NOW()
ORDER BY birthday ASC
LIMIT 5"

[neljan]

Thanks for your reply ixalmida.

Since I posted this, I changed the code to this:

$ergebnis=safe_query("SELECT nickname, userID, birthday, DATE_FORMAT(FROM_DAYS(TO_DAYS(FROM_UNIXTIME(".(time()+(31*24*3600)).")) - TO_DAYS(birthday)), '%y') 'age' FROM ".PREFIX."user WHERE DAYOFYEAR(birthday)<=DAYOFYEAR(FROM_UNIXTIME(".(time()+(31*24*3600)).")) AND DAYOFYEAR(birthday)>DAYOFYEAR(NOW()) ORDER BY `birthday` ASC");

...which gives me upcoming birthdays for the next 31 days (still not what I want). I then replaced this code with yours, but I got 0 results.

Heres the complete code:

$whatever=safe_query("SELECT nickname, userID, birthday, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%y') + 1 AS age FROM ".PREFIX."user WHERE birthday > NOW() ORDER BY birthday ASC LIMIT 5");

$n=0;

while($db=mysql_fetch_array($whatever)) {

list($year, $month, $day) = explode('-', $db['birthday']);

    $n++;

    $years=$db['age'];

    if($n>1) $birthweek.= ''.$day.'.'.$month.' <a href="index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a>  ('.$years.')<br />';

    else $birthweek.= ''.$day.'.'.$month.' <a href"index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a> ('.$years.')<br />';

}

if(!$n) $birthweek="None";

eval ("\$sc_birthdays = \"".gettemplate("sc_birthdays")."\";");
echo $sc_birthdays;

Thanks again for your time.

[ixalmida]

Sorry, I just realized that "birthday" is probably a DATE field, instead of TIMEDATE. Try comparing against CURDATE() instead. Also, I think you may have over-thought the age portion as well, so I changed that. I also realized that "birthday" is probably really "birthdate", so I broke down that part.

Also (just in case), add the following die statement and let me know if you get any errors.

$query = "SELECT nickname,
  userID,
  YEAR(NOW()) - YEAR(birthday) AS age
FROM ".PREFIX."user
WHERE  MONTH(birthday) > MONTH(NOW()) AND DAY(birthday) > DAY(NOW())
ORDER BY DAYOFYEAR(birthday) ASC
LIMIT 5";
$whatever=safe_query($query)
  or die(mysql_error()."<br>".$query);

Note: I didn't design this to roll over to the next year (so if it is December 29th and there is one birthday left in the year, there would only be one result - until the next year rolls around, then all is reset). I'd have to put a little more thought into it if rolling over to the next year is important to you.

[neljan]

I get this result:

http://clanrv.worldclanleague.com/index.php?site=sc_birthdays

As you can see, only 1 result and Andriz's birthday isn't even the next birthday (lol).

If it helps, the birthdays look like this in the table:

DD-MM-YYYY

[johanafm]

Then it's most likely not a date or datetime, but a varchar. (standard) SQL dates are using ISO-whatever date format: YYYY-MM-DD. Use either date, datetime or unsigned int (for storing unix timestamp) rather than varchar.
And this will show what format the birthday field has, where tablename is PREFIX.'user'

SHOW CREATE TABLE tablename

[ixalmida]

Doh...

Well...if they are all of type varchar and the format consistent, it shouldn't be too hard to convert them to a format that you can actually do some math or comparison with.

Here's an example:

// Add the new date columns...
mysql_query("ALTER TABLE ".PREFIX."user ADD COLUMN sql_birthdate DATE, unix_birthdate INT UNSIGNED");

// Get current birthdays...
$result = mysql_query("SELECT username, birthday FROM ".PREFIX."user");
while($row = mysql_fetch_assoc($result))
  {
  $username = $row['username'];
  $strDate = $row['birthday'];

  // Get MM/DD/YYYY pieces...
  $arrDate = explode("-", $strDate);

  // Verify valid birthday...
  if(checkdate($arrDate[0], $arrDate[1], $arrDate[2]))
    {
    // Convert pieces to SQL & Unix dates...
    $sqlDate = $arrDate[2] . "-" . $arrDate[0] . "-" . $arrDate[1];
    $unixDate = mktime(0, 0, 0, $arrDate[0], $arrDate[1], $arrDate[2]);

// Update table with new date formats...
$query = "UPDATE ".PREFIX."user 
     SET sql_birthdate = '$sqlDate', unix_birthdate = $unixDate
   WHERE username = '$username'";
mysql_query($query);
}
  }

As usual, when I post an example, I intend it as a template - not to be followed to the letter. Pick the date format that works best for you. If you are going to be doing heavy date math, pick Unixtime. If you are just checking birthdays, SQL dates are a little easier to handle.

[neljan]

neljan;10964124 wrote:

If it helps, the birthdays look like this in the table:

DD-MM-YYYY

:eek: what the hell is wrong with me?

It's YYYY-MM-DD and the type is 'DATE'.

If you'd like to shoot me I'll even load the gun for you.

[ixalmida]

Can you post a few rows of sample data (preferably with upcoming birthdays in it)? I'm not understanding why my suggestions didn't work for you. Please alter any data that needs to be kept private. 🙂

[neljan]

Hi ixalmida

Ok heres the result:

Heres the db showing the entry for Timothy:

(note that Jordan's birthday comes before Timothys).

The settings for the 'birthday' field:

The next 5 birthdays should be:

[CODE]username | birthday

Sousui | 11/10
AGP | 17/10
Nas | 06/11
Tiporosso | 16/11
Andriz | 22/11[/CODE]

Just to clarify, the complete code I have is:

<?

// Todays birthdays

$ergebnis=safe_query("SELECT nickname, userID, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birthday)), '%y') 'age' FROM ".PREFIX."user WHERE DAYOFYEAR(birthday)=DAYOFYEAR(NOW()+1)");

$n=0;

while($db=mysql_fetch_array($ergebnis)) {

    $n++;

    $years=$db['age'];

    if($n>1) $birthdays.=', <a href="index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a> ('.$years.')';

    else $birthdays.='<a href="index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a> ('.$years.')';

}

if(!$n) $birthdays="None";


// Upcoming birthdays

$query = "SELECT nickname, userID, birthday, YEAR(NOW()) - YEAR(birthday) AS age FROM ".PREFIX."user WHERE MONTH(birthday) > MONTH(NOW()) AND DAY(birthday) > DAY(NOW()) ORDER BY DAYOFYEAR(birthday) ASC LIMIT 5";

$ergebnis=safe_query($query)
  or die(mysql_error()."<br>".$query); 

$n=0;

while($db=mysql_fetch_array($ergebnis)) {

list($year, $month, $day) = explode('-', $db['birthday']);

    $n++;

    $years=$db['age'];

    if($n>1) $birthweek.= ''.$day.'.'.$month.' <a href="index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a>  ('.$years.')<br />';

    else $birthweek.= ''.$day.'.'.$month.' <a href"index.php?site=profile&id='.$db[userID].'"><b>'.$db[nickname].'</b></a> ('.$years.')<br />';

}

if(!$n) $birthweek="None";

eval ("\$sc_birthdays = \"".gettemplate("sc_birthdays")."\";");
echo $sc_birthdays;

?>

Thanks again for your time, appreciate it 🙂