Need help joining two mysql tables for displaying in mobile client app?

I have an android app that displays products from from one table: id, name, image, thumb, description, and rating. When the user clicks on a product within the listview, it brings up a detail page that shows the remaining data for that id from the database. What I am trying to do is also show comments that have been posted with the same product id from a separate table. Here’s what I have and what I have tried.

The first table(products) structure is:
product id(pid)
name
image
thumb
description
rating

The second table(comments) is:
product id(pid)
comment id(cid)
username
comment

This is the PHP code I have tried:

	  <?php
	include_once("config.php");
	 
	//$query=mysqli_query($con,"SELECT * FROM ".$table_name);
	$query=mysqli_query($con,"SELECT products.product_name,products.product_pic,products.product_thumb,products.product_description,
		products.product_rating,comments.username,comments.comment FROM products, comments WHERE products.pid AND comments.pid =" .$_POST["pid"]);

	$array;
	while($result=mysqli_fetch_assoc($query)){
	 
	$array[]=$result;
	}
	 
	echo json_encode($array);
	?>

Unfortunately, this doesn’t product any results. While using XAMPP to ensure a proper JSON respons, I instead get the following:

`Notice: Undefined index: sid in C:\xampp\htdocs\demoapp\fetch.php on line 6

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\demoapp\fetch.php on line 9

Notice: Undefined variable: array in C:\xampp\htdocs\demoapp\fetch.php on line 14
null`

Any help will be much appreciated. I can’t seem to wrap my brain around this. Giving myself a headache.

Ok, so I managed to get the two tables to show in one JSON response 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 LEFT JOIN comments on products.pid = comments.pid");

Problem is that if comments table has more than one comment for any given product, it displays the product multiple times for each comment instead of showing all comments as a sub category.

    `		{
			"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 would I get the JSON result to show only one response for that product id instead of separate ones? Also, although this is great for retrieving the data in a browser, how would I get this response with a POST method? Thank you

I’m pretty sure this is wrong, unless MySQL does something I’m not familiar with:

WHERE products.pid AND comments.pid =" .$_POST["pid"]);

You will need to do each one to achieve what you’re trying to do. With what you have you are basically saying “if true and comments.pid == pid”

WHERE products.pid  =" . $_POST["pid"]) . " AND comments.pid =" . $_POST["pid"]);
1 Like

I think I understood you correctly. I tried this:

` <?php
include_once(“config.php”);

	$query=mysqli_query($con,"SELECT products.pid,products.product_name,products.product_pic,products.product_thumb,products.product_recipe,products.product_rating,comments.username, comments.comment FROM products,comments WHERE products.pid  =" . $_POST["pid"] . " AND comments.pid =" . $_POST["pid"]);

	$array;
	while($result=mysqli_fetch_assoc($query)){
	 
	$array[]=$result;
	}
	 
	echo json_encode($array);
	?>`

And got this error while running with XAMPP in my Chrome browser:

` Notice: Undefined index: sid in C:\xampp\htdocs\demoapp\fetch.php on line 6

	Notice: Undefined index: sid in C:\xampp\htdocs\demoapp\fetch.php on line 6

	Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\demoapp\fetch.php on line 9

	Notice: Undefined variable: array in C:\xampp\htdocs\demoapp\fetch.php on line 14
	null`

I’m pretty sure I have it formatted correctly per your reply. I had to remove the “)” after the first .$_POST[“pid”] because it was throwing an unexpected “)” error.

I’ve looked at your example code several times for “sid” but all I’m seeing are "pid"s

Where is “sid” ?

Sorry. I meant pid. It was a typo. I ran the example on a different test database that has “sid” instead of “pid” but the format is the same

That explains the “undefined index” errors.

The table column names and array key names need to be the same i.e.
{table name}.{column name} won’t work for {table name}.{different column name}
$_POST['key name'] won’t work for $_POST['different key name']
* Both spelling and case

the test I ran was using the exact same sql query statement with the only difference was I changed “pid” to “sid” to correspond to the table I was querying. Everything else was exactly the same. Column and table names were not different with the exception of the “id”

Back to the query for a second, I’m not sure you can do what you’re hoping to do, though I stress I’m not an SQL expert. I think if you’re running a query across two tables in a one-to-many relationship (i.e. one product can have many comments), you’re going to get the product details back for each one, exactly as you showed in post #2, and you’ll have to deal with that while you’re displaying it.

I have a vague recollection of reading that trying to use columns with the same name in different tables can cause some trouble for php accessing it, though I might be mis-remembering a brief foray into MS-Access.

Or at least not that way. I have never used the syntax
FROM table_one, table_two

Without my reading the documentation I have a strong feeling that sytax will only work when the table schema is identical and for this some kind of JOIN will be needed.

First, other than not returning what you want, do the isolated queries run without errors?

$query=mysqli_query($con,"SELECT products.pid, products.product_name, products.product_pic, products.product_thumb, products.product_recipe, products.product_rating, FROM products, WHERE products.pid  =" . $_POST["pid"]);
$query=mysqli_query($con,"SELECT comments.username, comments.comment FROM comments WHERE comments.pid =" . $_POST["pid"]);

That is just a different version of the JOIN syntax, and in fact, IIRC, the DBMS converts the query from the original format to one like this:

SELECT products.pid
     , products.product_name
     , products.product_pic
     , products.product_thumb
     , products.product_recipe
     , products.product_rating
     , comments.username
     , comments.comment 
  FROM products
 INNER JOIN comments ON comments.pid = products.pid
 WHERE products.pid  = " . $_POST["pid"]

Now, to answer the latest question, the products information WILL be repeated for each row in the comments table that matches. That’s the nature of the beast when dealing with SQL information. So you have one of two choices:

  1. Handle the duplicate information in the PHP code
  2. Split the process into two separate queries, one for the product, and one for the comments.

If you want to do #1, here is a rough (OK, VERY rough - it’s been a while since I’ve done PHP) concept of how to load the array…

$array = array();
$commentCount = 0;
while($result=mysqli_fetch_assoc($query)){
	if ($commentCount == 0) {
		$array['productID'] = $result['pid'];
		$array['name'] = $result['name'];
		$array['image'] = $result['image'];
		$array['thumb'] = $result['thumb'];
		$array['description'] = $result['description'];
		$array['ratings'] = $result['ratings'];
	}
        $comment = array();
        $comment['commentID'] = $result['cid'];
        $comment['username'] = $result['username'];
        $comment['comment'] = $result['comment'];
	$array['comment'][$commentCount]=$comment;
}

I was thinking something along the lines of

$lastproduct = "";
while ($result = mysqli_fetch_assoc($query)) { 
  if ($result['pid'] != $lastproduct) {
    //output the product details here, but only if the product code changed 
    $lastproduct = $result['pid'];
    }
  // output the comments / ratings / usernames here
  }

Why not use a GROUP BY so that you only return one row for each product code in the first place.

Still wouldn’t help in this case as the query would return the product information for each comment. GROUP BYs are only appropriate when some sort of aggregation is needed (most recent comment date, count of number of comments, etc)

I thought all the comments were needed for each product, wouldn’t GROUP BY negate that?

You could GROUP_CONCAT() the comments.

Good point, I hadn’t seen that option.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.