phpmyadmin Question posted in
The official documentation can be found here.

Schedule and update with count of a different table – Phpmyadmin

i have 3 tables like this

questions_table

question_id | content           |  user             |
1           | my first question | [email protected] |
2           | my second question | [email protected]|

replies_table

reply_id | question_id|user                |content               |voteCount|
1        |      1     |[email protected] |first reply question 1 |0       |
2        |      1     |[email protected] |second reply question1 |0       |

vote_table

vote_id  | reply_id|  voted_by          |
1        |      2  | [email protected]|
2        |      2  | [email protected] |

so to explain this:

  1. A question was posted by two users [email protected] and
    [email protected]
  2. Then [email protected] and [email protected] replied to
    question 1.
  3. Then the reply of [email protected] which has a reply_id of 2 was voted up by
    [email protected] and [email protected]

what i need to do is to write an event scheduler in myphpmyadmin which will run every 2 hours.
what i want the query to do is to update the column voteCount in replies_table by counting the votes on that reply id.
this is what i got so far

SELECT COUNT(voteCount)
FROM replies_table
WHERE reply_id = .../**dont know how am i suppose to do this part **/;

i know it would be something like this but i have never wrote a event scheduler

Tags:

2

Answers


  1. 0

    You may simply use join to get the total vote count.

      select r.reply_id, v.Countreply as count from replies_table as r 
  inner join 
  (select reply_id, count(reply_id) as CountReply from vote_table group by reply_id) as v 
  on r.reply_id= v.reply_id

    If you want to update then it is simply done by update.

     update r set r.votecount = v.Countreply 
  from replies_table as r inner join 
  (select reply_id, count(reply_id) as CountReply from vote_table group by reply_id) as v 
  on r.reply_id= v.reply_id
    Login or Signup to reply.
  2. 0

    In MySQL, you would express the update as:

    i want the query to do is to update the column voteCount in replies_table by counting the votes on that reply id. this is what i got so far

    update replies_table rt join
       (select reply_id, count(*) as numvotes
        from votes v
        group by reply_id
      ) v
    set rt.VoteCount = v.numvotes;

    This may not be the best way to keep your data up-to-date.

    If your tables are not very big, then there is no need to store the VoteCount separately. Just run the query when you need it.

    If the VoteCount is actually important, then use a trigger to keep it up-to-date.

    And, there is no need to update all the rows, even if you take this route. You can keep track of timestamps and the last time updated to limit the number of updates.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search