php do something for every record in the database

Question

Hey everyone

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

Answer 1

Working with database in the loop isn.t a good practice. Iy is good to select all table data into array by one query and work with this array in future.

Answer 2

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... 

Answer 3

Do you have access by other means to MyQSL tables? Like with MySQL Administrator or another tool, even by command line?

This because would be much more time, resources and eveything else, saving doing that directly in the database, through a query or a database function.

I would do that this way.

But for sake of clarity, unless you are storing the videos themselves inside database tables, 1000 records are not a problem. Maybe 10.000 would be.

General tip: do only what you need to do.

If you just need operate upon data, do this on database. If you need just check one field in one table, SELECT your_field FROM your_table, instead of SELECT * FROM your_table.