Compare the rows of a table shown with a while

Question

Compare the rows of a table shown with a while

Navigation

#1 by (0 votes)

0

I am writing a code that shows the data of a table stored in a BD using a While cycle. The table shows 4 data that are ID , id_cliente , Mes de pago , monto .

On a client the While shows me 3 records

ID       id_cliente       Mes de pago        monto
1          25                  06            15000
2          25                  07            20000
3          25                  10            10000

I would like to know if there is any way to compare the months of payment of the different rows, that is, the difference of months between rows 1 and 2, between 2 and 3 and thus the more records there are.

to make known the months in which that customer did not pay, in this case would be the 08 and 09.

I thank those who can help. Greetings.

php while

asked by José Miguel Viña 06.12.2017 в 22:48

source

1 answer

Problem with $ _SERVER ['PHP_SELF'] How to know the classes my Java project uses?

score 0 · Answer 1

Let's see. You have a table pagos with columns id , id_cliente , mes and pago . Let's suppose that the ID is auto-incremental so we can fill it with test data that replicates what you show in your question:

INSERT INTO pagos (id_cliente, mes, monto) VALUES (25,'06',15000);
INSERT INTO pagos (id_cliente, mes, monto) VALUES (25,'07',20000);
INSERT INTO pagos (id_cliente, mes, monto) VALUES (25,'10',10000);

You would have to make an auxiliary table of months:

CREATE TABLE IF NOT EXISTS meses (mes varchar(2));
INSERT INTO meses (mes) VALUES 
           ('01'),('02'),('03'),('04'),
           ('05'),('06'),('07'),('08'),
           ('09'),('10'),('11'),('12');

And assuming you have a table usuarios with a field id where the user 25 exists, the following query:

select meses.mes, 
       pagos.id,
       usuarios.id as id_usuario,
       pagos.monto
FROM
meses join usuarios on 1=1
left join pagos ON meses.mes=pagos.mes AND usuarios.id=pagos.id_cliente
WHERE usuarios.id=25
order by mes ASC

I would give you every month, the id of user 25, and the payments, showing NULL where there are no payments:

Already with that you can see what other clauses to put (put zeros instead of nulls, or restrict what months show, etc)

Fiddle