CREATE TABLE `groups` (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`position` INT(11),
`active` INT(11)
);
INSERT INTO `groups` (`active`) VALUES (1), (1);
CREATE TABLE `methods` (
`id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`group_id` INT(11),
`name` VARCHAR(255),
`position` INT(11),
`active` INT(11)
);
INSERT INTO `methods` (`group_id`, `name`, `active`) VALUES
(1, 'Name 1', 1),(1, 'Name 1', 1),
(2, 'Name 1', 1),(2, 'Name 1', 1);
SELECT
`methods`.*
FROM `methods`
LEFT JOIN `groups` ON `groups`.`id` = `methods`.`group_id`
WHERE
`methods`.`active` = 1 AND
`groups`.`active` = 1
ORDER BY `methods`.`position`
<?php
$query = 'SELECT
`methods`.*
FROM `methods`
LEFT JOIN `groups` ON `groups`.`id` = `methods`.`group_id`
WHERE
`methods`.`active` = ? AND
`groups`.`active` = ?
ORDER BY `methods`.`group_id`, `methods`.`position`;';
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([1, 1]);
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$group_id = $row['group_id'];
printf(
'<ul data-group_id="%d">' . PHP_EOL . ' <li data-id="%d">%s</li>' . PHP_EOL,
$group_id, $row['id'], $roe['name']
);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if ($group_id != $row['group_id']) {
$group_id = $row['group_id'];
printf(
'</ul><ul data-group_id="%d">' . PHP_EOL,
$group_id
);
}
printf(' <li data-id="%d">%s</li>' . PHP_EOL, $row['id'], $roe['name']);
}
printf('</ul>');
}