PHP Mysql multidimensional array

Question

I’m having a little trouble assigning an index to encompass the items marked in the figure below in a multidimensional array I’m mounting with the database queries:

I need to take from another table the posts of each user and add in the array "hosters" together with the other data from the first query.

Doing the query in a single query produces multiple records according to the number of posts of each "Hoster", so it would not be feasible.

I’m making a array_push to "add" in the same array, however, you need an index there where it is marked in the image to go through the posts in the frontend.

$data = json_decode(file_get_contents("php://input"));

if (isset($data->city)) {

    $city = $data->city;
    $retorno = array();

    $sql = "SELECT m.userId, m.username, m.lastname, m.city, m.state, s.id FROM members m, signup s 
    WHERE city = ? AND m.userId = s.id AND s.hostOption = 'true' ";

    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('s', $city);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($mID, $username, $lastname, $city, $state, $sID);

    if ($stmt->num_rows > 0) {
        while ($stmt->fetch()) {

            $result = array(
                "id" => $mID,
                "name" => $username,
                "lastname" => $lastname,
                "city" => $city,
                "state" => $state,
            );

            $selectImages = mysqli_query($mysqli, "SELECT * FROM tours WHERE user_id = '" . $sID . "' ");
            while ($row = $selectImages->fetch_array(MYSQLI_BOTH)) {

                $tours = array("title" => $row['title']);
                array_push($result, $tours);
            }
            $retorno[] = $result;
        }

        $hosters["hosters"] = $retorno;
    }
    $return = json_encode($hosters);
    echo $return;
    $mysqli->close();
}

I tried to put $tours["tour"] = array("title" => $row['title']); but he plays the index "inside" hehe. I believe it is something simple for those who have more practice with arrays. Thanks from now on!

Answer 1

I ended up doing it that way:

$tour = array();

if ($stmt->num_rows > 0) {
    while ($stmt->fetch()) {

        $result = array(
            "id" => $mID,
            "name" => $username,
            "lastname" => $lastname,
            "city" => $city,
            "state" => $state,
            "tour" => $tour
        );

        $selectImages = mysqli_query($mysqli, "SELECT * FROM tours WHERE user_id = '" . $sID . "' ");
        while ($row = $selectImages->fetch_array(MYSQLI_BOTH)) {

            $tours = array("title" => $row['title'], "description" => $row['description']);
            array_push($result["tour"], $tours);
        }
        $retorno[] = $result;
    }

    $hosters["hosters"] = $retorno;
}