How to display upcoming birthdays?

Scorptique · December 6, 2008

Hello again guys, I need some help here.

I have a list of contact details, which includes their birthdays. If user clicks the link "Sort by birthday", the url will become contacts.php?category=cl_dob. So I use the GET method and put it in $category. How do I sort the contacts by the upcoming birthdays? Means the contact with the nearest birthday will be at the top.

I tried this but it didnt work. Help guys.

if ($category == 'cl_dob')

{

$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob from contact_list where username = '$username' AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d') ORDER BY cl_dob DESC ";

}

(I didnt use the <code></code> thingy because there was sth wrong with it)

gevans · December 6, 2008

You can't use an alias to order, try this;

$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username'  AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";

Note, to get the formatted date use $row['cl_dob_formatted']

Also the code tags are [ code ] [ /code ] not <code> </code>

Scorptique · December 6, 2008

Hey Gevans, thanks for the reply. But the codes didn't work. Nothing got displayed.

gevans · December 6, 2008

Do you have error reporting on? That way your browser will give you a lovely error telling you whats going wrong

Scorptique · December 6, 2008

Not even a lovely lil error got displayed =(

gevans · December 6, 2008

put this at the top of your script and try again

<?php
// at the top of your script - this may not work, depending on how PHP's configured
ini_set('display_errors', 'On');
error_reporting(E_ALL | E_STRICT);

...

?>

Scorptique · December 6, 2008

ah, really no error at all. I think it's because it deals with the mySQL syntax logic. Nothing to do with the php code.

gevans · December 6, 2008

If it was a syntax error php would still display an error.

Can you post all your code for me?

revraz · December 6, 2008

echo $sql and see what it contains.

Are you using mysql_error() after your query?

Scorptique · December 6, 2008

Well, I dont think that's a good idea because it is 300 lines long! I just paste the few I think are important.

Sort by <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_fname">name</a> | 
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_dob">Incoming birthdays</a> | 
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_email">Email</a>


$category= $_GET['sort'];
if (empty($category))
{
	$category = 'cl_fname';
}


$conn = mysql_connect("localhost", "root", null);
mysql_select_db("ecarddb", $conn);
if ($category == 'cl_fname'){
$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob from contact_list where username = '$username' order by cl_fname";}
if ($category == 'cl_email'){
$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob from contact_list where username = '$username' order by cl_email";}
if ($category == 'cl_dob'){
$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username'  AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";}

$result = mysql_query($sql, $conn) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
	$cl_email = $row['cl_email'];
	?>
        
        <table style="word-wrap:break-word;">
  		<tr>
    	<td height="21">
	<?php echo '<td width="100">'. $row['cl_fname'].', '. $row['cl_lname'].'</td>';
	echo '<td width="85">'.$row['cl_dob'].'</td>';
	echo '<td width="170">'.$row['cl_email'].'</td>'; ?></td>
		</tr>
	</table> <?php
}
mysql_close($conn);

gevans · December 6, 2008

Note, to get the formatted date use $row['cl_dob_formatted']

Sort by <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_fname">name</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_dob">Incoming birthdays</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_email">Email</a>
   

$category= $_GET['sort'];
   if (empty($category))
   {
      $category = 'cl_fname';
   }

   
   $conn = mysql_connect("localhost", "root", null);
   mysql_select_db("ecarddb", $conn);
   if ($category == 'cl_fname'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_fname";}
   if ($category == 'cl_email'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_email";}
   if ($category == 'cl_dob'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username'  AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";}

   $result = mysql_query($sql, $conn) or die(mysql_error());
   while($row = mysql_fetch_array($result))
   {
      $cl_email = $row['cl_email'];
      ?>
       
        <table style="word-wrap:break-word;">
        <tr>
       <td height="21">
      <?php echo '<td width="100">'. $row['cl_fname'].', '. $row['cl_lname'].'</td>';
      echo '<td width="85">'.$row['cl_dob_formatted'].'</td>';
      echo '<td width="170">'.$row['cl_email'].'</td>'; ?></td>
       </tr>
      </table> <?php
   }
   mysql_close($conn);

Scorptique · December 6, 2008

The table is still blank D=

gevans · December 6, 2008

Sort by <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_fname">name</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_dob">Incoming birthdays</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_email">Email</a>
   

$category= $_GET['sort'];
   if (empty($category))
   {
      $category = 'cl_fname';
   }

   
   $conn = mysql_connect("localhost", "root", null);
   mysql_select_db("ecarddb", $conn);
   if ($category == 'cl_fname'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_fname";}
   if ($category == 'cl_email'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_email";}
   if ($category == 'cl_dob'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username'  AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";}

echo $sql;

die();

   $result = mysql_query($sql, $conn) or die(mysql_error());
   while($row = mysql_fetch_array($result))
   {
      $cl_email = $row['cl_email'];
      ?>
       
        <table style="word-wrap:break-word;">
        <tr>
       <td height="21">
      <?php echo '<td width="100">'. $row['cl_fname'].', '. $row['cl_lname'].'</td>';
      echo '<td width="85">'.$row['cl_dob_formatted'].'</td>';
      echo '<td width="170">'.$row['cl_email'].'</td>'; ?></td>
       </tr>
      </table> <?php
   }
   mysql_close($conn);

That will echo the query so we can see whats going on

Scorptique · December 6, 2008

Hm..it just echos out the same statement.

"select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = 'raven' AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d') ORDER BY cl_dob DESC"

gevans · December 6, 2008

I'm a bit lost, so I'm going to try a few other things...

Sort by <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_fname">name</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_dob">Incoming birthdays</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_email">Email</a>
   

$category= $_GET['sort'];
   if (empty($category))
   {
      $category = 'cl_fname';
   }

   
   $conn = mysql_connect("localhost", "root", null);
   mysql_select_db("ecarddb", $conn);
   if ($category == 'cl_fname'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_fname";}
   if ($category == 'cl_email'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username' order by cl_email";}
   if ($category == 'cl_dob'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where username = '$username'  AND DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";}

   $result = mysql_query($sql, $conn) or die(mysql_error());
  echo mysql_num_rows($result);
die();
   while($row = mysql_fetch_array($result))
   {
      $cl_email = $row['cl_email'];
      ?>
       
        <table style="word-wrap:break-word;">
        <tr>
       <td height="21">
      <?php echo '<td width="100">'. $row['cl_fname'].', '. $row['cl_lname'].'</td>';
      echo '<td width="85">'.$row['cl_dob_formatted'].'</td>';
      echo '<td width="170">'.$row['cl_email'].'</td>'; ?></td>
       </tr>
      </table> <?php
   }
   mysql_close($conn);

This will echo the number of rows you're getting back from the database

Scorptique · December 6, 2008

It says 1. lol, why 1? Im also lost.

gevans · December 6, 2008

That's good, it means it's getting 1 row back from the database (the row with all your info)

Actually, if you want the whole contact list to show why do you have WHERE username= in the query?

Scorptique · December 6, 2008

Hmm, the results I expected is not one row. Is it all the rows from the whole contact list with the birthdays sorted out in upcoming ones first.

And yep...I used a where username='$username'.

gevans · December 6, 2008

That's why you only get one row, because your asking for all the rows from the database where username='$username' (that is only one row)

try this;

Sort by <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_fname">name</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_dob">Incoming birthdays</a> |
    <a href="main3.php?card_id=<?php echo $card; ?>&sort=cl_email">Email</a>
   

$category= $_GET['sort'];
   if (empty($category))
   {
      $category = 'cl_fname';
   }

   
   $conn = mysql_connect("localhost", "root", null);
   mysql_select_db("ecarddb", $conn);
   if ($category == 'cl_fname'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list order by cl_fname";}
   if ($category == 'cl_email'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list order by cl_email";}
   if ($category == 'cl_dob'){
   $sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob_formatted from contact_list where DATE_FORMAT('cl_dob', '%m-%d') > DATE_FORMAT(NOW(), '%m-%d')  ORDER BY cl_dob DESC ";}

   $result = mysql_query($sql, $conn) or die(mysql_error());

   while($row = mysql_fetch_array($result))
   {
      $cl_email = $row['cl_email'];
      ?>
       
        <table style="word-wrap:break-word;">
        <tr>
       <td height="21">
      <?php echo '<td width="100">'. $row['cl_fname'].', '. $row['cl_lname'].'</td>';
      echo '<td width="85">'.$row['cl_dob_formatted'].'</td>';
      echo '<td width="170">'.$row['cl_email'].'</td>'; ?></td>
       </tr>
      </table> <?php
   }
   mysql_close($conn);

Scorptique · December 6, 2008

oops. sorry gevans. I didnt tell you earlier. There are like 20 over rows with the same username.

This contact_list table is actually the list of contacts from a specific user, so username is not the unique one. cl_id is.

gevans · December 6, 2008

Ahh ok, my bad... I'm seriously lost then.

I'll have a look again, but am very confused

Scorptique · December 6, 2008

OMG. Gevans! I found this code somewhere online...

$sql = "select cl_id, cl_fname, cl_lname, cl_email, date_format(cl_dob, '%d %b %Y') AS cl_dob,
(TO_DAYS((cl_dob + INTERVAL (YEAR(CURRENT_DATE)-YEAR(cl_dob) + (RIGHT(CURRENT_DATE,5)>
RIGHT(cl_dob,5))) YEAR)) - TO_DAYS(CURRENT_DATE)) as daysAway from contact_list where
username = '$username' order by daysAway DESC";

And it works! But...i dont really understand (TO_DAYS((cl_dob + INTERVAL (YEAR(CURRENT_DATE)-YEAR(cl_dob) + (RIGHT(CURRENT_DATE,5)>RIGHT(cl_dob,5))) YEAR)) - TO_DAYS(CURRENT_DATE)). if you understand, can you explain to me? or should I post this in the mysql forum instead.

gevans · December 6, 2008

Glad you got it working!! your best way to find out what all the seperate parts are doing is to visit mysql.com.

There you'll be able to find all those functions and find out what they do.

Alternatively as you said you could post it in the mysql forum

Scorptique · December 6, 2008

FALSE ALARM. LOL. It doesnt work! =( It did rearrange the dates into some...other...arrangement =/

Sigh.

Sign In

How to display upcoming birthdays?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information