Myslqi query of two tables with sum of records

2

Good to have if I can explain everything; I have two tables in the BD:

productos

+----+----------+--------+
| id | producto | estado |
+----+----------+--------+
|    |          |        |
|    |          |        |
+----+----------+--------+
  • id = incremental auto record number
  • product = product name
  • status = simply is if that product is active on the web, with 0 and 1.

referencias

+----+-----------+------------+---------------+
| id |idProducto | referencia | estadoMaquina | 
+----+-----------+------------+---------------+
|    |           |            |               |
|    |           |            |               |
+----+-----------+------------+---------------+
  • id = incrementing auto registration number
  • idProduct = this id is the one that links to the id of the product table, to group the references
  • reference = the reference of the machine.
  • machineState = Here we say in what state this reference is in this way;
    0 = Available
    1 = Rented (occupied)
    2 = Sold (occupied)
    3 = Shared Exp. (Occupied)

Then we have a table that takes and is missing to get some data.

 | Producto | Cantidad | Ocupadas | Disponibles | Estado |

Product - > We take out the name.
Quantity - > We thank a teammate how many machines there are with that product id.
Busy - > There you have to find out how many references of that machine are with the status Machine 1,2,3.
Available - > Here, get as many references of this machine is with the machine state 0.

Then the query to remove the while cycle and show the results is like this: (Thanks to the partner @ CristianS9)

<?php
   //var_dump($_GET);
   $results = $mysqli->query("SELECT productos.id, productos.producto AS Producto,productos.alias AS Alias,count(idProducto) as Cantidad,estado AS Estado
   FROM referencias
   INNER JOIN productos 
   ON referencias.idProducto=productos.id
   GROUP BY idProducto,estado");                  
      mysqli_set_charset("utf8");
      while($res = $results->fetch_array()) {
?>

So now I'm trying to find out how many are available and I do it like this:

<?php 
  $resultas = $mysqli->query("
  SELECT productos.id, productos.producto, referencias.idProducto, referencias.estadoMaquina, count(idProducto) as total
  FROM productos
  INNER JOIN referencias
  ON productos.id = referencias.idProducto AND estadoMaquina = 0        
  GROUP BY idProducto, estadoMaquina" );
    $data=mysqli_fetch_assoc($resultas);
    echo $data[total];
?>

But doing it like that, he always pulls out all the available ones from the first id that there are 4 available and repeats it to me in the following registers. The good thing is that I run it in PHPMyadmin and I get the query right, I do not know if it's because this query is in a while.

These are the results I would have to show:

+----------+----------+----------+-------------+--------|
| Producto | Cantidad | Ocupadas | Disponibles | Estado |
+----------+----------+----------+-------------+--------|
| Maquina1 |     5    |    1     |      4      |   1    |
| Maquina2 |     5    |    2     |      3      |   1    |
| Maquina3 |     2    |    0     |      2      |   0    |
+----------+----------+----------+-------------+--------|

And this throws me:

+----------+----------+----------+-------------+--------|
| Producto | Cantidad | Ocupadas | Disponibles | Estado |
+----------+----------+----------+-------------+--------|
| Maquina1 |     5    |          |      4      |   1    |
| Maquina2 |     5    |          |      4      |   1    |
| Maquina3 |     2    |          |      4      |   0    |
+----------+----------+----------+-------------+--------|

In busy does not show anything because I'm doing the available.

Any suggestions, thanks!

    
asked by Miguel 20.08.2018 в 09:08
source

1 answer

1
SELECT 
    referencias.id,
    productos.producto,
    SUM(estadoMaquina=0) AS disponibles,
    SUM(estadoMaquina=1 || estadoMaquina=2 || estadoMaquina=3) AS ocupados,
    estado
FROM referencias
INNER JOIN productos ON idProducto=productos.id
GROUP BY idProducto

You can add the columns you need but the basic action is summarized here: Using a GROUP BY group them for each product. Taking advantage of this action, make a sum of all the machines that are in estadoMaquina=0 and show it as available and the same but this time add the rest of the states and show them as occupied.

    
answered by 21.08.2018 / 08:18
source