[RESOLVED] MySQL Date

dmcglone

Hi all,

I'm wondering if it is possible to alter data from a MySQL's date field with PHP.

I created a date field in mysql and without any alterations it displays the date in the format 2010-2-18 and I would like to change this to 2-18-2010. I was wondering if this could be achieved with PHP.

I also want to be able to extract the month out of this date, so I can put it in a variable and compare it to the current month, so I can only display things for that month. would this be possible using the mysql date?

Or should I just make 3 fields Month, Day, and Year and put all 3 into separate variables and then compare the month variable from the db to a variable that holds the current month.

Pikachu2000

You can try

SELECT DATE_FORMAT(stored_date, [parameters]) AS date

bradgrafelman

As Pikachu2000 points out, MySQL has a date/time function just like PHP does.

For more information the syntax for the format string, see this section in the MySQL manual.

johanafm

dmcglone;10943167 wrote:
I also want to be able to extract the month out of this date, so I can put it in a variable and compare it to the current month, so I can only display things for that month. would this be possible using the mysql date?

Function reference for MySQL 5.1.

SELECT MONTH(datefield) as month_num, MONTHNAME(datefield) as month_name

dmcglone;10943167 wrote:
Or should I just make 3 fields Month, Day, and Year and put all 3 into separate variables and then compare the month variable from the db to a variable that holds the current month.

One of the first normal forms used to require that you had no compound values, but I believe I read somewhere that it was removed as a requirement since there was no clear definition of what is compound/atomic. In my opinion, it depends on your needs, and I feel it is possible to define what is compound/atomic in each specific case. In other words, it may still be a good idea to keep this requirement in practice, even though it has no place in a formal and generic definition, since it becomes based on context.
E.g. a stree adress consists of a street name and a house number. As long as you never need to deal with anything but this whole entity, I'd consider this atomic and keep it that way. However, if you need to lookup neighbours (same street, house number is 2 greater or 2 less), then I'd consider the former street address compound and split it into street and house.
While you could consider a date compound when you need to operate on months only, I suspect you will still need to do date comparisons, which becomes cumbersome if you need to deal with year, month and day as separate entities. The same goes for operations such as date + interval 20 days, which in addition to being harder to handle might need to update 1, 2 or 3 fields. So I'd say there are good reasons for considering dates as atomic values.
And since there are good support for date and time functions, which also are efficient afaik (compar this to the streetname/housenumber example where storing them as an entity would require string comparison or regular expressions), there is little need to store the parts.
However, if you do not ever need to do full date comparisons etc, then you could very well consider date to be the compound of year, month and day and store these separately instead.
One thing is certain though: you do not want to store both the date and the month. That breaks normalization for sure and introduces the risk of storing inconsistent data.

dmcglone

bradgrafelman;10943257 wrote:
As Pikachu2000 points out, MySQL has a date/time function just like PHP does.

For more information the syntax for the format string, see this section in the MySQL manual.

Yeah, I could do this. I was hoping to find out if there is a solution using PHP, because I'm still learning PHP and I don't know if I'm ready to mix too much MySQL into my code, except for simple query commands.

I looked at the date() function in php's manual but it didn't seem to do what I was trying to do.

dmcglone

johanafm;10943277 wrote:
Function reference for MySQL 5.1.
SELECT MONTH(datefield) as month_num, MONTHNAME(datefield) as month_name
One of the first normal forms used to require that you had no compound values, but I believe I read somewhere that it was removed as a requirement since there was no clear definition of what is compound/atomic. In my opinion, it depends on your needs, and I feel it is possible to define what is compound/atomic in each specific case. In other words, it may still be a good idea to keep this requirement in practice, even though it has no place in a formal and generic definition, since it becomes based on context.
E.g. a stree adress consists of a street name and a house number. As long as you never need to deal with anything but this whole entity, I'd consider this atomic and keep it that way. However, if you need to lookup neighbours (same street, house number is 2 greater or 2 less), then I'd consider the former street address compound and split it into street and house.
While you could consider a date compound when you need to operate on months only, I suspect you will still need to do date comparisons, which becomes cumbersome if you need to deal with year, month and day as separate entities. The same goes for operations such as date + interval 20 days, which in addition to being harder to handle might need to update 1, 2 or 3 fields. So I'd say there are good reasons for considering dates as atomic values.
And since there are good support for date and time functions, which also are efficient afaik (compar this to the streetname/housenumber example where storing them as an entity would require string comparison or regular expressions), there is little need to store the parts.
However, if you do not ever need to do full date comparisons etc, then you could very well consider date to be the compound of year, month and day and store these separately instead.
One thing is certain though: you do not want to store both the date and the month. That breaks normalization for sure and introduces the risk of storing inconsistent data.

Basically all I was trying to accomplish was comparing the month in the database to the current month so when the month rolls around, if it matches the month in the database, it says "here I am" I can accomplish this using 3 separate fields for Month, Day and Year and using php's date() but this time around I am trying to learn how to work with a date field in mysql.

If manipulating the data from a mysql date field would require learning more SQL, then I should just skip over trying to learn this, because I'm afraid if I start mixing too much SQL into my PHP I'm going to get confused.

What would you suggest?

bradgrafelman

The first suggestion I have is that there's no sense in learning something the wrong way - that's just silly.

Second, I would store date components together in a DATE column as it makes it easier to work with them.

Third, it's not like we're telling you that you have to learn a whole bunch of SQL to do what you want... You just have to lookup two functions - MONTH() and CURDATE():

SELECT col1, col2 FROM muTable WHERE MONTH(dateCol) = MONTH(CURDATE())

note of course that the above query examines only the month components, not the month and year.

If you instead wanted to loop through all rows in the table while identifying the ones that match the present month, then you could accomplish that with a mix of SQL and PHP or solely PHP - it all depends on what you're trying to do.

dmcglone

bradgrafelman;10943308 wrote:
The first suggestion I have is that there's no sense in learning something the wrong way - that's just silly.

Second, I would store date components together in a DATE column as it makes it easier to work with them.

Third, it's not like we're telling you that you have to learn a whole bunch of SQL to do what you want... You just have to lookup two functions - MONTH() and CURDATE():
SELECT col1, col2 FROM muTable WHERE MONTH(dateCol) = MONTH(CURDATE())
note of course that the above query examines only the month components, not the month and year.

If you instead wanted to loop through all rows in the table while identifying the ones that match the present month, then you could accomplish that with a mix of SQL and PHP or solely PHP - it all depends on what you're trying to do.

You're absolutely right. I guess I'm just afraid, because I'm still learning. Sometimes I feel like my head is about to explode.

What frightens me the most is writing a bunch of code only to find out it doesn't work and it gets me all confused. I can say though that all the code I've written has worked, but not without having a few errors or typos that I've been able to find, sometimes quickly, sometimes not for days.

I guess I just need that little assurance to build my confidence more.

Pikachu2000

It's always easier to learn something right the first time than it is to learn it wrong, unlearn the wrong stuff, then relearn it correctly. It's also far less time consuming.

igorek

you need to know what you write. just like english it makes sence to you, what you wrote, makes sence to the server either one mysql server or php server side software. you need to start thinking like it does to get what you want that is the hardest part is to turn into "it".

p.s. pikachu good joke in ur signature

dmcglone

bradgrafelman;10943308 wrote:

SELECT col1, col2 FROM muTable WHERE MONTH(dateCol) = MONTH(CURDATE())

I tried this query and it worked without a hitch. It was so simple, I don't even know why I was worried.

Now all I'd like to figure out is how to work with this field to display the date in different formats. According to the MySQL manual, judging from the example, %m.%d.%Y would display the date in month-day-year format so I assume this is what I need:

GET_FORMAT(DATE,'USA') 	'%m.%d.%Y'

So I tried adding it to my query, and it doesn't work. I'm wondering if I even did it correctly. Here is my sample query:

$query_string = "SELECT birthday_id, name, birthdate FROM birthdays WHERE
 MONTH(birthdate) = MONTH(CURDATE()) && DATE_FORMAT('birthdate',GET_FORMAT(DATE,'USA'))";

I tried the query several different ways, and didn't get results.

I got to head out to work, I'll experiment with it some more when I get back.

Pikachu2000

dmcglone;10943383 wrote:
I tried this query and it worked without a hitch. It was so simple, I don't even know why I was worried.

Now all I'd like to figure out is how to work with this field to display the date in different formats. According to the MySQL manual, judging from the example, %m.%d.%Y would display the date in month-day-year format so I assume this is what I need:
GET_FORMAT(DATE,'USA') 	'%m.%d.%Y'
So I tried adding it to my query, and it doesn't work. I'm wondering if I even did it correctly. Here is my sample query:
$query_string = "SELECT birthday_id, name, birthdate FROM birthdays WHERE
 MONTH(birthdate) = MONTH(CURDATE()) && DATE_FORMAT('birthdate',GET_FORMAT(DATE,'USA'))";
I tried the query several different ways, and didn't get results.

I got to head out to work, I'll experiment with it some more when I get back.

Assuming you're using mysqli, try this, but if you cut and paste it, you'll have to clean out the spaces I used to hold the formatting so it would display here without having to scroll side-to-side:

//DEFINE THE QUERY
$query_string = "SELECT birthday_id, name, DATE_FORMAT(birthdate, %c-%e-%Y) 
     AS fomatted_date FROM birthdays WHERE MONTH(birthdate) = MONTH(CURDATE())";

//EXECUTE THE QUERY
$result = mysqli_query($db_connect, $query_string) or die( mysqli_error($db_connect) );

//CREATE AN ASSOCIATIVE ARRAY OF THE RESULTS AND DISPLAY
while( $result_array = mysqli_fetch_assoc($result) 
    {
    echo( $result_array['name'] . "has a birthday this month on: " . 
    $result_array['formatted_date'] . "<br>" );
}

Pikachu2000

It was too late to edit the above post I made to correct this error I made:

DATE_FORMAT(birthdate, %c-%e-%Y) should have single quotes enclosing the parameters as such:

DATE_FORMAT(birthdate, '%c-%e-%Y')

bradgrafelman

Pikachu2000 wrote:
you'll have to clean out the spaces I used to hold the formatting

Why is that? I often have SQL queries spanning multiple lines just to keep them readable.

Pikachu2000

bradgrafelman;10943402 wrote:
Why is that? I often have SQL queries spanning multiple lines just to keep them readable.

Sometimes when I paste a chunk of code into Zend, it craters if there are extra spaces in it. I can't explain it, but it's frustrating when it happens. I've just gotten into the habit of getting rid of unnecessary whitespace and letting Zend wrap the lines. It only seems to happen with spaces, not tabs though. Might be something to to with Mac OS X.

dmcglone

Hi Guys, Just got home a little while ago.

Ok I had to make a couple changes to your original query. I don't know if they were intentional or just by mistake, but here's the query I ended up using:

$query_string = "SELECT birthday_id, name,
DATE_FORMAT(birthdate, '%c-%e-%Y') AS birthdate 
FROM birthdays WHERE MONTH(birthdate) = MONTH(CURDATE())";
$result = $this->dbManager->DbGetAll($query_string);
return $result;

the single quotes were missing from the original query and I changed "formated_date" to "birthdate".

I've learned quite a bit from this thread. Not only with code, but also confidence. I was able to fix the original query. This pointed out to me, that I have actually learned, even though at times I feel so confused. It also boosted my confidence and I also found out that learning MySQL while I learn PHP isn't all that hard and I shouldn't let it intimidate me.

I appreciate everyone for giving me advice and showing me how things are or can be done and because of everyone here, someday hopefully I can help others.

Much appreciated guys!

dmcglone

Pikachu2000;10943415 wrote:
Sometimes when I paste a chunk of code into Zend, it craters if there are extra spaces in it. I can't explain it, but it's frustrating when it happens. I've just gotten into the habit of getting rid of unnecessary whitespace and letting Zend wrap the lines. It only seems to happen with spaces, not tabs though. Might be something to to with Mac OS X.

I try not to paste code that is presented to me, because I feel that typing it makes me think about it, analyze it and remember it better.

If I would copy and paste code, It most likely would have been forgotten. It's like saying "hey, easy way out, lets forget about it and move on"