2

I found a piece of code to show a birthday block in Drupal. Had to modify it a little bit to get it to work with the current versions. But I noticed that deleted or deceased persons were still showing up in this list. I don't know the correct code for this. Could someone please fix this code for me and others that are wanting to use this code? My php / mysql skills are not enough but I think it must be something like: $sql = "SELECT * FROM civicrm_contact \n" . "where is_deceased = false";

    <?php
// Check if CiviCRM is installed here.
if (!module_exists('civicrm')) return false;

// Initialization call is required to use CiviCRM APIs.
civicrm_initialize(true);
require_once('CRM/Core/DAO.php');

$query = "SELECT id, display_name, birth_date, CONCAT(((RIGHT(birth_date,5)
< RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)), RIGHT(birth_date,6)) AS bday,
concat(concat(month(birth_date), '/'), day(birth_date)) as displaydate, 
(TO_DAYS(CONCAT(((RIGHT(birth_date,5) < RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE)) AS toBday FROM civicrm_contact WHERE
(TO_DAYS(CONCAT(((RIGHT(birth_date,5) < RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE) < 7) ORDER BY bday, RIGHT(birth_date,5);";

$params = array( );

$dao =& CRM_Core_DAO::executeQuery( $query, $params );

echo "<div class=\"item-list\"><ul>\n";
while ( $dao->fetch( ) ) {

echo "<li><a href=\"/civicrm/contact/view?reset=1&cid=" . $dao->id . "\">" . $dao->display_name .
"</a>, " . $dao->displaydate;

echo "</li>\n";

}
echo "</div></ul>\n";

?>
Improve this question
  • Is there a reason you're using PHP rather than creating a block with Views? – Allen Hutchison Feb 3 '16 at 23:21
  • i suspect because of the difficulty in getting anyone whose birthday is in Month x ie regardless of the year. Various workarounds have been suggested in drupal including using php in Views so perhaps the above is a 'tidy' solution – petednz - fuzion♦ Feb 4 '16 at 3:38
2

Try this query (the only difference being the WHERE clause):

SELECT id, display_name, birth_date, CONCAT(((RIGHT(birth_date,5)
< RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)), RIGHT(birth_date,6)) AS bday,
concat(concat(month(birth_date), '/'), day(birth_date)) as displaydate, 
(TO_DAYS(CONCAT(((RIGHT(birth_date,5) < RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE)) AS toBday FROM civicrm_contact WHERE
(TO_DAYS(CONCAT(((RIGHT(birth_date,5) < RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE) < 7) AND is_deceased = 0 AND is_deleted = 0 ORDER BY bday, RIGHT(birth_date,5);
Improve this answer
  • Thx Jon. Will try this later today. – Johan den Hollander Feb 4 '16 at 9:10
  • That worked. I made some small changes to fit my needs considering date and name formats. – Johan den Hollander Feb 4 '16 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.