Retrieve data from two tables without duplicates in 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?

score 2 · Accepted Answer · edited May 09 '16 at 12:28

2

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.

edited May 09 '16 at 12:28

Mogsdad

44,709
21
151
275

answered Mar 07 '16 at 00:29

agi

100
6

Yes. Thats exactly how I want it to be retrieved – Steve C. Mar 07 '16 at 00:30
1

Ok, unfortunately there is no SQL-statement, which gives you such a formatted response. So if you really need this more compact format (maybe to send it from a server to a client) you can format the response or call multiple SQL-requests and compose the data. – agi Mar 07 '16 at 00:40
It doesn't need to be pretty like that but if there are 10 comments for product id 2, it shouldn't show 10 list items for the same product. You know what I mean? – Steve C. Mar 07 '16 at 00:41
Do you want to show the products or the comments in your listview? – agi Mar 07 '16 at 00:44
This is what I'm trying to achieve. I have a listview that lists all the products. OnClick of the list item brings up a detail activity. Within that activity clicking on a button opens a comments activity that shows all the comments for that product. My intention was to use volley to retrieve the json data and pass it to each activity using intents. Everything works as it should but instead of showing only one row item for product id 2 and then the corresponding comments, I get as many results of that product as there are comments – Steve C. Mar 07 '16 at 00:48
I know it can be done because I've seen the Android Central app successfully do it. Unfortunately I have no idea how they did it. :/ – Steve C. Mar 07 '16 at 00:49
1

Volley is a really good idea! I also worked with it. I would recommend to do more than just one request. If you have a lot of products and a lot of comments, you would load a huge amount of comments and data, just to show the products. So I would make a request for the list of the products and pass the product id to the comment-activity and make the request for the comments there. – agi Mar 07 '16 at 00:55
I was thinking about that possibility but I am unsure as to how to pass the product id to a new volley request to get all comments corresponding to that product id. Would you have a generic example of this? – Steve C. Mar 07 '16 at 01:03
1

For a GET-request you just have to add the parameter to the url: e.g. http://example.com/comments?product_id=2 (see also: http://stackoverflow.com/questions/16626032/volley-post-get-parameters) – agi Mar 07 '16 at 01:08
I'll give it a go and see if I can achieve the results I'm looking for. Thank you for your insight! – Steve C. Mar 07 '16 at 01:10
np and thx for the upvotes! At least I can comment everywhere :D – agi Mar 07 '16 at 01:12
1

You're welcome. I try to make sure I give up-votes where they're due. – Steve C. Mar 07 '16 at 01:14

Retrieve data from two tables without duplicates in Android?

1 Answers1