在Android中从两个表中检索数据而不重复？

Question

I am pulling data from two different tables using the following sql statement:

    $query=mysqli_query($con,"SELECT products.pid,products.product_name,products.product_pic,products.product_thumb,products.product_description,products.product_rating,comments.username, comments.comment FROM products RIGHT JOIN comments on products.pid = comments.pid");

I've tried:

LEFT JOIN
INNER JOIN
AND JUST JOIN

Unfortunately, in the Android listview, I get duplicate results of a product if it has more than one comment. Like so:

        {
            "pid": "2",
            "product_name": "Some product one",
            "product_pic": "http://localhost/img/generic.png",
            "product_thumb": "",
            "product_description": "some long description",
            "product_rating": "0",
            "username": "john",
            "comment": "one of my favorites"
        },
        {
            "pid": "2",
            "product_name": "Some product one",
            "product_pic": "http://localhost/img/generic.png",
            "product_thumb": "",
            "product_description": "some long description",
            "product_rating": "0",
            "username": "jane",
            "comment": "this was so cool"
        }

How do I get the JSON result to display in one row instead of duplicating the product?

Answer 1

What exactly do you expect from the SQL-request?

Do you want something like this?

{
    "pid": "2",
    "product_name": "Some product one",
    "product_pic": "http://localhost/img/generic.png",
    "product_thumb": "",
    "product_description": "some long description",
    "product_rating": "0",
    "comments" : [
        {
            "username": "john",
            "comment": "one of my favorites"
        },
        {
            "username": "jane",
            "comment": "this was so cool"
        }
    ]
}

This is not possible with SQL, but you can change the SQL-response to this format. An other option is to request, the products first, and call a second request for comments.