Product blocked by sale limete

I can think of a solution in the following way:

• a cantidad field that could be in the ventas table or another. That will depend on the structure of your database. Here I have put it in the same table ventas to simplify.
• you apply the SUM function on that field to calculate the quantity of products sold.
• you use GROUP BY so that the sum is made taking into account two criteria: id_pro and fecha_venta , so that you add the sales of each product by specific date.
• you finally use HAVING to evaluate that amount is less than 20,000

I leave this example, if you want to apply more filters as WHERE fecha_venta = '....' add more columns in the SELECT, put the field cantidad in another table, etc, you can do without problems. That will depend on your needs. The goal is to see how it works, analyzing the Query 1 .

You will see in the INSERT that I have put two rice sales of 10,000 each on May 1, and you will see that the rice product does not come out in the query that day. :)

EDIT INCLUDING PRODUCTS NOT SOLD

DEMO SQL Fiddle

MySQL 5.6 Schema Setup :

DROP TABLE IF EXISTS producto; 
CREATE TABLE producto
  ( id_pro int(11) NOT NULL AUTO_INCREMENT, 
    nombre_pro varchar(250) DEFAULT NULL,  
    imagen_pro varchar(250) DEFAULT NULL,  
    status_pro varchar(10) DEFAULT NULL, PRIMARY KEY (id_pro) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO producto (id_pro,nombre_pro) 
  VALUES 
  (1,"Arroz"),
  (2,"Carne"),
  (3,"No vendido") 
  ;


DROP TABLE IF EXISTS ventas; 
CREATE TABLE ventas
  ( id_ventas int(11) NOT NULL AUTO_INCREMENT,  
    id_producto int(11) DEFAULT NULL,  
    fecha_venta date DEFAULT NULL, 
    monto_venta decimal(19,2) DEFAULT NULL, 
    cantidad int DEFAULT NULL, 
    PRIMARY KEY (id_ventas) 
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 
  ventas (id_ventas, id_producto, fecha_venta, monto_venta, cantidad)
  VALUES 
  (1,1,'2017-05-01',1000.00,10000),
  (2,1,'2017-05-01',1000.00,10000),
  (3,1,'2017-05-02',700.00,7000),
  (4,2,'2017-05-01',10.00,1),
  (5,2,'2017-05-01',350.00,33),
  (6,2,'2017-05-02',723.00,89);

Query 1 :

SELECT p.nombre_pro, v.fecha_venta, SUM(v.cantidad) AS total  FROM
producto p
LEFT JOIN ventas v 
ON p.id_pro=v.id_producto
GROUP BY p.id_pro,v.fecha_venta
HAVING total < 20000 OR total IS NULL

Results :

| nombre_pro |           fecha_venta |  total |
|------------|-----------------------|--------|
|      Arroz | May, 02 2017 00:00:00 |   7000 |
|      Carne | May, 01 2017 00:00:00 |     34 |
|      Carne | May, 02 2017 00:00:00 |     89 |
| No vendido |                (null) | (null) |

You need an additional field to tell how many products you have in your stock or inventory. This field has to be of numeric value (numeric) or integer (int).

When the value of that product is 0. You prepare the impossibility of buying this product something like ..

<?php if ($producto[x] == 0) { $mensaje = "No hay existencia de stock";  }; ?>

Then you will send that to where you correspond that message according to your code infrastructure.

If you need more reference do not forget to do it. We can also offer you what you ask for if you exceed 2000:

    <?php if ($producto[x] >= 2000) { /* Aqui la instruccion de saltarse.  */ }; ?>

and if you are looping .. then

        <?php if ($producto[x] >= 2000) { continue; }; ?>

This with the intention that you make the jump to print on your template or view (mvc) the product.

1) Create an additional field called sales quantity in your product table, which is of integer value (int) and non-zero value.

2) Prepare a MYSQL statement similar to the one I will offer below to know how much the product has:

SELECT cantidad_de_ventas FROM tabla_de_productos WHERE '$elnombre_de_mi_producto'.

Translation: Select the field quantity_of_sales from the table "product_table" with the value '$ the_name_of_my_product. "

3) Then when the sales are added up and they are made add a sentence something like

INSERT INTO tabla_de_productos (cantidad_de_ventas) Values ('$elvalor_actual + 1')

The variable $ actual_value is the one that we will obtain with the previous mysql statement. and of course it is added 1 ... Now ....

You add a conditional in your php to check if the product can be displayed

        <?php if ($producto[x] >= 2000 ) { continue }; ?>

This is because you use a loop as a foreach or while to print that list of products and you will skip it.