The Little Guy Posted April 13, 2012 Share Posted April 13, 2012 Back in 2007 / 2008 I wrote a voting script which stores votes. The problem I have is I save the votes badly. I have 2 columns: - voteTotal (sum of all the votes) - votes (number of people who voted) Here are two values of on item: 1394 431 I want to break these up into single Items. I know I cannot go back into time and get the values, but I would like to calculate what could have been used. So for the above example, I would insert 431 rows into a table, each with a value of 1-5 (int's) all rows adding up to 1394. Any ideas of what I could do for this? Quote Link to comment https://forums.phpfreaks.com/topic/260837-break-cell-up-into-rows/ Share on other sites More sharing options...
requinix Posted April 13, 2012 Share Posted April 13, 2012 First, if 1*votes = voteTotal then all the votes are 1. If 5*votes = voteTotal then all the votes are 5. Otherwise get the average, which must be between two numbers in the 1-5 range. Call those two numbers "low" and "high". Take the fractional part of the average and * votes (and round). This is the number of "high" votes. The remainder is of "low" votes. voteTotal = 1394 votes = 431 1*431 = 431 so not ones 5*431 = 2155 so not fives 1394 / 431 ~ 3.23433 so low=3 and high=4 0.23433 * 431 = 101 so that many votes of 4 The remainder is 431-101 = 330 votes of 3 I can explain the proof if anyone wants. Quote Link to comment https://forums.phpfreaks.com/topic/260837-break-cell-up-into-rows/#findComment-1336891 Share on other sites More sharing options...
The Little Guy Posted April 15, 2012 Author Share Posted April 15, 2012 How do you decide the high? Maybe I am wrong but I don't think this can be done with pure MySQL... Quote Link to comment https://forums.phpfreaks.com/topic/260837-break-cell-up-into-rows/#findComment-1337559 Share on other sites More sharing options...
requinix Posted April 16, 2012 Share Posted April 16, 2012 It should be possible. MySQL can calculate the average and then round it up and down for the two vote numbers. You'd have to construct a loop but with a counter @variable you can do it. If the average is 3.x then it's possible to come up with a set of 3 and 4 votes that satisfies the two conditions. That's because any 1, 2, or 5 votes can be "transformed" into 3s and 4s without affecting the grand total or the number of votes. Because there's an average of 3.x, every low/high vote must have a "balancing" set of high/low votes that maintains the average. The math turns those balanced votes into 3s and 4s regardless of what the actual numbers where. The low and high values are just the two numbers on either side of the average. Say the average is 4.x. Start off with some vote close to that average, like 4. You can pick lots of 4s but the average of them will be too low. You need some higher votes, like 5, to raise it. In this case we picked the two numbers on either side of 4.x - you could pick anything really, it's just that it's easier to pick 4 and 5. Not mentioned is how this all only works if there is a possible set of votes to sum to the total and also maintain the average, but this is the case because these numbers aren't just randomly generated: there was a series of real, legal votes that resulted in them. If it weren't then all the math is out the window because it's simply not possible. Quote Link to comment https://forums.phpfreaks.com/topic/260837-break-cell-up-into-rows/#findComment-1337686 Share on other sites More sharing options...
The Little Guy Posted April 28, 2012 Author Share Posted April 28, 2012 Finally found time to implement this, it work terrific! Thanks for the help! Quote Link to comment https://forums.phpfreaks.com/topic/260837-break-cell-up-into-rows/#findComment-1341286 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.