Distinct values

Asked

Viewed 146 times

-1

SELECT `forma`,`id_produto`,`id_lanche`,`qtd_max`, 
       if(`id_lanche` in (select `id_lanche` from `produtos` 
            where `id_lanche` = {$id_produto}),'checked','') as marca FROM `formas`

db product = products

db revenue = forms

This code does the following job: Search in db o id of the product and the recipe it is. And mark the ingredients and their amount.

id     nome      id_lanche    preço
1     X-egg       1            7.00
2     X-tudo      2            9.00

tabela receita
id    id_produto  id_lanche    preço     quantidade (quantiodade no caso e quanto o produto tem de cada item)
1     1           1            1.00      1
2     2           1            2.00      2
3     3           1            2.00      2
4     4           1            3.00      1
5     1           2            1.00      1
6     2           2            2.00      2
7     5           2            2.00      1
8     4           2            3.00      1



Alface         [X] 1x  [ ] 2x  [ ] 3x
Bacon          [ ] 1x  [X] 2x  [ ] 3x
Queijo Cheddar [ ] 1x  [X] 2x  [ ] 3x
Mostarda dijon [X] 1x  [ ] 2x  [ ] 3x
bife           [ ] 1x  [ ] 2x  [ ] 3x
pão            [ ] 1x  [ ] 2x  [ ] 3x
Alface         [ ] 1x  [ ] 2x  [ ] 3x
Bacon          [ ] 1x  [ ] 2x  [ ] 3x

Now I need him to also eliminate the duplicate elements... but watching whether he’s marked or not... I put a group by forms. But it does not distinguish whether this marked or not.

php mysql

  • In the case of the table formas is the first there?

  • the table forms and the recipe table

  • What is the representation of those markings there Alface [X] 1x [ ] 2x [ ] 3x? If possible put the result of your query.

  • by this cunsulta says that the id_lanche 1 has the form (recipe) of the id_lanche in this case are 4 items there see what the quantity of each item and marks the checkbox corresponding to quantity

  • only that the problem is that it is printing 2 lettuce pq has 2 lettuces in db and he has to print only one lettuce... but observing and giving priority to what is marked and eliminating what is not marked.. if not even a marked item it prints any one

  • Right, you have to ask a question every time, in the question you are mixing the SQL query with the logic of printing, then it is difficult for us to help you identify the problem.

  • 1º What is the reason for having 2 lettuces in the bank? 2º Put the result of your query (the same that you put in the question). 3º Place also a representation of the products table.

  • has 2 lettuces pq has the id_lanche of the form table, it matches with the snack id_lanche of the product table... and assembles the recipe... and has the quantity of the item

  • @Kaduamaral, I finished editing the post... ta printing like product 1 x-Egg

  • See if you can simulate the problem here Sqlfiddle, creates a simulation of your data and tables and query.

  • A simple Union of shapes with products and count the quantity of products would not be simpler !?

  • more has what is marked

Show 7 more comments

1 answer

0

This way it doesn’t work?

SELECT `forma`,`id_produto`,`id_lanche`,`qtd_max`, 
       if(`id_lanche` in (select `id_lanche` from `produtos` 
            where `id_lanche` = {$id_produto}),'checked','') as marca FROM `formas`
group by `forma`,`id_produto`,`id_lanche`,`qtd_max`, 
       if(`id_lanche` in (select `id_lanche` from `produtos` 
            where `id_lanche` = {$id_produto}),'checked','');

  • he just organizes the search in alphabetical order... still prints 2 lettuce

  • he has to identify which lettuce is marked and which is not... which is not he eliminates... if he has not even one marked he puts any one... plus all different values

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.