CODEWITHSUNDEEP
phpmysqlcron
hohner
hohner

Reputation: 11588

Updating a MySQL table with the COUNT of another

I'm trying to update a column in table Profiles with a COUNT from another table Videos. All videos are stored in the Videos table, and I want to create a column in Profiles called VideoCount which shows the COUNT of that user's videos in the Videos table.

Basically, I want to run a cron job - so I need to know how to find the COUNT of a user's videos in the Videos table and then UPDATE that user's VideoCount in Profiles with this count number.

To identify who's uploaded which video, Videos.Owner and Profiles.ID should be made to match. Does anybody know the SQL which I need to run for the PHP file cron job?

Upvotes: 1

Views: 928

Answers (3)

n0cturnal
n0cturnal

Reputation: 1725

Something like 

UPDATE A SET VideoCount = 
(
  SELECT COUNT(B.*) 
  FROM Videos AS B INNER JOIN Profiles AS B ON (A.Profile_ID = B.Owner_ID)  
  GROUP BY B.Owner_ID
);

I'm not sure this syntax is completely correct. give it a try

Upvotes: 1

teemitzitrone
teemitzitrone

Reputation: 2260

UPDATE Profiles
SET VideoCount = (
   SELECT COUNT(*)
   FROM Videos
   WHERE Videos.Owner = Profiles.ID);

as simple as it can be^^

and if you want it fancy: all the triggers you might need:

DELIMITER //

CREATE TRIGGER increaseVideoCount AFTER INSERT ON Videos
  FOR EACH ROW BEGIN
    UPDATE Profiles SET VideoCount = VideoCount + 1 WHERE ID = NEW.Owner;
  END;
//

CREATE TRIGGER decreaseVideoCount AFTER DELETE ON Videos
  FOR EACH ROW BEGIN
    UPDATE Profiles SET VideoCount = VideoCount - 1 WHERE ID = OLD.Owner;
  END;
//

CREATE TRIGGER checkVideoCount AFTER UPDATE ON Videos
  FOR EACH ROW BEGIN
    IF OLD.Owner <> NEW.Owner THEN
      UPDATE Profiles SET VideoCount = VideoCount + 1 WHERE ID = NEW.Owner;
      UPDATE Profiles SET VideoCount = VideoCount - 1 WHERE ID = OLD.Owner;
    END IF;
  END;
//

DELIMITER ;

Upvotes: 3

XMen
XMen

Reputation: 30248

If you want to update video count that you can upadate video count at the time of upload, so as i think there is no need for the cron job for this.

update profiles set videocount=(select count(*) from videos where profileid =$profileid ) 
where profileid=$profileid

Upvotes: 2

Related Questions