Copy field from one table field to another table field using key-convert date

[tyler_durden]

This is my first real jump into PHP, I created a small script a few years ago but have not touched it since (or any other programming for that matter), so I'm not sure how to start this.

 

I need a script that I can run once a day through cron and take the date from one table/filed and insert it into a different table/field, converting the human readable date to a Unix date.

 

Table Name: Ads

Field: endtime_value (human readable date)

 

to

 

Table Name: Node

Field: auto_expire (Converted to Unix time)

 

Both use a field named "nid" as the key field, so the fields should match each nid field from one table to the next.  Following a tutorial I have been able to insert into a field certain data, but I don't know how to do it so the nid's match and how to convert the human readable date to Unix time.  Thanks in advance!.

 

[AbraCadaver]

Your Node nid needs to not be autoincrement and Node auto_expire needs to be INT, VARCHAR or something that can take a UNIX timestamp:

 

INSERT INTO Node (nid, auto_expire) SELECT Ads.nid, UNIX_TIMESTAMP(Ads.endtime_value) FROM Ads

 

[tyler_durden]

Thanks for the help so far.  I should have prefaced with that both tables have a field with the same "nid" key already created, so I just need to "match them up", not sure what technical term is used for that.  There are no new records creation, just editing what already has been created.

 

Also, the "auto_expire" field is already set for Unix timestamps as other records have a Unix timestamp in there.  I just need a special script to catch some "odd ducks".

 

 

Your Node nid needs to not be autoincrement and Node auto_expire needs to be INT, VARCHAR or something that can take a UNIX timestamp:

 

INSERT INTO Node (nid, auto_expire) SELECT Ads.nid, UNIX_TIMESTAMP(Ads.endtime_value) FROM Ads

 

[AbraCadaver]

Not tested and just a shot in the dark:

 

UPDATE Node SET auto_expire = (SELECT Ads.nid, UNIX_TIMESTAMP(Ads.endtime_value) FROM Ads) WHERE Node.nid = Ads.nid 

[tyler_durden]

Great,

Thanks for tip, now I at least have a syntax to start with.  Won't get to it until the weekend since it's the wife's bday tomorrow. 

[tyler_durden]

Sort of good news, I was able to figure out how to have the original date created in Unixtime so I do not have to convert it now, but I have spent the lat 1.5 hours unsuccessfully trying to get my "Insert" command to work. I have renamed my fields, and here are the new names.

 

Table: content_type_ads

- field_expire_value

- nid

 

WANT TO COPY field_expire_value from above table field to "expire" field in table below where the nid is equal in each record.

 

Table: auto_expire

- expire

- nid

 

I am completely lost...  here is a sample code I have been testing with below.  I keep getting the error, "#1054 - Unknown column 'content_type_ads.nid' in 'where clause'".

 

UPDATE auto_expire SET expire = (SELECT content_type_ads.nid, auto_expire.nid, content_type_ads.field_expire_value FROM content_type_ads) WHERE content_type_ads.nid = auto_expire.nid

[DavidAM]

 

UPDATE auto_expire 
  SET expire = (SELECT content_type_ads.nid, auto_expire.nid, content_type_ads.field_expire_value 
    FROM content_type_ads) 
  WHERE content_type_ads.nid = auto_expire.nid

 

This is not going to work. 1) The SELECT is returning multiple values (which cannot be assigned to a single value); 2) The WHERE phrase is referencing a table that is not included in the UPDATE (the subquery does not count, although the WHERE could be moved into the subquery).

 

It could be done using a JOIN:

UPDATE auto_expire 
  JOIN content_type_ads ON content_type_ads.nid = auto_expire.nid
  SET auto_expire.expire = content_type_ads.field_expire_value

[tyler_durden]

Thanks for the clarification DavidAM.  Your code worked!