CODEWITHSUNDEEP
phpmysqlsqlselectdistinct
Jimmy Adaro
Jimmy Adaro

Reputation: 1405

Display product OR collection

I'm doing a products system where a product can be included inside a collection.

In products' page I show all the products, BUT if some product is INSIDE a collection, I need to display only the collection name and NOT the product itself. I mean, hide the products who are inside a collection and show the collection once while also showing the others products (without collection).

TABLE 1: colecciones ("collections" in Spanish) - Where I store the ID and name of the collection.

CREATE TABLE `colecciones` ( 
  `id` INT(255) NOT NULL AUTO_INCREMENT , 
  `nombre` VARCHAR(100) NOT NULL , 
  PRIMARY KEY (`id`)
);

INSERT INTO `colecciones`
    (`nombre`)
VALUES
  ('dulce'),('salado'),('agrio')
;

http://sqlfiddle.com/#!9/8534e/1/0

TABLE 2: productos ("products") - Where I store the products and, if it is inside a collection, the collection ID (column "coleccion"). If not, the value is 0.

CREATE TABLE `productos` ( 
  `id` INT(255) NOT NULL AUTO_INCREMENT , 
 `nombre` VARCHAR(100) NOT NULL , 
 `coleccion` INT(255) NOT NULL , 
 PRIMARY KEY (`id`)
);

INSERT INTO `productos`
    (`nombre`, `coleccion`)
VALUES
  ('manzana',1),('salmon',2),('sandia',0),('sopa',3),('pera',1),('agua',0)
;

http://sqlfiddle.com/#!9/6aef2/1/0

Looking at the example, the product with name "manzana" (apple) is part of collection "dulce" (sweet), and the product "agua" (water) isn't part of any collection.

What I expect to show is this:

<h1>My products</h1>

dulce /* (ID 1 table `colecciones`) because I have products with "coleccion = 1" */
salado /* (ID 2 table `colecciones`) because I have products with "coleccion = 2" */
sandia /* the product itself, not collection */
agrio /* (ID 3 table `colecciones`) because I have products with "coleccion = 3" */
agua /* the product itself, not collection */

I'm stuck with this. How can I display the data in this way with PHP?

I need to order like productos.id DESC, which I accomplished by adding at the end:

ORDER BY c.`id` DESC

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think an aggregation will do what you want:

select (case when c.name is not null then c.name else p.name end) as name
from productos p left join
     colecciones c
     on p.coleccion = c.id
group by name, (c.name is not null);

The second condition in the group by just ensures that you get two rows when the product and coleccion have the same name.

I also think this will work for your purposes:

select p.name from productos where coleccion = 0
union all
select c.name from colecciones;

You seem to want all products not in a collection plus the collection names (which is what this query does).

EDIT:

I'm not sure which id you want, but you could do:

select p.name, p.id from productos where coleccion = 0
union all
select c.name, c.id from colecciones
order by id desc;

Upvotes: 1

Stephen
Stephen

Reputation: 18964

If the order doesn't matter, just use two queries:

SELECT * FROM productos WHERE coleccion = 0;

This will give you all the products that are not in a collection. Although, I would suggest using NULL instead of 0.

Second query:

SELECT colecciones.* FROM productos
INNER JOIN colecciones on colecciones.id = productos.coleccion
GROUP BY coleccion;

This will give you all of the collections that have products.

Upvotes: 1

Related Questions