0

I have three tables:

Table Results:

  • id
  • homeScore
  • awayScore

Table Ladder:

  • id
  • points

Table Predictions

  • id
  • homeScore
  • awayScore

I want to add points to all ids in Table Ladder, triggered by insertions, modifications or removals in Table Results, depending on the value of homeScore,awayScore from table Predictions.

And I have rules like:

  • If user result and real result are identical, +5 points
  • If user predicted correct winner, +2 points
  • If user predicted correct goal difference +3 points
  • If user predicted correct goals scored, +1 point (can happen for both teams)

Is this possible with only SQL? If not, how do I approach this with PHP?

Troix
  • 128
  • 14
  • 6
    You are probably looking for `triggers`. That's as much information as I can give you with the current content of your question. – FirstOne Dec 29 '15 at 02:13
  • 1
    Possible duplicate of [PHP MySQL Triggers - How to pass variables to trigger?](http://stackoverflow.com/questions/7750208/php-mysql-triggers-how-to-pass-variables-to-trigger) – Jia Jian Goi Dec 29 '15 at 02:24
  • I've looked into triggers, it is a bit confusing to me, I've updated my question. Am I able to apply my rules(different points for different outcomes) within triggers? @FirstOne – Troix Dec 29 '15 at 11:40
  • @Troix, Yeah, you can have `if`s inside your trigger. That way, it's a matter of defining the conditions and what to do when each one occur. First you should learn the basic of triggers, ex: insert something -> do something else. Then you might want to learn triggers that have different behaviour depending on the value. Since this will introduce the `IF`, it will be easier to create different outcome based on those criteria. – FirstOne Dec 29 '15 at 14:51

1 Answers1

1

You can do this if you define triggers. Compare new result with old result upon insert/update/delete and your problem will be solved. Read some tutorials about triggers for the RDBMS you are using and you will be able to solve the problem.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Thanks for the edit & answer, I've looked into triggers, however, I am not sure if I can apply different amount of points depending on values from those columns? – Troix Dec 29 '15 at 11:45