1

I have a table, table_a, in my MySQL db. (I am using PHP for scripting)

Now I have created another table, after realizing its necessity, called table_b. For every row in table_a I want to insert some of its values into table_b, and then affix a timestamp (DATETIME type).

This is where I am:

$query = "INSERT INTO table_b('id_a', 'type_a', 'date_a') SELECT table_a.id, table_a.type, '$datetime'";

where $datetime is a time value (php).

I'm not sure this is going to work. Could someone tell me a correct way to do this.

(Aside: I am aware I'm not using prepared statements - that's for another day)

Thanks in advance.

Improve this question
5
  • you are not sure this is going to work. have u tried it? easy way to not be unsure anymore. Commented Oct 14, 2013 at 16:44
  • 1
    It should work fine, as long as you add a FROM clause to the SELECT. Commented Oct 14, 2013 at 16:44
  • 1
    SELECT table_a.id, table_a.type, '$datetime' from tablename Commented Oct 14, 2013 at 16:47
  • 1
    Should work. Make sure your datetime variable is in right format and add the "FROM table_a" Commented Oct 14, 2013 at 16:48
  • 1
    Adding the FROM table_a worked a charm. Unfortunately I can't mark anybody's answer correct until someone writes it as an answer. Commented Oct 14, 2013 at 17:13

1 Answer 1

Reset to default
1

With thanks to all the comments, the answer is simple:

$query = "INSERT INTO table_b(id_a, type_a, date_a) SELECT table_a.id, table_a.type, '$datetime' FROM table_a";

This adds the desired values from every row of table_a into table_b.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.