[Resolved] Birthday issue

slimjim

ok i have a member table and inside that table i have a field called birthday and that field is setup like this :

birthday date NOT NULL default '0000-00-00',

Fields i want to use for listing the Birthdays is fname and lname

A few questions.

1) how do i do the query so that it looks thru all members birthday fields and finds birthdays listed this month based on the current month, and list fname and lname and of course month and day and NOT THE YEAR. But my problem using the code below, shows me this:
Happy Birthday To following members
John Doe 1956-01-07

Mary Jane 1967-01-23

and i want it to show it like this.
Happy Birthday To following members
John doe January 07
Mary Jane January 23

Don't even want to use the year for right now. After I get help figuring this out , i would like to have it calculate the persons birthday so it would show that John doe is 46 years old based on year he was born but for now just working on the one issue.

order by first day of month downward.

Now if there isnt a birthday i want it to show "No birthdays this month".
2) How do i make the form so that it inputs the right format into the birthday field ?

3) Now i was going to use but like i said it's not how i need it:

<table border="0" cellpadding="0" cellspacing="0" width="400">
          <tr><td>Happy Birthday To following members</td></tr>
<?
  $query="SELECT fname, lname, birthday FROM members WHERE month(now())=month(birthday)";

$result = mysql_query($query) or die("Query failed");	


while ($myrow = mysql_fetch_array($result)) {
	?>

<tr><td><? echo $myrow["fname"]?> <? echo $myrow["lname"]?> <? echo $myrow["birthday"]?> </td></tr>

<?
	}

?>          </table>

TTT

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Date_calculations

slimjim

http://www.mysql.com/documentation/...te_calculations

Thank You First Of All.
Well that is usefull for my problem for figuring out the age, but it doesnt help me with figuring this problem :

and i want it to show it like this.
Happy Birthday To following members
John doe January 07
Mary Jane January 23

Notice the difference.....

Now if there isnt a birthday i want it to show "No birthdays this month".

TTT

i think you didn't read whole description...

You can even write the query so that it works no matter what the current month is. That way you don't have to use a particular month number in the query. DATE_ADD() allows you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

and from manual

slimjim

Ok figured out the process to look for birthdays of the current month.

  $query="SELECT fname, lname, birthday FROM members WHERE month(now())=month(birthday)";

That's not my problem. My problem is the form that imputs the birthday ( not sure how to make it so that the format is right )

Other problem is this : With the above query i get results like this :
Happy Birthday To following members
John Doe 1959-01-06
May Jane 1956-01-08

I want results that look like this :
Happy Birthday To following members
John Doe January 07
May Jane January 23

So not sure if i have the table set up right and not sure about the form at all or how i should write the insert code.

Thank You

weevil

Check the date_format mysql function
http://www.mysql.com/doc/en/Date_and_time_functions.html

$query="SELECT fname, lname, date_format(birthday, '%M %e')FROM members WHERE month(now())=month(birthday)";

slimjim

Nope now i get the results showing without any date.

weevil

Try printing $myrow[2] instead of $myrow[birthday]
you can also change your query to

$query="SELECT fname, lname, date_format(birthday, '%M %e') AS formatted_birthday FROM members WHERE month(now())=month(birthday)";

and echo $myrow[formatted_birthday]

slimjim

Now i got it going with your help. You know i read that info and now trying to figure out how you came up with the query you did from http://www.mysql.com/doc/en/Date_and_time_functions.html

I mean on that page shows me nothing that has to do with using the query you used.

weevil

This table about half way down the page shows all of the options you can use with date_format

DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string:
Specifier Description

%a Abbreviated weekday name (Sun..Sat)

%b Abbreviated month name (Jan..Dec)

%c Month, numeric (0..12)

%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, etc.)

%d Day of the month, numeric (00..31)

%e Day of the month, numeric (0..31)

%f Microseconds (000000..999999)

%H Hour (00..23)

%h Hour (01..12)

%I Hour (01..12)

%i Minutes, numeric (00..59)

%j Day of year (001..366)

%k Hour (0..23)

%l Hour (1..12)

%M Month name (January..December)

%m Month, numeric (00..12)

%p AM or PM

%r Time, 12-hour (hh:mm:ss followed by AM or PM)

%S Seconds (00..59)

%s Seconds (00..59)

%T Time, 24-hour (hh:mm:ss)

%U Week (00..53), where Sunday is the first day of the week

%u Week (00..53), where Monday is the first day of the week

%V Week (01..53), where Sunday is the first day of the week; used with %X

%v Week (01..53), where Monday is the first day of the week; used with %x

%W Weekday name (Sunday..Saturday)

%w Day of the week (0=Sunday..6=Saturday)

%X Year for the week where Sunday is the first day of the week, numeric, 4 digits; used with %V

%x Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with %v

%Y Year, numeric, 4 digits

%y Year, numeric, 2 digits

%% A literal `%'.

slimjim

Great but how would i have guessed to use
AS formatted_birthday
In the query ?

Well i guess that's why I am newbie 🙂

Now going to try and figure out how to take this information and make it so that i can email the birthday list out to all birthday members.

Any idea on how to do it ?

slimjim

Well actually i need to figure out the insert code and form now so that the birthday info goes into the databse table birthday right.

ANy help on that ?

weevil

The php mail function is a good place to start for sending the email.

What do you have so far for the insert code and form?

slimjim

Here is what i have for insert but of course without the birthday added"

insert into members values('0','$login','$pswd_1','$fname','$lname','$email','$city','$state','$country','$zip','$phone','$fax','','0','$dt1')");

And the form But not really sure how to add the birthday field in form so that it shows up in databse as 0000-00-00:

<form action="register.php?action=submit" method=post>
      <tr> 
        <td colspan="2"><p><font face="Tahoma"><strong>Account details</strong></font></p>
        <p><b><font color="#FF0000">If there is a</font><font color="#000000" size="4"> *</font><font color="#FF0000"> then that field must be filled out</font></b></p>
        <br>
        </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Username *</font></td>
        <td> <font face="Tahoma"> <input type="text" name="login" size="20">
          <font size="1"> (i.e. joe42) </font></font></td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Password *</font></td>
        <td> <font face="Tahoma"> <input type="password" name="pswd_1" size="20">
          <font size="1">(i.e. c4dfo9348t6 ) </font></font></td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Confirm password *</font></td>
        <td> <font face="Tahoma"> <input type="password" name="pswd_2" size="20">
          <font size="1"> (the same as password) </font></font></td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">E-mail address *</font></td>
        <td> <font face="Tahoma"> <input type="text" name="email" size="20">
          <font size="1">(i.e. [email]joesmoe@hotmail.com[/email])</font> </font> </td>
      </tr>
      <tr> 
        <td colspan="2"><p>&nbsp;</p>
          <p><font face="Tahoma"><strong>Owner Details</strong></font></p></td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">First name *</font></td>
        <td> <input type="text" name="fname" size="20"> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Last name *</font></td>
        <td> <input type="text" name="lname" size="20"> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">City</font></td>
        <td> <input type="text" name="city" size="20"> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">State/County</font></td>
        <td> <select name="state">
            <option>Select One</option>
            <option value="International">International</option>
            <option value='Alabama'>Alabama</option>
            <option value='Alaska'>Alaska</option>
            <option value='Arizona'>Arizona</option>
            <option value='Arkansas'>Arkansas</option>
            <option value='California'>California</option>
            <option value='Colorado'>Colorado</option>
            <option value='Connecticut'>Connecticut</option>
            <option value='Washington D.C.'>Washington D.C.</option>
            <option value='Delaware'>Delaware</option>
            <option value='Florida'>Florida</option>
            <option value='Georgia'>Georgia</option>
            <option value='Hawaii'>Hawaii</option>
            <option value='Idaho'>Idaho</option>
            <option value='Illinois'>Illinois</option>
            <option value='Indiana'>Indiana</option>
            <option value='Iowa'>Iowa</option>
            <option value='Kansas'>Kansas</option>
            <option value='Kentucky'>Kentucky</option>
            <option value='Louisiana'>Louisiana</option>
            <option value='Maine'>Maine</option>
            <option value='Maryland'>Maryland</option>
            <option value='Massachusetts'>Massachusetts</option>
            <option value='Michigan'>Michigan</option>
            <option value='Minnesota'>Minnesota</option>
            <option value='Mississippi'>Mississippi</option>
            <option value='Missouri'>Missouri</option>
            <option value='Montana'>Montana</option>
            <option value='Nebraska'>Nebraska</option>
            <option value='Nevada'>Nevada</option>
            <option value='New Hampshire'>New Hampshire</option>
            <option value='New Jersey'>New Jersey</option>
            <option value='New Mexico'>New Mexico</option>
            <option value='New York'>New York</option>
            <option value='North Carolina'>North Carolina</option>
            <option value='North Dakota'>North Dakota</option>
            <option value='Ohio'>Ohio</option>
            <option value='Oklahoma'>Oklahoma</option>
            <option value='Oregon'>Oregon</option>
            <option value='Pennsylvania'>Pennsylvania</option>
            <option value='Puerto Rico'>Puerto Rico</option>
            <option value='Rhode Island'>Rhode Island</option>
            <option value='South Carolina'>South Carolina</option>
            <option value='South Dakota'>South Dakota</option>
            <option value='Tennessee'>Tennessee</option>
            <option value='Texas'>Texas</option>
            <option value='Utah'>Utah</option>
            <option value='Vermont'>Vermont</option>
            <option value='Virginia'>Virginia</option>
            <option value='Washington'>Washington</option>
            <option value='West Virginia'>West Virginia</option>
            <option value='Wisconsin'>Wisconsin</option>
            <option value='Wyoming'>Wyoming</option>
          </select> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Country</font></td>
        <td> <select name="country">
            <option>Select One</option>
            <option value='United States of America'>United States of America</option>
                      </select> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Postal/ZIP Code</font></td>
        <td> <input type="text" name="zip" size="20"> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Phone</font></td>
        <td> <input type="text" name="phone" size="20"> </td>
      </tr>
      <tr> 
        <td><font face="Tahoma" size="2">Fax</font></td>
        <td> <input type="text" name="fax" size="20"> </td>
      </tr>
      <tr> 
        <td colspan="2"> <div align="center">
		 <input name="uid" type="hidden" id="uid" value="<?php echo $uid; ?>">
            <input type="reset" name="Submit2" value="Reset">
            <input type="submit" name="Submit" value="Register">
            <input type="hidden" name="action" value="submit">
          </div></td>
      </tr>
    </form>

jasonmills58

well, i'm pretty new myself, but if you want to enforce your date format,
you could always build three pull-down menus, with year, month, and day formatted how you like,
and then just append those together in the processing phase.

weevil

I would use the 3 drop downs as Jason suggested with 2 digit values for all days and months (ex: 01 for January instead of 1
and then just append the values together in this order

$date = "$_POST[year]$_POST[month]$_POST[day]"

slimjim

Ok here is what i did :
In the register page
I made three drop down boxes one for year.month.day

in the insert statment under value i added $birthdate

And above insert query i added this :
$birthdate =$year.$month.$day;

Works perfect.

Now i have a question on order by. Look at my query and tell me how I can order the date by earliest day first ? I tried order by day but of course that doesnt work.

<?
$query="SELECT fname, lname, date_format(birthday, '%M %e') AS formatted_birthday FROM members WHERE month(now())=month(birthday)";

$result = mysql_query($query) or die("Query failed");	
while ($myrow = mysql_fetch_array($result)) {
	?>

slimjim

Now I have search all over and can't find any information on how to do a order by using the day part of date type
0000-00-00
Is this possible to do ?
so if i have in my db
1959-02-09
1959-02-04
1959-02-08

And i want to order then by smallest day first, is there a way?

weevil

You can use the dayofmonth mysql function

$query="SELECT fname, lanme, date_format(birthday, '%M %e') AS formatted_birthday,  DAYOFMONTH(birthday)  AS day_of_birth FROM members WHERE month(now())=month(birthday) ORDER  BY day_of_birth";

slimjim

Worked like a charm Thank You