开发者

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


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


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


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

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

I would do that this way.

But for the 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 just what you need to do.

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜