CODEWITHSUNDEEP
phpmysqllaravel
xcomsw
xcomsw

Reputation: 165

Select inside a select query MYSQL

Hello! I have a query that needs to output screens data.
Each screens has 4 booths to be shown. I need a query with one result (because I just have one screen) And has 4 booths in it (coz I assigned 4 booths)

Here's my query:

$result = DB::select("
    SELECT
        `A`.`scr_name`,
        `A`.`mission_id`,
        `B`.`booth_id`,
        `E`.`name`
    FROM `tbl_screen` `A`
    LEFT JOIN
        `tbl_screen_booths` `B`
    ON `B`.`screen_id` = `A`.`id`
    LEFT JOIN
        `tbl_service_booths` `C`
    ON `B`.`booth_id` = `C`.`id`
    LEFT JOIN
        `tbl_missions_services` `D`
    ON `C`.`mission_services_id` = `D`.`id`
    LEFT JOIN
        `tbl_services` `E`
    ON `D`.`service_id` = `E`.`id`
    WHERE `A`.`mission_id` = $mission_id
    GROUP BY
        `B`.`booth_id`;
");

return $result;

I want something like this:

"scr_name": "Test Screen",
"mission_id": 2,
"name": "booth1", //index[0]
"name": "booth2", //index[1]
"name": "booth3", //index[2]
"name": "booth4" //index[4]

My query returns something like this:

[
{
"scr_name": "Test Screen",
"mission_id": 2,
"booth_id": 7,
"name": "booth1"
},
{
"scr_name": "Test Screen",
"mission_id": 2,
"booth_id": 9,
"name": "booth2"
},
{
"scr_name": "Test Screen",
"mission_id": 2,
"booth_id": 10,
"name": "booth3"
},
{
"scr_name": "Test Screen",
"mission_id": 2,
"booth_id": 11,
"name": "booth4"
}
]

Upvotes: 1

Views: 38

Answers (1)

ZeroOne
ZeroOne

Reputation: 9117

the result is okay.. just need a little iterate to get the value..

$data['scr_name'] = $result[0]->scr_name;
$data['mission_id'] = $result[0]-> mission_id;

foreach($result as $index => $item) {
     $data['name'.($index+1)] => $item->name;
}

result: 

"scr_name": "Test Screen",
"mission_id": 2,
"name1": "booth1", //index[0]
"name2": "booth2", //index[1]
"name3": "booth3", //index[2]
"name4": "booth4" //index[4]

array key cant have same key name

Upvotes: 1

Related Questions