mysql get result of most frequent value of every post

Vote count:

0

I found some similar questions with good answers but i couldnt figure out how to apply this to my specific case. I have a site where users can rate there favorite post from 1-6. Every number is a different value.

Now i need to know the most frequently votes for every single post. So i need to count every post id and than the most frequent values of every post id.

After that i wanne update every result in another table. (dont know how to figure this out right now im not that good with mysql yet).

this are the two tables where i need to know how often every post exist in post_id and what is the most frequently voting number of every single post.

just an example

voting | post_id               
---------------
  3    |   12
  1    |   6
  4    |   13
  2    |   5
  6    |   12
  5    |   6  

i need the output like this to know which post is mostly votet for which category.

post | most voted in this category               
---------------
  1    |   3
  2    |   5
  3    |   6
  4    |   1
  5    |   4
  6    |   6  

i need this for every post in the table. and than i would need to update every post in another table. i guess i have to do this in a loop. but im already stuck at the first part.

all i have is this. for the first part.

    <?php global $wpdb;

$test = $wpdb->get_results('SELECT posts_id, value, COUNT(posts_id) AS ActionCount
FROM rating_item_entry_value
GROUP BY posts_id
ORDER BY ActionCount DESC'); 
echo '<pre>';
print_r($test);
echo '</pre>';

and this is the output i get

    Array
(
    [0] => stdClass Object
        (
            [posts_id] => 0
            [value] => 5
            [ActionCount] => 7
        )

    [1] => stdClass Object
        (
            [posts_id] => 221
            [value] => 3
            [ActionCount] => 3
        )

    [2] => stdClass Object
        (
            [posts_id] => 197
            [value] => 5
            [ActionCount] => 2
        )

    [3] => stdClass Object
        (
            [posts_id] => 164
            [value] => 3
            [ActionCount] => 1
        )

)

for the example.

I have no idea how to do this better, trying a lot but can't figure it out. does anyone has a good solution how to get the most frequent number for every single id? (and maybe how to safe the results in a variable to update every post in another table within a loop?) thank u so much for any help. regards

asked 35 secs ago

jimmy

1

mysql get result of most frequent value of every post