query help

[stijn0713]

i got 2 table (please see attached files)

1. table trainingen
   
2. table oefeningen (dutch for excercices)
   

 

I'm trying to show on a page:

 

training 1

deadlift : 10, 8, 6

squat : 5, 5, 3

 

that why i try to query the results like this:

 

Array
(
[training_id] => 1
[training_naam] => TRAINING 1
[excercices] => Array
	(
		[deadlift] => 10, 8, 6
		[squat] => 5,  5, 5
	)

)

OR

Array
(
[training_id] => 1
[training_naam] => TRAINING 1
[excercices] => Array
	(
		[deadlift] => Array
			(
				[0] => 10
				[1] => 8
				[2] => 6
			)
		[squat] => Array
			(
				[0] => 5
				[1] => 5
				[2] => 5
			)
	)
)

 

and a another array with the same structure for another training:

 

I only managed to get something like this:

 

$sql = "SELECT t.training_id, t.naam AS training_naam, o.naam, GROUP_CONCAT(reps) AS excercices FROM trainingen t
inner JOIN oefeningen o ON t.training_id = o.training_id
WHERE user_id = $id";

 

which gives:

 

Array
(
[training_id] => 1
[training_naam] => TRAINING 1
[naam] => deadlift
[excercices] => 10,8,6,12,12,10,10,6,5,5,10,3,10,10,5,10,8,6,10,8,6,10,8,6,10,6,4
)

 

Can somebody help me out the right query? thanks!

trainingen.pdf

oefeningen.pdf

[Jessica]

You need to limit the join to the exercises table on which two exercises you want. It looks like you're getting every single exercise.

[Barand]

You need to add

 

GROUP BY t.naam, o.naam

[stijn0713]

thanks for the GROUP BY advice. i looked a bit further to that statement what it does and it seems logic that now i get arrays where the reps are comma-seperated and grouped for each combination of a training name and excercice name. the thing is that i now have a but duplicate values in my result set arrays, like this:

 

Array
(
   [training_id] => 1
   [training_naam] => TRAINING 1
   [naam] => deadlift
   [excercices] => 10,8,6
)
Array
(
   [training_id] => 1
   [training_naam] => TRAINING 1
   [naam] => squat
   [excercices] => 12,12,10,10,6,5,5
)

 

It would be nicer if it where possible to make the excercice another layer deeper with the name of the excercice and the reps as values, like:

 

Array
(
    [training_id] => 1
    [training_naam] => TRAINING 1
    [excercices] => Array
		    (
				    [deadlift] => 10, 8, 6
				    [squat] => 5,  5, 5
		    )
)

 

Is that possible, or do i better do this with php after having retrieved all the different record sets? thanks already for the help

[Barand]

I can't see that the query would be any different to achieve the latter. It's down to how you process the results to build the arrays.

[stijn0713]

probably a messy solution, but ill share it if someone might find it usefull:

 

<?php

$id = $_SESSION['user_id'];

$sql = "SELECT training_id, naam FROM trainingen WHERE user_id = $id";
$rs = mysql_query($sql);


while ($row = mysql_fetch_assoc($rs)){

 $t_id = $row['training_id'];
 $trainingen[$row['training_id']]['t_naam'] = $row['naam'];

 $sql2 = "SELECT o.naam AS o_naam, GROUP_CONCAT(reps) AS reps FROM oefeningen o WHERE training_id = $t_id GROUP BY training_id, o.naam";
 $rs2 = mysql_query($sql2);

  while ($row2 = mysql_fetch_assoc($rs2)){
   $trainingen[$row['training_id']]['oefeningen'][$row2['o_naam']] = $row2['reps'];
  }
}
?>

<?php
$content = '<ul>';
foreach ($trainingen as $training_id => $arr){

 $t_naam = $trainingen[$training_id]['t_naam'];
 $content .= '<li>';
 $content .= "$t_naam";
 $content .= '</li>';

  foreach($trainingen[$training_id]['oefeningen'] as $o_naam => $reps){

   $content .= '<ul>';
   $content .= '<li>';
   $content .= "$o_naam" . "($reps)";
   $content .= '</li>';
   $content .= '</ul>';
  }

}
$content .= '<ul>';
echo $content;
?>

[Barand]

$sql = "SELECT t.training_id, t.naam AS training_naam, o.naam,
   GROUP_CONCAT(reps) AS excercices
FROM trainingen t
   INNER JOIN oefeningen o ON t.training_id = o.training_id
WHERE user_id = 1
GROUP BY t.naam,o.naam";

$res = $mysqli->query($sql);
$results = array();

while (list($tid, $tnm, $onm, $reps) = $res->fetch_row()) {
   if (!isset($results[$tid]))
    $results[$tid] = array('training_id'=>$tid, 'naam'=>$tnm, 'excercises'=>array());
   $results[$tid]['excercises'][$onm] = $reps;
}

echo '<pre>',print_r($results, true),'</pre>';