Okay, you'll have to forgive a lot of ignorance here, but I'm venturing further into the world of mySQL than I have ever done before, and quite frankly, don't know what the heck I'm talking about.
What I want to do, is to allow mySQL to sort values on the fly using a calculation from two other fields. For example, users submit links. Each link can be voted on. In the link table, there would be a field housing both the total number of votes, and the cumulative score of those votes, with the result being a division of the latter by the former. ie; link1 has been voted on 10 times, and has a total score of 100, giving it an actual score of perfect 10.
Now, ideally, I would be able to do some sort of select statement that would order the results of the query so that they were ordered descendingly by that calculated result. Quite frankly, I don't know if this is something that can be done at all, and certainly not how to do it.
Alternative methods I'm considering is to add a 'result' field to the table, and have that update every time a vote is submitted, but this just seems amateurish, and I'm trying to get away from that, for what it's worth. The other idea I had, is to pull the result of the query into a multidimensional array that also has the calculated score, then display the contents of the array in descending order, sorted by the actual score. I also don't know how to do this though.
If anyone knows, I'd really appreciate a helping hand on this. I'm not doing it for any big project or anything, just trying to further tackle areas that I'm not familiar with, and learn through usage. So far, it's been slow going, but I'm trying.
What's the best method of accomplishing the desired result? What's the most efficient method? Which method would you use, and why? How big a moron am I for not knowing how to do this already?
All input is appreciated, but input with example code is appreciated more
. Thanks in advance.
What I want to do, is to allow mySQL to sort values on the fly using a calculation from two other fields. For example, users submit links. Each link can be voted on. In the link table, there would be a field housing both the total number of votes, and the cumulative score of those votes, with the result being a division of the latter by the former. ie; link1 has been voted on 10 times, and has a total score of 100, giving it an actual score of perfect 10.
Now, ideally, I would be able to do some sort of select statement that would order the results of the query so that they were ordered descendingly by that calculated result. Quite frankly, I don't know if this is something that can be done at all, and certainly not how to do it.
Alternative methods I'm considering is to add a 'result' field to the table, and have that update every time a vote is submitted, but this just seems amateurish, and I'm trying to get away from that, for what it's worth. The other idea I had, is to pull the result of the query into a multidimensional array that also has the calculated score, then display the contents of the array in descending order, sorted by the actual score. I also don't know how to do this though.
If anyone knows, I'd really appreciate a helping hand on this. I'm not doing it for any big project or anything, just trying to further tackle areas that I'm not familiar with, and learn through usage. So far, it's been slow going, but I'm trying.
What's the best method of accomplishing the desired result? What's the most efficient method? Which method would you use, and why? How big a moron am I for not knowing how to do this already?
All input is appreciated, but input with example code is appreciated more
. Thanks in advance.