Rosamunda Rosamunda - 4 months ago 7
PHP Question

How to sum a set of results from a MySQL database and avoid getting duplicates in PHP?

I have users that can make individual payments for some courses.
I have a table for users, a table for payments, a table for verified Payments (pagosVerificados), a table for courses, and for courses that the users have taken. Each course includes a field for the course's price.

I'm trying to do a database query that shows all the payments of each user. Each payment is attached to a specific course.

Now, the problem is that if I do a list of payments and some users make more than one payment for a single course the course gets repeated on the query.

So how may I use that query to get the total amount of each course and then rest from that each individual payment?

This is how the database is configured:



CREATE TABLE cursos (
cursoID int unsigned not null auto_increment primary key,
nombreCurso char(100) not null,
cursoPrecio int(10) null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE cursosUsuarios (
cursosUsuariosID int unsigned not null auto_increment primary key,
userID int not null,
cursoID int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE pagos (
pagoID int unsigned not null auto_increment primary key,
userID int not null,
pagoMonto int null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE pagosVerificados (
pagosVerificadosID int unsigned not null auto_increment primary key,
userID int not null,
pagoID int not null,
cursoID int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


This is the query:



SELECT pagosVerificados.pagoID AS pagoid, pagos.pagoMonto,
cursosUsuarios.userID,
cursos.cursoID, cursos.nombreCurso, cursos.cursoPrecio
FROM pagos JOIN pagosVerificados
ON pagos.pagoID = pagosVerificados.pagoID
LEFT JOIN cursosUsuarios
ON pagosVerificados.cursoID = cursosUsuarios.cursoID
JOIN cursos
ON cursosUsuarios.cursoID = cursos.cursoID
WHERE cursosUsuarios.userID = 16


This is the result I get:



pagoID --- pagoMonto ---- userID -- cursoPrecio -- cursoID - nombreCurso
3 -------- 200 ---------- 16 ------ 5000 --------- 15 ------ alfa
11 ------- 25 ----------- 16 ------ 5000 --------- 15 ------ alfa
12 ------- 50 ----------- 16 ------ 8000 --------- 18 ------ zeta


Now, if I want to use PHP to do this:

Alfa
5000
-200
-25
======
4775


How may I do that?

Answer

I'm not going to lie, I have no idea what your table names or columns mean but this is my best guess. Use this below as a subquery then join to your other tables to get the course name and total price of the course.

select sum(A.pagoMonto), A.userID, B.cursoID
from pagos A inner join pagosVerificados B 
on ON A.pagoID = B.pagoID
Where B.userID = 16
GROUP BY B.cursoID,A.userID
Comments