How to read tables and render them as JSON in PHP?

Question

I have database with structure of category and subcategory:

category:
Image of category table

subcategory:
Image of subcategory table

I want JSON via PHP in required format. I have tried most time but it didn't work.

{
"success":"true",
"category_1":{
"cat_id":"1",
"cat_name":"menu1",
"cat_img":"cat1.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
},
{
"sub_id2":"3",
"sub_name2":"sub name"
},
{
"sub_id3":"4",
"sub_name3":"sub name"
}
]
},
"category_2":{
"cat_id":"2",
"cat_name":"menu2",
"cat_img":"cat2.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
}
]
},
"category_3":{
"cat_id":"3",
"cat_name":"menu3",
"cat_img":"cat3.jpg",
"sub_category":[
{
"sub_id":"1",
"sub_name":"sub name"
},
{
"sub_id1":"2",
"sub_name1":"sub name"
},
{
"sub_id3":"",
"sub_name1":"sub name"
}
]
}
}

I have tried this code, but it didn't work. I would prefer a PDO solution.

$servername = "localhost";
$username = "res_user";
$password = "Res@123";
$dbname = "res_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

 $sql = "SELECT *, category.id AS catId FROM category INNER JOIN sub_category ON category.id = sub_category.category_id ORDER BY category.id, sub_category.category_id";
$res = mysqli_query($conn, $sql);
$categoryArray = array();
$oldCatId = 0;
while ($row = mysqli_fetch_assoc($res)) {
    if ($row['catId'] != $oldCatId) {
        $categoryArray[$row['catId']] = array(
            'success' => true,
            'category_' . $row['catId'] = array(
        'cat_id' => $row["catId"],
        'cat_name' => $row['cat_name'],
        'cat_img' => $row['cat_img'],
        'sub_category' => array(
            'sub_id' => $row['category_id'],
            'sub_name' => $row['sub_name']
        )
            )
        );
     $oldCatId = $row['catId'];
    } else {
        $categoryArray[$row['catId']]['category_' . $row['catId']]['sub_category'][] = array(
            'sub_id' => $row['category_id'],
            'sub_name' => $row['sub_name']
        );
    }
}


  echo  json_encode($categoryArray);

this may help http://stackoverflow.com/questions/671118/what-exactly-is-restful-programming — AVM, Dec 24 '14 at 12:04
Can we see your PHP code? Please edit it into your question. — halfer, Dec 24 '14 at 12:22
Thanks for making an edit. "I want it to work in PDO" - ah, this is why we ask for prior effort. Here someone has written code for you, with code that will work, and it still doesn't match your requirements. When asking questions, make sure all the relevant detail in included please. — halfer, Dec 24 '14 at 16:04
"It didn't work" - what doesn't work? Be more specific if you can, and try some debugging? — halfer, Dec 24 '14 at 16:05

score 0 · Answer 1 · edited Dec 24 '14 at 12:50

0

You need something like this: Get out all rows from category ordered by category id, and JOIN it with subcategory.

Then you loop through on your resutls. You checking, is the current category id is equal to previous cat id. If not, then build the main data array, and add first subcategory, if not, then just add the next subcategory.

$sql = "
    SELECT *, category.id AS catId
    FROM category
    INNER JOIN subcategory ON category.id = subcategory.category_id
    ORDER BY category.id, subcategory.category_id
";
$res = mysqli_query($conn, $sql);
$categoryArray = array();
$oldCatId = 0;
while ($row = mysqli_fetch_assoc($res)) {
    if ($row['catId'] != $oldCatId) {
        $categoryArray[$row['catId']] = array(
            'success' => true,
            'category_' . $row['catId'] = array(
        'cat_id' => $row["catId"],
        'cat_name' => $row['cat_name'],
        'cat_img' => $row['cat_img'],
        'sub_category' => array(
            'sub_id' => $row['category_id'],
            'sub_name' => $row['sub_name']
        )
            )
        );
        $oldCatId = $row['catId'];
    } else {
        $categoryArray[$row['catId']]['category_' . $row['catId']]['sub_category'][] = array(
            'sub_id' => $row['category_id'],
            'sub_name' => $row['sub_name']
        );
    }
}

edited Dec 24 '14 at 12:50

halfer

19,824
17
99
186

answered Dec 24 '14 at 12:30

vaso123

12,347
4
34
64

Well done for answering, though I wonder if it worthwhile encouraging OPs of this kind to make an effort first. Stack Overflow doesn't want to get itself known as a "free coder" service `:)`. – halfer Dec 24 '14 at 12:39
(FWIW I'd discourage concatenation in the query - just let the string run on, and use quotes at the start and finish. It's easier to edit SQL that way, I think). – halfer Dec 24 '14 at 12:40
@halfer: `to get itself known as a "free coder" service` Today is a christmas day man :) This is a gift from me. – vaso123 Dec 24 '14 at 12:47
"just let the string run on" I think this is more readable. First, it's in the column 80, so at here, and in a text editor is readable, and it help me to separate the conditions / statements, etc... – vaso123 Dec 24 '14 at 12:49
I've made an edit to your query, roll it back if you disagree - the requirement for concatenating every line just gets in the way. Hopefully the OP at least will look at the edit! – halfer Dec 24 '14 at 12:51
@halfer The other thing is, why better the concatenated, that is, you can easily comment out a row, or rows if you want. Netbeans guys are not stupid :) – vaso123 Dec 24 '14 at 12:53
You can do that in SQL too - just `/* ... */`. In my estimation, broadly speaking, people are still concatenating because they don't know there's a cleaner alternative. – halfer Dec 24 '14 at 12:54
You can do it in `PHP` not in sql. – vaso123 Dec 24 '14 at 12:57
What do you mean? If you are saying it is not possible to comment in SQL, that's not true - I've described how to do that above. – halfer Dec 24 '14 at 16:00
`$sql = "SELECT 'a' FROM table" . " WHERE 1 = 1";` Now you can comment `$sql = "SELECT 'a' FROM table" //. " WHERE 1 = 1";` when the `WHERE` statement in new row. – vaso123 Dec 24 '14 at 16:02
Yes, I understand that. I still think it adds too much cruft to regard it as a net benefit. My purpose in replying above was to ensure readers were not left with the false impression that one cannot comment code out in SQL. – halfer Dec 24 '14 at 16:08
Notice: Array to string conversion in 'cat_img' => $row['cat_img'], 'sub_category' => array( 'sub_id' => $row['category_id'], 'sub_name' => $row['sub_name'] ) ) ); – Sachin Sonwaniya Dec 26 '14 at 11:14

How to read tables and render them as JSON in PHP?

1 Answers1