2

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

7
  • Does this answer your question? How do I loop through a MySQL query via PDO in PHP? Commented Feb 14, 2020 at 12:32
  • 3
    Your push into $output_arr comes to late, you are only doing that after both of those loops, whereas it should happen inside of the outer loop. (The initialization as an empty array belongs before the whole thing then of course.) Commented Feb 14, 2020 at 12:38
  • That's also an impressively complex query for someone who is new to MySQL - can you describe what its end goal is? There may be a simpler route than the query you currently have. Commented Feb 14, 2020 at 12:39
  • @misorude gives solution to the issue. Other than this, this code has too much query calls to MySQL and could cause script execution break. Consider optimizing code. Commented Feb 14, 2020 at 12:51
  • You could wrap specific parts of your code into individual functions. That way it's easier to keep track of what they do. For example, getAllKamps(), getCompaniesForKamp($kampid), etc. I didn't follow your code so well, so sorry if these function names are wrong. Commented Feb 14, 2020 at 13:45

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.