php do something for every record in the database

I have two tables in the database(videos and viewData) .

Im trying to build a script that runs for each record in the "videos" table and does something using the "videoID" field for that specific entry in the "videos" table. The does something part would be dumping some data into the viewData table.

Would I need to store all the records in an array before calling the loop? An example of a loop like this would be really helpful. Also in a way that could be potentially scalable that wouldn't hurt the server too much if there were a 1000+ records in the "videos" table.

Thanks, Dave

Solution

Try to avoid the loop at all costs. Think set based processing, which means handle the entire set of rows within one SQL command.

I'm not entirely sure what you are attempting to do, as your question is a little vague. however, here are two possibly ways to handle what you are trying to do using set based thinking.

You can do a JOIN in an UPDATE, essentially selecting from the parent table and UPDATEing the child table for all rows in a single UPDATE command.

UPDATE c
    SET Col1=p.Col1
    FROM ParentTable           p
        INNER JOIN ChildTable  c On p.ParentID=c.ParentID
    WHERE ...

you can also INSERT based on a SELECT, so you would create one row from each row returned in the SELECT, like:

INSERT INTO ChildTable
        (Col1, Col2, Col3, Col4)
    SELECT
        p.ColA, p.ColB, 'constant value', p.ColC-p.ColD
        FROM ParentTable p
        WHERE...