CODEWITHSUNDEEP
phpmysql
Rosamunda
Rosamunda

Reputation: 14980

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?

Upvotes: 1

Views: 56

Answers (3)

Jakob
Jakob

Reputation: 3546

You need to select data where nombreCurso is 'alfa' and group by it.
And also in select do sum of pagoMonto.

Then in PHP just retrieve cursoPrecio and sum of pagoMonto and do calculation.

Full code is below: 

<?php
// Insert your data for mysql connection
$servername = "";
$username = "";
$password = "";
$database = "";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$sql = "SELECT pagosVerificados.pagoID AS pagoid, SUM(pagos.pagoMonto) AS pmSum, 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 
        AND cursos.nombreCurso = 'alfa' 
        GROUP BY cursos.nombreCurso";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo $row["cursoPrecio"].'<br/>';
        echo '-'.$row["pmSum"].'<br/>';
        echo $row["cursoPrecio"] - $row["pmSum"];
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

Upvotes: 1

bassxzero
bassxzero

Reputation: 5041

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

Upvotes: 1

Amazone
Amazone

Reputation: 436

First, in your query it's better to put:

ORDER BY `cursoID` ASC

After, your array must be put in a loop:

foreach($myarray as $line){
  if(!$precio$line['cursoID'])) {
    $precio$line['cursoID'] = $line['cursoPrecio'];
    $list[]= $line['cursoID'];
  }
  $precio$line['cursoID'] -= $line['pagoMonto'];
} 
foreach($list as $thing){
  echo 'To pay for Curso '.$thing.': '.$precio$thing.'<br>';
}

Upvotes: 1

Related Questions