Jump to content

[SOLVED] Date conversion help


unrelenting

Recommended Posts

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.

Link to comment
Share on other sites

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? >_<

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 />";
}

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 />";
}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 />";
}
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. >_<

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.