Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Iterating through a table (database) ??

Quote Reply
Iterating through a table (database) ??
Alex,

What would be the best way to iterate through the database (or a table?)

Select * from Table_Name offset x limit n

Or would it be better to

Select ID from Table_name offset x limit n

Then use ->get to get each record in turn, rather than fetchrow?

Which would have the least system impact, if speed wasn't an issue?

Specifically, I need to iterate through the Rate_VoteTrack table, and re-generate the Count and Average (rating), then update the Links table with them.

Actually, I guess it would be better to iterate through the Links table, and if no Rate_VoteTrack entry existed, set Votes and Rating to '0'. If Rate_VoteTrack had a timestamp field, then any record not updated prior to the recalc run could be deleted as "orphan" ratings.

Or... would it be better to create a table relation from Links->Rate_VotesTrack

While I'm musing on this, an esoteric type question:

Rate_UserTrack is related to Rate_VoteTrack in that each entry in the Rate_UserTrack table is one partial entry in the Rate_VotesTrack. Rate_VotesTrack is actually a fully dependent table to Rate_UserTrack.

If an entry in Rate_UserTrack is deleted or altered, a record in Rate_VotesTrack needs to be altered - ie: a column value needs to be decremented (delete/change) and a column value needs to be incremented (change), as well as 2 fields recalculated.

Is it better to do this all in the code, outside of Links->SQL or, should the tables be related, and an event triggered to handle this?

I may not be explaining this right, but LinksSQL->SQL maintains table relations. Does it cover this sort of complex relationship?

Add/edit/delete->Rate_UserTrack needs to Add/update a Rate_VoteTrack entry which updates Links->votes/Ratings.

Deleting a Link record empties out CatLinks, etc, but should also empty out Rate_UserTrack and Rate_VoteTrack.

Deleting a User should do the same... otherwise there are bound to be internal problems at some point.


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Subject Author Views Date
Thread Iterating through a table (database) ?? pugdog 1724 Feb 22, 2002, 12:31 AM
Post Re: [pugdog] Iterating through a table (database) ??
Alex 1631 Feb 25, 2002, 12:00 PM