PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE points (`id` int, `attempt_id` int, `group_id` int, `points` int) ; INSERT INTO points (`id`, `attempt_id`, `group_id`, `points`) VALUES (1, 1, 2, 5), (2, 1, 2, 5), (3, 1, 1, 5), (4, 2, 3, 5), (5, 2, 2, 5), (6, 2, 3, 5), (7, 3, 1, 5), (8, 3, 3, 5) ; CREATE TABLE attempts (`id` int, `status` int) ; INSERT INTO attempts (`id`, `status`) VALUES (1, 1), (2, 1), (3, 0) ; CREATE TABLE `groups` (`id` int, `title` varchar(7)) ; INSERT INTO `groups` (`id`, `title`) VALUES (1, 'Group 1'), (2, 'Group 2'), (3, 'Group 3') ;
Copy Clear
Copy Format Clear
<?php $query = mysqli_query($mysqli, "SELECT * FROM `groups` order BY `id` ASC"); while ( $row = mysqli_fetch_array($query) ) { $group_id = $row["id"]; $gpc = mysqli_query($mysqli, "SELECT SUM(`points`) as TotalPoints FROM `points` WHERE `attempt_id` IN (SELECT `id` FROM `attempts` WHERE `status`=1) AND `group_id`=".$group_id); $gpcrow = mysqli_fetch_assoc($gpc); echo $row["title"]." : ".$gpcrow["TotalPoints"]; }
Show:  
Copy Clear