Print result if all rows match PHP MysQl

Question

Print result if all rows match PHP MysQl

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

3

I have a problem, I'm a PHP starter, I have a table where I keep the items that contain an order.

Each item is marked as YES, if it has already been distributed, and it is marked as YES if it has already been delivered, since this may happen another day.

What I need is to know how to make the query and how to print a button for example, if all the items are = YES and YES then a button that says CLOSE THE SALE, since they were all delivered and delivered.

oder_product

+-----+-----+-----+-----+-----+------+
| id | item_id | reparto | entregado |
======================================
| 1  |    1    |   SI    |    NO    |  
======================================

======================================
| 2  |    2    |   SI    |   SI    | 
======================================
| 3  |    2    |   SI    |   SI    | 
======================================
| 4  |    2    |   SI    |   SI    | 
======================================
| 5  |    2    |   SI    |   SI    | 
======================================

======================================
| 6  |    3    |   SI    |    NO    | 
======================================
| 7  |    3    |   SI    |    SI    | 
======================================

and well I'm stuck from here, I can select all, but how does PHP know they are all really?

SELECT * FROM oder_product WHERE reparto="SI" AND entregado="SI" AND item_id="2";

PHP I think it would be something like this:

If result=all rows match;
Echo' Todos coinciden AQUI BOTON CERRAR VENTA';

else
Echo' No todos coinciden, faltan $result2 para coincidir;

I hope to have explained myself well, I take help in MySql syntax if it uses a type of COUNT and in PHP.

mysql php

asked by MrCongo 21.03.2016 в 19:01

source

3 answers

Creating api in node I get error: bodyparse is not defined The port is occupied when pressing CTRL + C from the linux terminal with Nodejs applications

score 2 · Answer 1

It's always good to avoid nested queries, so I'd recommend something like:

select IF(SUM(IF(reparto = 'SI' AND entregado = 'SI', 1, 0) = COUNT(*), TRUE, FALSE) as cerrada from order_producto GROUP BY item_id;

and then just buy over the field cerrada

score 0 · Answer 2

You could do two queries as Toni suggests in your answer, or put the two as subqueries within the same query:

SELECT (SELECT COUNT(id) FROM oder_product WHERE item_id = '1') AS num_item,
       (SELECT COUNT(id) FROM oder_product WHERE item_id = '1' AND reparto = 'SI' AND entregado = 'SI') AS num_sies

If the value of num_item and num_sies match, then all the boxes were "YES" and the button to close the window should be displayed.

Or you could simplify if you focus on focusing the problem in a different way: you should show the button if none of the rows of the product has "NO" in distribution or delivery . That is a much simpler query:

SELECT COUNT(*) FROM oder_product WHERE item_id="2" AND (reparto="NO" OR entregado="NO");

If the result of COUNT(*) is greater than zero, that means that some of the rows have a "NO" and the close window button should not be shown. (NOTE: This would be taking into account that the item_id is valid and exists in the table, if it does not exist, the result will always be 0).

score 0 · Answer 3

One option that occurred to me would be to make two queries counting the resulting records, one with the example condition:

SELECT count(*) FROM oder_product WHERE reparto="SI" AND entregado="SI"

and another without condition

SELECT count(*) FROM oder_product;

and if you match the button.