Answers.Body: <p>Try this query (the only difference being the WHERE clause):</p>
  
  <pre><code>SELECT id, display_name, birth_date, CONCAT(((RIGHT(birth_date,5)
  &lt; 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) &lt; 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) &lt; RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
  RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE) &lt; 7) AND is_deceased = 0 AND is_deleted = 0 ORDER BY bday, RIGHT(birth_date,5);
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-04T09:10:10.910Z
Answers.Text: Thx Jon. Will try this later today.
Answers.UserDisplayName: Johan den Hollander
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-04T14:37:56.650Z
Answers.Text: That worked. I made some small changes to fit my needs considering date and name formats.
Answers.UserDisplayName: Johan den Hollander
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-02-04T06:17:44.350Z
Answers.LastActivityDate: 2016-02-04T06:17:44.350Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>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 <code>civicrm_contact</code> \n"
          . "where <code>is_deceased</code> = false";</p>
  
  <pre><code>    &lt;?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)
  &lt; 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) &lt; 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) &lt; RIGHT(CURRENT_DATE,5)) + YEAR(CURRENT_DATE)),
  RIGHT(birth_date,6))) - TO_DAYS(CURRENT_DATE) &lt; 7) ORDER BY bday, RIGHT(birth_date,5);";
  
  $params = array( );
  
  $dao =&amp; CRM_Core_DAO::executeQuery( $query, $params );
  
  echo "&lt;div class=\"item-list\"&gt;&lt;ul&gt;\n";
  while ( $dao-&gt;fetch( ) ) {
  
  echo "&lt;li&gt;&lt;a href=\"/civicrm/contact/view?reset=1&amp;cid=" . $dao-&gt;id . "\"&gt;" . $dao-&gt;display_name .
  "&lt;/a&gt;, " . $dao-&gt;displaydate;
  
  echo "&lt;/li&gt;\n";
  
  }
  echo "&lt;/div&gt;&lt;/ul&gt;\n";
  
  ?&gt;
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-02-03T23:21:08.633Z
Comments.Text: Is there a reason you're using PHP rather than creating a block with Views?
Comments.UserDisplayName: Allen Hutchison
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-02-04T03:38:48.853Z
Comments.Text: 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
Comments.UserDisplayName: petednz - fuzion
Tags: <drupal>
Title: Birthday block in Drupal without deceased and deleted contacts
ThreadText: # Birthday block in Drupal without deceased and deleted contacts
  
  (Asked by: username_0 on 2016-02-03)
  
  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";
  
  ?>
  
  ```
  
  **Tags:** drupal
  
  > **Comments:**
  > **username_1**: Is there a reason you're using PHP rather than creating a block with Views?  
  > **username_2**: 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  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2016-02-04. Score: 2 | Accepted answer
  
  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);
  
  ```
  
  > **Comments:**
  > **username_0**: Thx Jon. Will try this later today.  
  > **username_0**: That worked. I made some small changes to fit my needs considering date and name formats.  
  
  ---
  
  