Need some help with a query

[peterbarone]

I have two tables one that holds a list of exercises, and one that holds a list of workouts.

 

My table structures

Exercises table

idexercises

1Bench Press

2Dips

3Pull Ups

 

Workouts  table

 

idexercises1exercises2exercises3exercises4

12132

23221

 

 

So what i'm looking to render in my php file is a table that looks like this.

 

idexercises1exercises2exercises3exercises4

1DipsBenchPull upDips

2Pull upDipsDipsBench

 

 

Thanks for any help

[kickstart]

Hi

 

This should do it.

 

SELECT a.id, b.exercises, c.exercises, d.exercises, e.exercises
FROM Workouts a
INNER JOIN Exercises b ON a.exercises1 = b.id
INNER JOIN Exercises c ON a.exercises2 = c.id
INNER JOIN Exercises d ON a.exercises3 = d.id
INNER JOIN Exercises e ON a.exercises4 = e.id

 

However probably not a good idea to have a fixed number of column s for exercises. Better to have a table of workouts and a table of exercises, and then a link table with as many rows are required for for the number of exercises in any particular workout.

 

All the best

 

Keith

[peterbarone]

Thanks Keith for taken the time to look at this for me.  I'm really new to inner join and MySql for that matter.

 

"Better to have a table of workouts and a table of exercises, and then a link table with as many rows are required for for the number of exercises in any particular workout"

 

I like that idea so much better but it is beyond my knowledge at this time.

[kickstart]

Hi

 

To do it have your table of exercises (as you have now).

 

Have a table of workouts

 

Workouts

Id    WorkoutName

1      Bills work out

2      Joes work out

 

Have a table linking them together

 

WorkoutLink

Id    WorkoutId  ExerciseId

1      1                1

2      1                2

3      1                3

4      1                4

5      2                2

 

Then have

 

SELECT *

FROM Workouts a

INNER JOIN WorkoutLink b ON a.Id = b.WorkoutId

INNER JOIN Exercises c ON b.ExerciseId = c.Id

 

(don't really use SELECT *, just done for show here).

 

That would give you a list of workouts with 1 exercise per row. You could format that as you want with php.

 

All the best

 

Keith

[peterbarone]

Thanks a ton !  You got me thinking with your last post.  I rethought the process of what I was tring to do.

I had everything that I needed I was just making to complex.  Being a noob the magic overwhelms the simplicity lol.

 

Based on the first post to me I just changed the location of the information that I was looking to store and all fell into place.

 

Again thanks so much for taken the time to look at this for me.