SlimFrameowork Array Groups

slim-framework

(Alex Mieila) #1

Buna seara revin cu o intrebare legata de SlimFramework si API-uri in sine.
Se da codul:

$app->get('/api/books', function($request, $response) use ($app){
    require_once('databaseconnect.php');

    $queryBooks = "
    SELECT
        kbb.bookID,
        kbb.bookName,
        GROUP_CONCAT(kbc.contentText) as Text,
        GROUP_CONCAT(kbc.contentType) as Type
    FROM
        kb_books as kbb,
        kb_content as kbc        
    WHERE
        kbb.bookID = kbc.bookID
    GROUP BY
        kbb.bookID    
        ";

    $resultBooks = $mysqli->query($queryBooks);

    while($rowP = $resultBooks->fetch_assoc()){
        $data[] = $rowP;
    }

    if (!isset($data)){
        return $response->withStatus(404);
    } else {
        echo json_encode($data);
    }

});

Si baza de date:
Screenshot_2 Screenshot_3

Momentan afiseaza asta:
Screenshot_4

Asa ca eu vreau sa arate text si type ca noi categorii separate, sa nu faca concat cu separator.

Multumesc!


(Eduard-Dan Stanescu) #2

Eu ma gandesc ca asa ar trebui sa arate la final JSON-ul generat:

[
   {
      bookID: "1",
      bookName: "Carte 1",
      content: [
         {text: "text 1", type: "Warning"},
         {text: "text 2", type: "Warning"},
         {text: "text 3", type: "Warning"}
      ]
   },
   {
      bookID: "2",
      bookName: "Carte 2",
      content: [
         {text: "text 1", type: "Warning"},
         {text: "text 2", type: "Warning"}
      ]
   }
]

(Andrei Avram) #3

Mie nu mi-e foarte clar ce doresti.

Poate ai nevoie de JSON_ARRAYAGG in loc de GROUP_CONCAT?
Versiunea minima de mysql de care ai nevoie e 5.7.


(Alex Mieila) #4

Ceva de genul as vrea sa fac doar ca nu imi dau seama cum :slight_smile:


(Eduard-Dan Stanescu) #5

Verifica daca ai kbc.contentID, daca nu redenumeste in query si cod.

$app->get('/api/books', function($request, $response) use ($app){
    require_once('databaseconnect.php');

    $queryBooks = "
        SELECT
            kbb.bookID,
            kbb.bookName,
            kbc.contentID,
            kbc.contentText,
            kbc.contentType
        FROM
            kb_books as kbb,
            kb_content as kbc
        WHERE
            kbb.bookID = kbc.bookID
    ";

    $resultBooks = $mysqli->query($queryBooks);

    while($rowP = $resultBooks->fetch_assoc()){
        $data[] = $rowP;
    }

    $dataBooks = array();
    foreach ($data as $key) {
        $dataBooks[$key['bookID']]['bookID'] = $key['bookID'];
        $dataBooks[$key['bookID']]['bookName'] = $key['bookName'];
        $dataBooks[$key['bookID']]['bookContent'][$key['contentID']] = array(
            'text' => $key['contentText'],
            'type' => $key['contentType']
        );
        $dataBooks[$key['bookID']]['bookContent'] = array_values($dataBooks[$key['bookID']]['bookContent']);
    }
    $dataBooks = array_values($dataBooks);

    if (!isset($dataBooks)){
        return $response->withStatus(404);
    } else {
        echo json_encode($dataBooks);
    }

});

(Alex Mieila) #6

Asta a fost, multumesc mult de tot!