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!