stijn0713 Posted February 9, 2013 Share Posted February 9, 2013 i got 2 table (please see attached files) table trainingen 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 Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/ Share on other sites More sharing options...
Jessica Posted February 9, 2013 Share Posted February 9, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411350 Share on other sites More sharing options...
Barand Posted February 9, 2013 Share Posted February 9, 2013 You need to add GROUP BY t.naam, o.naam Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411355 Share on other sites More sharing options...
stijn0713 Posted February 9, 2013 Author Share Posted February 9, 2013 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 Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411363 Share on other sites More sharing options...
Barand Posted February 9, 2013 Share Posted February 9, 2013 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. Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411368 Share on other sites More sharing options...
stijn0713 Posted February 9, 2013 Author Share Posted February 9, 2013 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; ?> Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411374 Share on other sites More sharing options...
Barand Posted February 9, 2013 Share Posted February 9, 2013 $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>'; Quote Link to comment https://forums.phpfreaks.com/topic/274267-query-help/#findComment-1411378 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.