Remove duplicate category name

Hallo!

Here is my simple script who fetching data from database.
There is two tables "category" and "products".
Here is tables structure:


CREATE TABLE `category` (
`id` int(4) NOT NULL auto_increment,
`name` text NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `products` (
`id` int(4) NOT NULL auto_increment,
`category_id` int(4) NOT NULL default '0',
`product` text NOT NULL,
PRIMARY KEY (`id`)
)

mysql_connect ("localhost", "user", "pass");

mysql_select_db ("mycatalog");

$select = "
	SELECT 
		 name,
		 product
	FROM
		category, 
		products
	WHERE
		category.id = products.category_id
";

$query = mysql_query ("$select") or die (mysql_error());

echo "<table border=1>"; 

while ($row = mysql_fetch_array ($query)) {
	
	echo "<tr><td>" . "<b>" . $row['name'] . "</b>" . "</td><td>" . $row['product'] . "</td></tr>";
	  
}

echo "</table>";

PHP:

But data who I fetch is:

Category products
Apple red
Apple green
Pear yellow
Pear green

How can I remove duplicate Apple, Pear ...?
I want this:

Apple red green
Pear yellow green

Thanks!

 

Have a look at DISTINCT().

 

I tried with DISTINCT but nothing to result...

I think in this piece of code need some "if" checks:

while ($row = mysql_fetch_array ($query)) {
	
	echo "<tr><td>" . "<b>" . $row['name'] . "</b>" . "</td><td>" . $row['product'] . "</td></tr>";
	  
}

PHP:

 

Sure you could do that, but it's not the best way. You'd be pulling unnecessary data from the database. Give this a try.

$select = "
    SELECT 
         DISTINCT name,
         product
    FROM
        category, 
        products
    WHERE
        category.id = products.category_id
	GROUP BY
		products.category_id
";

PHP:

 

I think you should GROUP your results by name

 

Mens, unfortunately, I haven`t good result ...

if I use

$select = "
    SELECT DISTINCT 
         name,
         product
    FROM
        category, 
        products
    WHERE
        category.id = products.category_id
    GROUP BY
        products.category_id
";

PHP:

Here I get this

Apple - red (Here I lose "green")
Pear - yellow (And Here I lose "green")

 

You should use the above with group_concat, search mysql docs for it for exact syntax, it should be something like this.

$select = "
    SELECT DISTINCT 
         name,
         group_concat(product, ' ')
    FROM
        category, 
        products
    WHERE
        category.id = products.category_id
    GROUP BY
        products.category_id
";

PHP: