0

I have a website that allows coaches to assign and track athletes training. For the athletes to see their training 4 tables need to work together, I last updated this years ago as only I and my athletes was using it and I used to just do another sql request inside the while loop of the previous request to get the desired result, as others will be using it in the future I want to do things properly and I don't think this is possible with prepared statements.

I am not sure if what I have is a coding problem or indeed a logic problem.

The tables are:

Group - This is used to track which athletes are in which groups or teams, in_group being the id of the users

groupName in_group
group1 1,2,3,4
group2 5,6,7,8

Training - This is used to assign the training, and track which athletes can make each session, plan being the id of the work out in workout_title and attending being the id of the users

sessDate groupName plan attending
2021-09-05 group1 1,2,3 2,4
2021-07-08 group2 4,5,6 6,7,8

Workout_title - this holds the titles of the work outs for the athletes, the idea is the coach can have multiple set workouts and adjust things like sets, reps etc

id groupName wo_title
1 group1 warm up
2 group1 plyos
4 group2 sprints

Workout_exercise - this holds the individual exercises of the work outs, linked by the tid column which is the workout_title id

tid wo_title exercise_name
1 warm up high knees
1 warm up A-skip
2 plyos bounds
2 plyos hops
4 sprints 40m
4 sprints 30m
4 sprints 20m

I have tried JOIN and INNER JOIN (which I think are the same thing?) for the 1st 3 tables all seemed to work fine except it only returns the 1st workout in the plan column. I can't figure out a way for the sql request to run through all the id's in the plan column and line out the corresponding workout_title.

$ps = $db_conx->prepare("SELECT group.groupName, training.sessDate, 
    training.plan, workout_title.id, workout_title.wo_title, workout_title.groupName 
    FROM group 
    JOIN training 
    ON group.groupName = training.groupName
    JOIN workout_title
    ON training.plan = workout_title.id
    WHERE FIND_IN_SET(?, in_group)");
$ps->bind_param("s", $username); 
$ps->execute();
$result = $ps->get_result();
while ($row = $result->fetch_assoc()) {
}

I read some solutions as to storing the results and then running another sql request but again could not get this to work, the result would not match.

If there is no way to cade it and indeed I need to re arrange the tables any help with the logic in doing that would also be appreciated.

Shadow
  • 33,525
  • 10
  • 51
  • 64
dyer926
  • 47
  • 1
  • 10
  • 3
    What you have is a database design problem. Storing lists of values in a single column is not a good idea. SQL already has a structure for storing lists, they are called tables. So instead of your column `in_group`, you'd have a separate table called something like `GroupUser` with two columns, `GroupID` and `UserID`, this would link your groups to your users. Adopt this approach throughout, and you'll probably find most of your problems go away – GarethD May 20 '21 at 11:27
  • 2
    To tack onto Gareth's comment, here it is explained: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – El_Vanja May 20 '21 at 11:29
  • Your actual issue is here: `ON training.plan = workout_title.id`, the examples you have given for `training.plan` are `1,2,3` and `4,5,6`, and for `workout_title.id` the values are `1`, `2`, and `4`, so there are no matches. With your actual data what you'll probably find is this only works for training plans with a s ingle workout. As above though, fix your database design and your join problems will go away – GarethD May 20 '21 at 11:31
  • 1
    I don't see what this has to do with PHP, mysqli or prepared statements. Your problem is non-normalized database. Fix your database structure and then you will be able to use SQL joins – Dharman May 20 '21 at 11:34
  • @GarethD yes you are correct only works on the first number in the list. You first comment seems to be the only fix but will probably need a hole re-work of the site, would doing something like that slow the system down? or does it not affect efficiency? – dyer926 May 20 '21 at 14:58
  • @dyer926 no, it would not, quite to the contrary. See the link in El_Vanja's comment above for why you really, really should not use comma delimited list of values in a string in a relational database. – Shadow May 20 '21 at 15:46

0 Answers0