mysql find series of records in table A, copy to table B

Question

I'm trying to write a PHP script right now that

1. uses a unique primary ID to search for a specific record in a database.
2. It then should search a table for records that match certain fields of the first record(for example, find all entries with a name and date that is the same as "record one")
3. it should then copy all entries that match into a new table.

Right now I have it only grabs one matching entry and then stops. How can I format this to copy over all entries?

$query = sprintf("SELECT name, date FROM original_table
WHERE id='%s'",
mysql_real_escape_string($id)); 
$result = mysql_query($query);

$row=mysql_fetch_array($result);


//find and copy all matching entries
$query = sprintf("INSERT into second_table(all fields)
SELECT all fields FROM original_table WHERE name='%s' AND date='%s'")

any suggestions as to what I can do to accomplish this?

Answer 1

In SQL, the query that you want is:

INSERT into second_table(all fields)
    SELECT ot.all fields
    FROM (select *
          from original_table ot
          where key = <thekey>
         ) rec join
         original_table ot
         on rec.name = ot.name and
            rec.date = ot.date

Do you want to exclude the original key? If so, include "and rec.key <> ot.key".

Answer 2

You can select and insert with a single query, for example:

INSERT INTO new_table (name, date, etc)
SELECT name, date, etc FROM old_table
WHERE name='%s' AND date='%s'

Explanation:

Line 1: define the columns you want to insert into the new table
Line 2: select the data from the old table
Line 3: specify what you want to copy with a WHERE or ON statement

Potentially you could also dynamically create new_table but in this example it already exists.