Jump to content


Photo

Selecting & Timestamp problems with birthday calendar


  • Please log in to reply
7 replies to this topic

#1 Adastra

Adastra
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 11:31 AM

I made a tiny birthday calendar for private use, which uses a timestamp for the birthday. So far it works fine, but since I'm a PHP noob, I have two problems:

1) How can I do a select sorted by month? Right now, when I select with ORDER BY timestamp, it gives me the oldest person first and the youngest last, means it's sorted by year.

2) How can I have it automatically sorting it by month with the month as headline? Like this example:

January
01/11/75 - Jane Doe (31)
01/21/80 - Janette Doe (26)

February
02/02/79 - Peter Parker (27)
02/18/44 - Clark Kent (62)

...and so on


My current code looks like this.
include('connect');
$bday_table = "mytable";

$bday_today_month = date("F");
$bday_today_monthm = date("m");
$bday_today_year = date("Y");

$bday_sql = "SELECT * FROM $bday_table ORDER BY timestamp";
$bday_result = mysql_query($bday_sql) or	print ("Unable to select data.<br />" . $bday_sql . "<br />" . mysql_error());
while ($bday_row = mysql_fetch_array($bday_result)) {
	$bday_date = date("M d, Y",$bday_row["timestamp"]);
	$bday_name = $bday_row["name"];
	$bday_email = $bday_row["email"];
	$bday_url = $bday_row["url"];
	
	$bday_year = date("Y",$bday_row["timestamp"]);
	$bday_age = ($bday_today_year - $bday_year);
	
	echo $bday_date." - ".$bday_name." (".$bday_age.")<br />";
}

I'd be grateful for any help, snippets & stuff like that ;)

#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 31 August 2006 - 12:12 PM

TOP TIP - don't use timestamp for peoples birthdays!

What if some born in 1969 (are they still alive!!!) wanted to use it????

You are better off using the built-in time/date datatypes of which ever database you are using - they are better than those offered in php.

Calculation between dates is also better in the database domain - according to some tets I read of course!!!!!
follow me on twitter @PHPsycho

#3 Adastra

Adastra
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 12:14 PM

It works perfectly fine with people born before 1970 - the timestamps of those are with a minus in front. I haven't had any problems with that so far....

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 31 August 2006 - 01:13 PM

DESC or ASC look them up.// For your select issue.

also look up date on  php.net // That to display the months.


Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 Adastra

Adastra
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 01:17 PM

DESC or ASC look them up.// For your select issue.

also look up date on  php.net // That to display the months.



I know how to use DESC, ASC and date.
My problem is that I don't know how to sort by month with the timestamp instead of sorting by year (=timestamp)....
:/

#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 31 August 2006 - 01:23 PM

i posted the code becouse as you were advised from the other member that your database does all the work for you in this situation, your code is programmed badly to format the way you want it to work i had a good look and couldnt come up with any possabiltys so sorry.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#7 Adastra

Adastra
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 August 2006 - 02:17 PM

I know there's some way this will work out, I just need to know how to extract the month from a timestamp first.

I attempted something like this, but it didn't return any results. (I'm a PHP noob, I only have a vague idea of what I'm doing).

$bday_today_month = date("F");
$bday_today_monthm = date("m");
$bday_today_year = date("Y");

$months = array(01,02,03,04,05,06,07,08,09,10,11,12);

foreach ($months as $month) {
	$months_sql = "SELECT * FROM $bday_table WHERE timestamp = month($month) ORDER BY timestamp";
	$months_result = mysql_query($months_sql) or	print ("Unable to select data.<br />" . $months_sql . "<br />" . mysql_error());

	while ($months_row = mysql_fetch_array($months_result)) {
		$months_date = date("M",$months_row["timestamp"]);
		echo $months_date ;
		
		
			$bday_sql = "SELECT * FROM $bday_table FROM_UNIXTIME(timestamp,'%M') = $month ORDER BY timestamp";
			$bday_result = mysql_query($bday_sql) or	print ("Unable to select data.<br />" . $bday_sql . "<br />" . mysql_error());
			while ($bday_row = mysql_fetch_array($bday_result)) {
				$bday_date = date("M d, Y",$bday_row["timestamp"]);
				$bday_name = $bday_row["name"];
				$bday_email = $bday_row["email"];
				$bday_url = $bday_row["url"];
				
				$bday_year = date("Y",$bday_row["timestamp"]);
				$bday_age = ($bday_today_year - $bday_year);
				
				echo $bday_date." - ".$bday_name." (".$bday_age.")<br />";
			}
		}
}


#8 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 31 August 2006 - 02:32 PM

If you have it read the date - time chapter in 'PHP and MySQL Web Devlopment'

In there it points out a few pitfalls in using php to do date/time calculations. I still maintain it is better to let mysql do those for you. Far superior functionality like accounting for leap years and using the gregorian calendar - which IMO is better when dealing with dates outside the unix timestamp range.
follow me on twitter @PHPsycho




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users