Simple but frustrating datetime php mysql prob

[newb40]

Hi I'm a newbie having trouble entering a date into my mysql database

When I write:

$query = "INSERT INTO test3 (play_date) VALUES ('2007-10-10 10:10:10')";

it works perfectly...

however when I set
$query = "INSERT INTO test3 (play_date) VALUES ('$play_date')";

and
$play_date = ('2007-10-10 10:10:10');
or
$play_date = date('2007-10-10 10:10:10');
(or it seems anything else I do)

it doesn't work.....and instead enters 0000-00-00 00:00:00

when I say echo $play_date it looks like the perfect format on screen...

I've been working on this for days...and I'm pulling my hair out...the solution has to be SO simple but I just can't get it right...can someone help me out?

Helpful suggestions would be greatly appreciated.

[Jake Arkinstall]

try this:

$play_date = "2007-10-10 10:10:10"; 

[kigoobe]

because probably, you are doing

$play_date = ('2007-10-10 10:10:10');

and it's becoming

$query = "INSERT INTO test3 (play_date) VALUES ('('2007-10-10 10:10:10')')";

alternatively, you could also use mysql's NOW() function to insert current datetime ...

$query = "INSERT INTO test3 SET play_date = NOW()";

EDIT arkinstall told the same thing, before me.

[r937]

alternatively, you could also use mysql's NOW() function to insert current datetime ...

[Raju Gautam]

Hello r937, is there any difference between these two coz i saw the output is same?

$sql = "select CURRENT_TIMESTAMP";
$sql = "select now()"; 

I mean any execution time... or something differ?

[r937]

they are the same, but CURRENT_TIMESTAMP is standard SQL

[Raju Gautam]

they are the same, but CURRENT_TIMESTAMP is standard SQL

Thank you r937, usually i use CURRENT_TIMESTAMP because it works in PgSQL too. That's why i was curious to work and i did not try to look for the performance differences if there.

[newb40]

Hi...

I just tried:

$query = "INSERT INTO test2 (play_date) VALUES ('$play_date')";

where

$play_date = "2007-10-10 10:10:10";

and it still didn't work...it entered 0000-00-00 00:00:00 again

but
$query = "INSERT INTO test2 (play_date) VALUES ('2007-10-10 10:10:10')";

worked fine...


Still frustrated...eventually I'd like to be entering...$Thissunday into mysql

$date1=strtotime("now");
$Thissunday = strtotime("Sunday", $date1);

but I can't even enter a simple variable...any other thoughts?

[kigoobe]

What about

$query = "INSERT INTO test3 SET play_date = NOW()"; 

? Are you sure you don't wan't to try mysql functions?

[r937]

mysql function for current timestamp is good, but he needs more

he also wants to do this:[quote]eventually I'd like to be entering...$Thissunday into mysql[/code]

[Jake Arkinstall]

lol - he just sent me a PM with this post 8's question, and I gave him the solution.

date("Y-m-d H:i:s", strtotime("Sunday")); 

[kigoobe]

Off Topic:

are you really 15 arkinstall ? then you are doing great at such a young age ... till now I thought that Brandon must be the youngest here ...

[Jake Arkinstall]

Off Topic:

yup. Who's Brandon?

[kigoobe]

Off Topic:

another young and talented guy from SP. userID = devbanana

[newb40]

Tried sending

$query = "INSERT INTO test2(play_date) VALUES ('$play_date')";
where
$date1=strtotime("now");
$play_date= date("Y-m-d H:i:s", strtotime("Sunday", $date1));

didn't work, just enters 0000-00-00 00:00:00 into database

so echo'd $query to get

INSERT INTO test2(play_date) VALUES ('$play_date') showing up on the screen,

Tried to parse variable by changing $query to:

$query = "INSERT INTO test2(play_date) VALUES ('".$play_date."')";

and echo'd $query to get

INSERT INTO test2(play_date) VALUES ('$play_date')

then changed $query to

$query = "INSERT INTO test2(play_date) VALUES (".$play_date.")";

and finally got the mysql error

An error occurred on line 34:
Query: INSERT INTO test2(play_date) VALUES ($play_date)
MySQL Error: Unknown column '$play_date' in 'field list'


$query = "INSERT INTO test2(play_date) VALUES ('2008-10-10 10:10:10')";

works perfectly though...something is wrong with submitting my variable but I can't get it right...

Help! I have no hair left!!

[Jake Arkinstall]

what if you try:

$query = sprintf("INSERT INTO test2(play_date) VALUES ('%s')", $play_date);

[newb40]

now I get a simple parse error

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING on line 32

[wonshikee]

and echo'd $query to get

INSERT INTO test2(play_date) VALUES ('$play_date')
:

This would imply that you are using single quotes, not double.

$query = "INSERT INTO test2(play_date) VALUES (".$play_date.")"; 

Should be

$query = "INSERT INTO test2 (play_date) VALUES ('$play_date')"; 

exactly as is.

[Jake Arkinstall]

newb, did you try the updated version? I left in the double-quotes by mistake before, so I updated it.

[newb40]

When I use:

$query = "INSERT INTO test2 (play_date) VALUES ('".$play_date."')";

then when I echo $query I get:

INSERT INTO test2 (play_date) VALUES ('$play_date')

and 0000-00-00 00:00:00 goes into the database

[Jake Arkinstall]

but what about:

$query = sprintf("INSERT INTO test2(play_date) VALUES ('%s')", $play_date); 

[newb40]

Tried

$query = sprintf("INSERT INTO test2(play_date) VALUES ('%s')", $play_date);

echo $query still gives

INSERT INTO test2(play_date) VALUES ('$play_date')

and 0000-00-00 00:00:00 into database

[newb40]

wow...shouldn't be this difficult...

feel like quitting

[Jake Arkinstall]

What PHP setup are you using?

[newb40]

I'm using PHP version 5.1.6

through HostMonster...