2. PHPRunner General questions
  3. Arrays and while loop
This topic is locked

Arrays and while loop

 		3/28/2020 6:45:09 AM
PHPRunner General questions
P
 PaulM author

I have the following tables. When a person joins, all games are automatically inserted into 'Person Games' from the entire list in the 'Games Master Table'. I can have 100's of Persons and want to be able to update them all when a new game is added if the games don't already exist for that person. Could anybody point me in the right direction please?
Table Games Master Table

Game

Football

Netball

Rugby

Tennis

Running
Table Person

id Name

1 John

2 Roger

3 Dave

4 Susan

5 Mike
Person Games

id Game

1 Football

1 Netball

1 Rugby

1 Tennis

2 Football

2 Netball

2 Rugby

2 Tennis

3 Football

3 Netball

3 Rugby

3 Tennis

4 Football

4 Netball

4 Rugby

4 Tennis

5 Football

5 Netball

5 Rugby

5 Tennis
Sergey Kornilov admin 3/28/2020

So I assume you need to implement an event like AfterAdd of Games table after a new game was added.

  1. We need to select all users that do not have this game yet. I'm not sure if this query is correct but it explains the idea.
    $sql = "select * from Person where id not in (select id from PersonGame where Game='".$values["Game"]."')";
  2. Execute query and loop through results.
    Example:

    https://xlinesoft.com/phprunner/docs/db_query.htm
  3. For each record insert a new record into PersonGames table with the current member ID from SQL query results and the game that as just added ($values["Game"])
    Example:

    https://xlinesoft.com/phprunner/docs/db_insert.htm
P
 		PaulM author 3/28/2020



So I assume you need to implement an event like AfterAdd of Games table after a new game was added.

  1. We need to select all users that do not have this game yet. I'm not sure if this query is correct but it explains the idea.
    $sql = "select * from Person where id not in (select id from PersonGame where Game='".$values["Game"]."')";
  2. Execute query and loop through results.
    Example:

    https://xlinesoft.com/phprunner/docs/db_query.htm
  3. For each record insert a new record into PersonGames table with the current member ID from SQL query results and the game that as just added ($values["Game"])
    Example:

    https://xlinesoft.com/phprunner/docs/db_insert.htm


Thank you, that helps but its more like I need to select all the Persons_id and then update their games that are missing and populate it from the games table as it may be more than just the latest game missing
Sergey Kornilov admin 3/29/2020

This exactly what I explained how to do. If you add this code to the Games table AfterAdd event it will take care of the latest game added and you will never have the situation where more than one game is missing.
If you just need to make sure that all persons have all games - you can just delete all records in PersonGame table and insert all games for all persons.