Hi

i have a table named rating with, by default, 1 column (bandname). everytime a user registers on my site, a new column on the ratings table is created in the name of the user. Likewise any time a band is created, a new row appears in the table named $bandname

I now have a table with a field matching every user to every bandname, in this field users can place integers between 1 and 10 which will assign their rating to any band in the table they wish to rate.

What im aiming for is a query which will will loop through the number of columns(users) in the table next to each bandname and create an overall value for each row. Now i need the query to send back to me the highest ranking 100 bands.

I can do this with php obviously but im just starting to appreciate that im needlessly filtering alot info with php that could just as well be filtered with mysql, hence saving time.

Does anybody have any idea how id go about creating such a query, or if it is even possible with mysql?

Thanks is advance

No!!!

It's not possible, at least easily, to do what you want using SQL. You could come up with a hack to do it, probably using a cursor, and procedural SQL code ... but there's a saying that if you can't write a simple SQL query to get what you need, something is wrong with the query or the schema.

In your case, it's with the schema. Don't go adding columns on the fly like that!! Seriously, there are plenty of reasons why this is bad. You can run into duplicate or illegal column names, you're limited to a certain number of columns per table ( in the thousands, though ), you can only store a certain number of bytes per row ... every new column you add slows down the responsiveness of the table.

And, most basic of all the reasons, it's just not relational design. Instead, you could use a table with a column called bandname, another for the name fo the user, and a third for the rating. Add a row, instead of a column, to store more data. It sounds like a meaningless difference, but this way is what databases are good at, so can do quickly, easily, it gives you options to enforce referential integrity, and your query becomes amazingly easy.

If you do it this way, you can get what you want with a SQL query like Select Top 100 Sum(Rating), Avg(Rating) From bandname Where band="xxx" Group By band Order By Sum(Rating) Desc. That would be the SQL Server syntax; I think in MySQL you use a where clause instead of a top x clause.

Besides being simpler, this is also going to do the job in less time using less memory on your server. The database engine is highly optimized for this kind of stuff, and does a much better job than PHP loops.

PS - If you REALLY need to add ( or average, or use some other formula ) certain columns instead of rows, ie you can't or won't restructure the data, you can do a select * query, and then use two nested for loops in PHP; the first would iterate rows the way you're used to, and the second loop, inside that, would iterate columns inside the row. I could show you the C# ASP code for that, but I don't think that would translate to PHP. But it would still be advisable to restructure the data. And you could do that with a simple select into query.