unrelenting Posted August 23, 2008 Share Posted August 23, 2008 I have a date column in my mysql database with dates like this: 2005-06-24 and 1898-10-12 I want to display them like this: 06/24/2005 and 10/12/1898. What's the best method? Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Wait wait wait. What type is the database column? I hope that it's DATE and not VARCHAR. If it's date, you can just do something like: #SQL SELECT DATE_FORMAT(somedate, '%c/%e/%Y') AS date FROM users WHERE user_id = 4; Quote Link to comment Share on other sites More sharing options...
JasonLewis Posted August 23, 2008 Share Posted August 23, 2008 You can also take a look at these functions. date() and strtotime(). Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Wait wait wait. What type is the database column? I hope that it's DATE and not VARCHAR. If it's date, you can just do something like: #SQL SELECT DATE_FORMAT(somedate, '%c/%e/%Y') AS date FROM users WHERE user_id = 4; Yes, it's a date format column and that would work but I am planning to do all sorts of queries involving that column and I'd prefer a method using PHP to do it but thanks. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 You can also take a look at these functions. date() and strtotime(). Nothing beats having MySQL do it for you though. Quote Link to comment Share on other sites More sharing options...
ratcateme Posted August 23, 2008 Share Posted August 23, 2008 some like this: $date = date("m/j/y",strtotime($mysql_date)); Scott. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Wait wait wait. What type is the database column? I hope that it's DATE and not VARCHAR. If it's date, you can just do something like: #SQL SELECT DATE_FORMAT(somedate, '%c/%e/%Y') AS date FROM users WHERE user_id = 4; Yes, it's a date format column and that would work but I am planning to do all sorts of queries involving that column and I'd prefer a method using PHP to do it but thanks. Uhh...Elaborate. That sounds pretty inefficient actually. Can you show me what you mean and maybe I can help? >_< Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Will these strtotime() and date() functions work on dates from the 1800's. DarkWater, I just started working with this table and haven't decided on all of the queries I will use but there will be many. It's a football schedule and it has a lot of data to tinker with. I will have it set for searching and comparisons involving different opponents and coaches and years. Just all sorts of options. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Will these strtotime() and date() functions work on dates from the 1800's. DarkWater, I just started working with this table and haven't decided on all of the queries I will use but there will be many. It's a football schedule and it has a lot of data to tinker with. I will have it set for searching and comparisons involving different opponents and coaches and years. Just all sorts of options. That sounds like it's much easier with a well-written query and normalized tables than tons of conversions in PHP. Quote Link to comment Share on other sites More sharing options...
JasonLewis Posted August 23, 2008 Share Posted August 23, 2008 Yeah I don't think you can do dates from too long ago. No harm in trying but I don't think it would work. Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Will these strtotime() and date() functions work on dates from the 1800's. DarkWater, I just started working with this table and haven't decided on all of the queries I will use but there will be many. It's a football schedule and it has a lot of data to tinker with. I will have it set for searching and comparisons involving different opponents and coaches and years. Just all sorts of options. That sounds like it's much easier with a well-written query and normalized tables than tons of conversions in PHP. If I want to display a table with only the dates from year 1952 and also display all of these other columns using a simple while loop how would I word the query? Table: old_schedules Columns: year, date, opponent, location, final Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Will these strtotime() and date() functions work on dates from the 1800's. DarkWater, I just started working with this table and haven't decided on all of the queries I will use but there will be many. It's a football schedule and it has a lot of data to tinker with. I will have it set for searching and comparisons involving different opponents and coaches and years. Just all sorts of options. That sounds like it's much easier with a well-written query and normalized tables than tons of conversions in PHP. If I want to display a table with only the dates from year 1952 and also display all of these other columns using a simple while loop how would I word the query? Table: old_schedules Columns: year, date, opponent, location, final I don't know what the "year" column is for, so I'll just use the date column, which you said was of the type DATE. You could do something like: <?php $sql = sprintf('SELECT * FROM old_schedules WHERE YEAR(date) = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['date']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Will these strtotime() and date() functions work on dates from the 1800's. DarkWater, I just started working with this table and haven't decided on all of the queries I will use but there will be many. It's a football schedule and it has a lot of data to tinker with. I will have it set for searching and comparisons involving different opponents and coaches and years. Just all sorts of options. That sounds like it's much easier with a well-written query and normalized tables than tons of conversions in PHP. If I want to display a table with only the dates from year 1952 and also display all of these other columns using a simple while loop how would I word the query? Table: old_schedules Columns: year, date, opponent, location, final I don't know what the "year" column is for, so I'll just use the date column, which you said was of the type DATE. You could do something like: <?php $sql = sprintf('SELECT * FROM old_schedules WHERE YEAR(date) = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['date']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } The year column is the one you would use. The date column could contain dates that span into the new year for some games and would corupt the information. The year column is just a simpl integer column with like: 1952 Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Okay, and something simple like this wouldn't work? <?php $sql = sprintf('SELECT * FROM old_schedules WHERE year = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['date']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Okay, and something simple like this wouldn't work? <?php $sql = sprintf('SELECT * FROM old_schedules WHERE year = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['date']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } It works but it doesn't format the 'date' column that is being displayed. It is still in the regular 1952-10-22 format. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 That's not what you asked me to do. Try something like: <?php $sql = sprintf('SELECT *, DATE_FORMAT(date, '%c/%e/%Y') AS newdate FROM old_schedules WHERE year = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['newdate']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } ?> Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Woops, I lied, try: <?php $sql = sprintf('SELECT *, DATE_FORMAT(date, "%c/%e/%Y") AS newdate FROM old_schedules WHERE year = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['newdate']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } ?> Had a syntax error. EDIT: You know, you really shouldn't have a year column if you already have a date column because normalized tables shouldn't repeat data. Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Woops, I lied, try: <?php $sql = sprintf('SELECT *, DATE_FORMAT(date, "%c/%e/%Y") AS newdate FROM old_schedules WHERE year = %d', 1952); $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['newdate']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } ?> EDIT: You know, you really shouldn't have a year column if you already have a date column because normalized tables shouldn't repeat data. Warning: sprintf() [function.sprintf]: Too few arguments in /var/www/website/sched.php on line 127 Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/website/sched.php on line 129 Had a syntax error. There is no practical way of doing it without it laid out like this. For instance, if you want the games from the 1974 season then all of the games would show up correctly except if they went to a bowl game that was played in 1975. That game wouldn't show up without being tagged as part of the 1974 season via the year column. Also, you might get the bowl game from the 1973 season that was played in 1974. This was the only way I could make it simple. Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 You know, I completely forgot about the DATE_FORMAT parameters messing with the sprintf(). Just use: <?php $sql ='SELECT *, DATE_FORMAT(date, "%c/%e/%Y") AS newdate FROM old_schedules WHERE year = 1952'; $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['newdate']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } ?> THAT will work. >_< Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 You know, I completely forgot about the DATE_FORMAT parameters messing with the sprintf(). Just use: <?php $sql ='SELECT *, DATE_FORMAT(date, "%c/%e/%Y") AS newdate FROM old_schedules WHERE year = 1952'; $result = mysql_query($sql) OR die(mysql_error()); echo "Year/Date/Opponent/Location/Final<br />"; while ($row = mysql_fetch_assoc($result)) { echo "{$row['year']}/{$row['newdate']}/{$row['opponent']}/{$row['location']}/{$row['final']}<br />"; } ?> THAT will work. >_< Now that got it. Fantastic. So I can just add this (at the same point in the query) to most any of my queries and it will format it for me? , DATE_FORMAT(date, "%c/%e/%Y") AS newdate Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Yes, I don't see why not. Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Yes, I don't see why not. Thanks a lot. I just don't know near enough about structuring queries. One other thing while I have you ear.... I also have a won_loss column. It's a varchar column and it contains either a W or a L in every row. I'd like to use those to create a final record row at the bottom of a particular year table. So it would look like 8-4 or 9-3 depending on how many W's and L's. It may be easy to some of you guys but it has me perplexed. :-\ Would it involve a separate query? Quote Link to comment Share on other sites More sharing options...
DarkWater Posted August 23, 2008 Share Posted August 23, 2008 Yes, I don't see why not. Thanks a lot. I just don't know near enough about structuring queries. One other thing while I have you ear.... I also have a won_loss column. It's a varchar column and it contains either a W or a L in every row. I'd like to use those to create a final record row at the bottom of a particular year table. So it would look like 8-4 or 9-3 depending on how many W's and L's. It may be easy to some of you guys but it has me perplexed. :-\ Would it involve a separate query? You should really just have it as a TINYINY column and use 1 or 0 to indicate win or loss, respectively. It would be MUCH MUCH MUCH easier to create a query that way for it. Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Yes, I don't see why not. Thanks a lot. I just don't know near enough about structuring queries. One other thing while I have you ear.... I also have a won_loss column. It's a varchar column and it contains either a W or a L in every row. I'd like to use those to create a final record row at the bottom of a particular year table. So it would look like 8-4 or 9-3 depending on how many W's and L's. It may be easy to some of you guys but it has me perplexed. :-\ Would it involve a separate query? You should really just have it as a TINYINY column and use 1 or 0 to indicate win or loss, respectively. It would be MUCH MUCH MUCH easier to create a query that way for it. I agree completely in hindsight. :-\ Know of an easy way to convert that to 1's and 0's? Quote Link to comment Share on other sites More sharing options...
unrelenting Posted August 23, 2008 Author Share Posted August 23, 2008 Never mind. I just dumped it, edited the dump and reimported it. Thanks for the idea. 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.