CODEWITHSUNDEEP
phpmysqldatabase
Ndrd
Ndrd

Reputation: 13

Counting number of times a value occurs and sending count to another column?

I have a table that I have fed data into through a PHP script, and am managing it using phpMyAdmin. My table has 4 columns. The first is an auto increment, second and third are values being fed in, and the final is meant to keep track of how many times the value from column 3 has appeared.

This is how my table currently appears

RowNumber   UserID  SongID  Plays
1             540   2191    0
2             540   2671    0
3             550   3891    0
4             550   2191    0
5             550   2671    0
6             560   9391    0
7             560   2191    0

I want to search through the whole table and change the value in the Plays column to show how many times the value appears in the table.

Ideally, this is how I want my table to output:

RowNumber   UserID  SongID  Plays
1             540   2191    3
2             540   2671    2
3             550   3891    1
4             550   2191    3
5             550   2671    2
6             560   9391    1
7             560   2191    3

Is there a way to search through the table and update these values? The amount of data being inputted into the table is quite large, so an efficient solution would be greatly appreciated.

Upvotes: 1

Views: 636

Answers (2)

ariefbayu
ariefbayu

Reputation: 21979

I think you can use simple PHP query that is run periodically (Note: not an actual code):

$sql = "SELECT UserID, SongID, COUNT(RowNumber) AS CNT FROM SomeTable GROUP BY 1, 2 ORDER BY 3 ASC";

foreach($result as $row){
   $sql = "UPDATE SomeTable SET Plays = ".$row['CNT']." WHERE UserID = '" . $row['UserID'] . "' AND SongID = '" . $row['SongID'] . "'";
}

Upvotes: 0

cdhowie
cdhowie

Reputation: 169008

Consider using a view instead of a table, unless you need the value cached for performance reasons. You can compute the count of each value in a subquery and join the results back to the table like so:

SELECT Table.RowNumber, Table.UserID, Table.SongID, x.Plays
FROM Table
INNER JOIN (
    SELECT SongID, COUNT(*) AS Plays
    FROM Table
    GROUP BY SongID
) x
ON Table.SongID = x.SongID;

And create a view from it using CREATE VIEW TableWithPlays AS SELECT .... Having an index on SongID will allow the subquery to complete rather quickly, and you will never have to worry about the Plays column being up to date.

If you do in fact want to cache the values, use an UPDATE query based on the above query:

UPDATE Table a
INNER JOIN (
    SELECT SongID, COUNT(*) AS Plays
    FROM Table
    GROUP BY SongID
) b
ON a.SongID = b.SongID
SET Plays = b.Plays;

As with the view solution, don't forget the index on SongID.

Upvotes: 1

Related Questions