At 17:15 07/03/2005 +0000, you wrote:
JeRRy wrote:Hi, I have 23 tables, well I have more but these 23 tables are the main agenda regarding this question. In each of the table is a column titled "score" and a column titled "nickname" ... These are the two columns we need for this.well - two options spring to mind...
1) use a single table, with 23 columns instead of 2. Then sum the columns in your query.
- 1 query, no loops required.
2) have your script pull everything and add it all up.
- 22 queries, a loop for each, array storing scores & indexed by name.
Actually I disagree strongly unless your intention is to be able to read the rows yourself !
As the stated intention is to add up scores (doing the maths) in fact much more efficiently you want to have just 3 columns in a single table, "tblBlah" :
User RoundNumber RoundScore
Bob 1 6 Bob 2 2 Bob 3 3 Steve 1 5 Steve 3 1 Steve 4 6
So for all rounds, you do : SELECT SUM(RoundScore) FROM tblBlah WHERE User='Bob' GROUP BY User
And to get Bob's round 2 score : SELECT RoundScore FROM tblBlah WHERE User='Bob' AND RoundNumber=2
You can do all sorts of stuff now which doesn't involve queries which run like
SELECT COUNT(*) FROM tblBlah WHERE User='Steve' GROUP BY User And you've just found out how many rounds Steve has completed.
Or you can do SELECT RoundNumber, AVG(RoundScore) FROM tblBlah GROUP BY RoundNumber
Now you've got a list of rounds and the average score per round. And you don't have to write a gigantic query across 23 tables like SELECT tbl1.RoundScore, tbl2.RoundScore, tbl3.RoundScore, tbl4.RoundScore .... even if that was practical (you'd have to use outer joins and the result set would be huge).
Using multiple tables, or even multiple columns is deeply inefficient for this. You can set up a couple of queries to merge all the tables into one in a few minutes, it's called database refactoring and in your case would improve the manageability of your data by10x.
For this sort of table, you would create an index on each of the 3 columns to imccrease the query speed massively. A nice side effect apart from speed, is that data stored in this way takes up no space if a (new) user hasn't completed any rounds (for example ;-)
HTH Cheers - Neil
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
