Im very new to both mysql and php.
So I'm making 2 sql queries and binding them together with kampid as the from both the queries.
everything is in the same table and not in different so what I know I cannot
$array1 = array();
$array2 = array();
$sql = "select r.kampid, q.trade from (
select kampid
from posts where kampid != 0
group by kampid
) r
join
(
select p.kampid, p.trade
from
(
select kampid, trade, count(*) as cnt
from posts
group by kampid, trade
) p
join
(
select kampid, max(cnt) as maxcnt
from (
select kampid, trade, count(*) as cnt
from posts
group by kampid, trade) x
group by kampid) y
on y.kampid = p.kampid and y.maxcnt = p.cnt
) q
on r.kampid = q.kampid";
//prepare statement
$statement = $this->conn->prepare($sql);
//catch error
if (!$statement){
throw new Exception($statement->error);
}
$statement->execute();
//result we got in execution
$result = $statement->get_result();
// each line append to array new row one by one when it is found
while ($row = $result->fetch_assoc()){
// $array1[$row["kampid"]][] = array( $row['trade'] );
$array1['kampid'] = $row['kampid'];
$array1['trade'] = array($row['trade']);
$array1['department'] = array();
$sql2 = "SELECT COUNT(posts.companyName) AS companies,
posts.kampid, posts.department, SUM(posts.people)AS total
FROM posts WHERE kampid = '".$row['kampid']."' GROUP BY posts.department, posts.kampid ORDER BY posts.kampid, posts.department ASC";
//prepare statement
$statement = $this->conn->prepare($sql2);
//catch error
if (!$statement){
throw new Exception($statement->error);
}
$statement->execute();
//result we got in execution
$result = $statement->get_result();
while ($row2 = $result->fetch_assoc()){
$array2['department'] = $row2['department'];
$array2['companies'] = $row2['companies'];
$array2['total'] = $row2['total'];
array_push($array1[department], $array2);
}
}
$output_arr = array();
array_push($output_arr, $array1);
$jsonData = json_encode($output_arr, JSON_PRETTY_PRINT);
echo $jsonData;
}
and I get this output:
[
{
"kampid": 1901,
"trade": [
"Åkeri"
],
"department": [
{
"department": 3,
"companies": 8,
"total": 63
},
{
"department": 4,
"companies": 38,
"total": 114
}
]
}
]
but the json stops after the first "kampid", when there are more "kampid" 1902, 1903 etc
the result I want is as follow:
[
{
"kampid": 1901,
"trade": [
"Truck"
],
"department": [
{
"department": 3,
"companies": 8,
"total": 63
},
{
"department": 4,
"companies": 38,
"total": 114
}
]
},
{
"kampid": 1902,
"trade": [
"Mechanic"
],
"department": [
{
"department": 3,
"companies": 8,
"total": 63
},
{
"department": 4,
"companies": 38,
"total": 114
}
]
},
{
"kampid": 1903,
"trade": [
""
],
"department": [
{
"department": 3,
"companies": 8,
"total": 63
},
{
"department": 4,
"companies": 38,
"total": 114
}
]
}
]
Can someone point out what I'm doing wrong?
EDIT: got answer to move "array_push($output_arr, $array1);" inside the outer loop, but for consisted result I needed to rename the second $result and $statement
getAllKamps(),getCompaniesForKamp($kampid), etc. I didn't follow your code so well, so sorry if these function names are wrong.