php mysql datetime

Question

I have two tables in MySQL, and each table has its own datetime field. I want to copy the datetime of table A to overwrite the datetime of table B. I use PHP.

$result = mysql_query("select * 
                         from A 
                        where id = $key");
$row = mysql_fetch_array($result);
print $row[2]."\n"; // $row[2] is the datetime field
mysql_query("update B 
                set date_cal = $row[2]  
              where id = $key") // try to overwrite datetime in table B

$row[2] has the string representation of datetime field.

But the overwrite operation does not take effect. How to do to overwrite datetime field of table B using php?

If I insist using $row[2] to assign the new datetime field rather running mysql_query again, how to do?

is it failing silently or throwing an error? If so, what is the error? — dnagirl
– dnagirl, Commented Jan 20, 2011 at 17:21
Just a tip, you can do this directly in one sql query: UPDATE b SET date_cal = (SELECT date_cal FROM a WHERE id = $key) WHERE id = $key. — alexn
– alexn, Commented Jan 20, 2011 at 17:23
@OMG Ponies: It's called 'variable interpolation'. "WHERE ID = $key" is valid, and PHP will replace $key with its value. — gen_Eric
– gen_Eric, Commented Jan 20, 2011 at 17:30
failing silently. No error throwing. The answers work. But if I need to use $row[2] to assign the datetime field, what should I do to make it correct? — chnet
– chnet, Commented Jan 20, 2011 at 21:23

Sam Dufel · Accepted Answer · 2011-01-20 17:21:37Z

2

I believe you'll need to wrap the date in quotes in your update query.

mysql_query("update B set date_cal=$row[2] where id=$key")
Should be
mysql_query("update B set date_cal='$row[2]' where id=$key")

On another note, I'd suggest that you don't access your fields by index when you're doing a SELECT * query. If you add another field to your database, $row[2] could end up referring to something else. If you instead call $row = mysql_fetch_assoc($result);, you can refer to it by field name, eg
mysql_query("update B set date_cal='{$row['date_field]}' where id=$key")

answered Jan 20, 2011 at 17:21

Sam Dufel

17.6k3 gold badges51 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

Good point, depends on the data type. If INT, doesn't need single quotes.

chnet Over a year ago

It works. But if I insist using $row[2] to assign the datetime field and do not use select query again, how can I do to make it correct?

Sam Dufel Over a year ago

If you don't plan on changing the database schema, then what you have is ok. The problem is when you forget and change the database 6 months later, then wonder why your website is broken.

Sarfraz · Accepted Answer · 2011-01-20 17:30:14Z

2

If I understand that correctly, you can do the same from within your query:

update B 
   set date_cal = (select date_cal from A where id = {$key})
where id = $key

answered Jan 20, 2011 at 17:30

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

1 Comment

chnet Over a year ago

It works. But If I need to use $row[2] to assign the datetime field, what should I do to make it correct?

Collectives™ on Stack Overflow

php mysql datetime

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related