Okay, so lets say that we have 4 columns and 3 rows of data.
|user_id|pick_1|pick_2|pick_3|
-------------------------------
|fred |C++ |java | php |
------------------------------
|eric |java |C++ | php |
------------------------------
|sam | C++ | php | java |
------------------------------
So right now, users are entering their favorite languages. The first pick(pick_1) would be the favorite programming language and the second pick (pick_2) would be the 2nd favorite programming language and etc.
How can I organize this in a way so that I can give a point value according to what columns the programming languages are. So maybe pick_1 can give 3 points, pick_2 can give 2 points and pick_3 can give 1 point.
So when you tally up the scores, C++ will have 8 points, java will have 6 points, and php will have 4 points.
That way I can give an overall ranking of what tends to be the more favorable programming language. Like so
|rank|language|points|
----------------------
| 1 | C++ | 8 |
----------------------
| 2 | java | 6 |
----------------------
| 3 | php | 4 |
----------------------
It doesn't even need to have a point system, I just couldn't think of another way to rank the languages on a scale of liked to un-liked. So if there's another way to yield the same results than please let me know. Otherwise how would I be able to do this. Preferably in just MySql. I am currently using PHP.
Thank you for reading.
for a SQL only solution, I would normalize your structure, and put the picks in a different table:
users: user_id; user_name
picks: pick_id; user_id; language; points;
then you would have your data in 2 tables:
| user_id | user_name |
-----------------------
| 1 | Fred |
-----------------------
| 2 | Eric |
-----------------------
| 3 | Sam |
-----------------------
| pick_id | user_id | language | points |
---------------------------------------------
| 1 | 1 | C++ | 1 |
---------------------------------------------
| 2 | 1 | Java | 2 |
---------------------------------------------
| 3 | 1 | php | 3 |
---------------------------------------------
| 4 | 2 | Java | 1 |
---------------------------------------------
| 5 | 2 | C++ | 2 |
---------------------------------------------
| 6 | 2 | php | 3 |
---------------------------------------------
| 7 | 3 | C++ | 1 |
---------------------------------------------
| 8 | 3 | Java | 2 |
---------------------------------------------
| 9 | 3 | php | 3 |
---------------------------------------------
And then use the following query to fetch the desired result:
SELECT language, SUM(points) FROM users JOIN picks ON users.user_id=picks.user_id GROUP BY language
As seen in this fiddle
This way it's also easy to add constraints so people can not vote for a language more then once, or give the same amount of votes to 2 different languages.
You need a simpler structure
User_ID | Pick | Points
Fred c++ 3
Fred php 2
Fred java 1
This way you can do a simple sum(points) group by pick