<?php
$mysqli = new MysqliCustom("localhost", "my_user", "my_password", "world");
//Допускаю что валидация $catId уже есть.
$questions = $mysqli->getQuestionsByCategoryId($catId);
$result = [];
if (count($questions)) {
$users = $mysqli->getUsersByQuestionsId(array_unique(array_column($questions, 'user_id')));
foreach ($users as $user) {
$result[] = [
'questions' => array_filter($questions, function ($item) use ($user) {
return $item['user_id'] == $user['id'];
}),
'user' => $user
];
}
}
//вывод
class MysqliCustom extends mysqli {
public function __construct($host, $user, $pass, $db, $port = 3306) {
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
parent::__construct($host, $user, $pass, $db, $port);
$this->set_charset('utf8mb4');
}
public function getQuestionsByCategoryId(int $categoryId): array {
$questionsQ = $this->prepare('SELECT * FROM questions WHERE catalog_id = ? ');
// на 8.1 через execute
$questionsQ->bind_param("i", $categoryId);
$questionsQ->execute();
return $this->getResultFromQuery($questionsQ->get_result());
}
public function getUsersByQuestionsId(array $userIds): array {
$userQ = $this->prepare('SELECT id, name, gender FROM users WHERE id IN (' . rtrim(str_repeat('? ,', count($userIds)), ',') . ')');
// на 8.1 через execute
$userQ->bind_param(str_repeat('i', count($userIds)), ...$userIds);
$userQ->execute();
return $this->getResultFromQuery($userQ->get_result());
}
private function getResultFromQuery(mysqli_result|false $result): array {
if (!$result) {
$result->free();
return [];
}
$resultArray = $result->fetch_all(MYSQLI_ASSOC);
$result->free();
if (!$resultArray) {
return [];
}
return $resultArray;
}
}