Adastra Posted August 31, 2006 Share Posted August 31, 2006 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: January01/11/75 - Jane Doe (31)01/21/80 - Janette Doe (26)February02/02/79 - Peter Parker (27)02/18/44 - Clark Kent (62)...and so onMy current code looks like this. [code]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 />";}[/code]I'd be grateful for any help, snippets & stuff like that ;) Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 31, 2006 Share Posted August 31, 2006 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!!!!! Quote Link to comment Share on other sites More sharing options...
Adastra Posted August 31, 2006 Author Share Posted August 31, 2006 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.... Quote Link to comment Share on other sites More sharing options...
redarrow Posted August 31, 2006 Share Posted August 31, 2006 DESC or ASC look them up.// For your select issue.also look up date on php.net // That to display the months. Quote Link to comment Share on other sites More sharing options...
Adastra Posted August 31, 2006 Author Share Posted August 31, 2006 [quote author=redarrow link=topic=106369.msg425337#msg425337 date=1157030019]DESC or ASC look them up.// For your select issue.also look up date on php.net // That to display the months.[/quote]I know how to use DESC, ASC and date. My problem is that I don't know how to sort by [b]month[/b] with the timestamp instead of sorting by year (=timestamp)....:/ Quote Link to comment Share on other sites More sharing options...
redarrow Posted August 31, 2006 Share Posted August 31, 2006 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. Quote Link to comment Share on other sites More sharing options...
Adastra Posted August 31, 2006 Author Share Posted August 31, 2006 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). [code]$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 />"; } }}[/code] Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 31, 2006 Share Posted August 31, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.