[PHP & MySQL] Dates >.<

[SineCosine]

Ugh, I hate Date objects and I haven't even tried time yet =(
I tried to read up on dates but.. I'm stuck =/

Anyway, here's what I am trying to do:
01) A user posts a message
02) A PHP script creates date using gmdate("F d' y")
03) It stores the date into the MySQL database

However, the date object causes the whole thing to screw up >.<
(I know because removing the date part let everything work fine xD)
Is there a way to convert a Date to a String?

I tried:

$Date = gmDate("F d' y");

$String = $Date . ''; //Failed
echo $Date //Works, =/

I tried to store $gmDate as a varchar, failed, so I tried with the DATE datatype, failed, too.

I hate the date object =/

 

[celerisk]

What "date object" did you use?

Generally, it's enough to use the type "timestamp" for the db.
'insert into table (date) values (from_unixtime(' . time() . '))'
Or simply let the db do it:
insert into table (date) values (now())

Read it back with
select unix_timestamp(date) from table
and format it with "date()" to whatever you feel it should look like

 

[SineCosine]

I used: DATE
Lol, really.

It was in caps and said 'date', that's all.
Umm..

How about GMT?
Like, if I needed to make it GMT +8 <.<

[EDIT]
Holy ****
Worked ._.
So..

Let's say I did this to store it:

mysql_query("INSERT INTO Some_Table (Date) VALUE (NOW())");

To get back the format as: July 18' 10
I do:

$Resource = mysql_query("SELECT * FROM Some_Table");
$Row = mysql_fetch_array($Resource);

echo date("F d' y", $Row['Date']);
//I do this? o.0

 

[celerisk]

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.


The DATE type is used when you need only a date value, without a time part.

 

[SineCosine]

I really only need the date for now.
I'm afraid of time.

When I feel comfy with dates, I'll try time =x
Anyways, I tried:

mysql_query("INSERT INTO Some_Table (Date) VALUE (NOW())");

$Resource = mysql_query("SELECT * FROM Some_Table");

while ($Row = mysql_fetch_array($Resource)) {
        echo date("F d' y", $Row['Date']);
}

When I inserted.. the timestamp was: 2010-07-18 04:46:49 <-- Anyways, the time should be 05:46:49, it's 1hour slow
The result from that date() was: December 31' 69

o.0
Is unix_timestamp() a date function that takes a.. Date datatype?
And did I get a weird date because I left that function out?

 

[celerisk]

"INSERT INTO Some_Table (Date) VALUE (NOW())"

Well, the db knows what it is doing, so this part is correct.


"SELECT * FROM Some_Table"

* is not that great a choice. Even less on dates.
Have a look at what $Row['Date'] holds (without any conversion).

 

[SineCosine]

*Goes off to try it..*
I got this:

2010-07-18 04:46:49

Hmm..
Is there a problem with it? O_O

I need to use "SELECT * FROM Some_Table" because I need to loop and display all the data that the MySQL table has on a.. HTML table.

 

[celerisk]

" Is there a problem with it? "

Yes. Well, sort of. "date()" expects a timestamp. You're passing a string...
Hence the idea of having the db return a timestamp.

 

[SineCosine]

So..
date($String, $Timestamp) Right?

So I have to use: unix_timestamp to convert the string 2010-07-18 04:46:49 into a timestamp?

[EDIT]
Umm..
Wait-

It didn't work.
How do I use "unix_timestamp()"?
I did this =x

date("F d' y", unix_timestamp($Row['Date']));

W3schools didn't show me a unix_timestamp() function =/
And for some reason, I can never understand the PHP documentation >.<
It seems like it's in a format I will understand in 2lifetimes.

[EDIT=2]
Wait--

"date()" expects a timestamp. You're passing a string

I thought the function NOW() returns a timestamp? o.0
So..

I stored a timestamp in a table in a column for timestamp datatypes..
And when I call it back, it becomes a string? o.0

 

[celerisk]

That probably works, yes


Alternatively, let the db do the formating:
select date_format(Date, '%M %d %Y') as 'Date'

 

[SineCosine]

select date_format(Date, '%M %d %Y') as 'Date'

I didn't get that >.<

I'm allowed to use functions in mysql queries?
So..

mysql_query("SELECT date_format(Date, '%M, %d, %Y') FROM Some_Table");
//This works? o.0

Also..
Is there a site with PHP and MySQL functions besides w3schools that isn't a documentation meant for developers? =x
Something like w3schools (Only because I like the nice format there)

 

[celerisk]

I got the format from here:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
And from here for PHP's date():
http://www.php.net/manual/en/function.date.php


" I'm allowed to use functions in mysql queries? "

Generally speaking, I'd say "yes" to this.
With you though... probably not. Problem being that you'd have to read some documentation

But, yes, it's usually a really good idea to let the db do as much work as it can.
Stuff like formating comes basically for free. The extra time that takes is nothing compared to the time the actual selecting needs.



" This works? "

Try it out?
(If you get weird "stuff" somehow, have a look at what $Row contains: echo '<pre>', print_r($Row, true), '</pre>')

 

[SineCosine]

I didn't try it out yet because I need to select everything =x
There's more than date that needs to be echo'd out xD
But dates are the problem that I have.

So..
select date_format is a mysql query?

mysql_query("SELECT DATE_FORMAT('Some_TimeStamp', 'Some_Format')");

 

[celerisk]

" There's more than date that needs to be echo'd out "

Well, then select this,that,whatever,... and the date and get going.
What are you waiting for?

 

[SineCosine]

//Using a timestamp datatype
$TS = $Row['Date'];
$DATE = mysql_query("SELECT DATE_FORMAT('$TS', '%M %d %Y')");

Result: Resource id #7

//Using a DATE datatype
$TS = $Row['Date'];
$DATE = mysql_query("SELECT DATE_FORMAT($TS, '%M %d %Y')");

Result: --No result-- (Nothing echo'd)

//Using a DATE datatype
$TS = $Row['Date'];
$DATE = mysql_query("DATE_FORMAT($TS, '%M %d %Y')");

Result: --No result-- (Nothing echo'd)

//Using a timestamp
$TS = $Row['Date'];
$DATE = mysql_query("DATE_FORMAT($TS, '%M %d %Y')");

Result: --No result-- (Nothing echo'd)

//Using a timestamp
$TS = $Row['Date'];
$DATE = mysql_query("DATE_FORMAT('$TS', '%M %d %Y')");

Result: --No result-- (Nothing echo'd)

o.0

[EDIT]
So different from when the documentation does it =/

 

[celerisk]

...

$Resource = mysql_query("select date_format(Date, '%M %d %Y') as 'Date' from some_table");

while ($Row = mysql_fetch_assoc($Resource))
{
    echo '<pre>', print_r($Row, true), '</pre>';
}

 

[SineCosine]

Don't be mad at me =/

Array
(
    [0] => July 18 2010
    [Date] => July 18 2010
)

Questions =x

So..

$Resource = mysql_query("select date_format(Date, '%M %d %Y') as 'Date' from some_table");

'Date' does not need a dollar sign because it isn't a PHP variable but.. A column name?
What's "As 'Date'" for?

[EDIT]
Fixed a typo error, bolded

 

[celerisk]

select Date from some_table
selects whatever is in column "Date" of table "some_table", gives it some default format and returns that.

select date_format(Date, '%M %d %Y') from some_table
Selects whatever is in column "Date" of table "some_table", formated according to your wishes and returns that.


Now, every now and then you get some weird column names back when using functions in queries.
As in, if you're lucky $Row['Date'] still works, if you're less lucky you might need to use $Row["date_format(Date, '%M %d %Y')"] to get the result.
Since that's pretty much bull, you make use of a really cool feature: rename columns to something that makes sense!

select date_format(Date, '%M %d %Y') as 'Date' from some_table
Same as above, only now you're sure your column will be called "Date".

 

[SineCosine]

So if I used: As 'TestDate'
And my actual column name was 'Date'

What would happen?

$Resource = mysql_query("select date_format(Date, '%M %d %Y') as 'TestDate' from some_table");
//What happens now? o.0

//This?
$Row = mysql_fetch_array($Resource);
echo $Row['TestDate'];  //And this will give me the correct date and format, right?

 

[celerisk]

" What happens now? "

Santa comes and asks you to use $Row['TestDate'] to get your result.
See? It is that simple.


For the easily confused, it's no problem to use something like
select name as "date",email as "phonenumber",phone as "name" from table
The db doesn't particularly care