Get the sum of all the hours

Question

I am doing a project with PHP and MySQL. I have this problem.

This is my code

<?php 

$proyecto = $_POST['id'];

$servername = "localhost";
$username = "dbuser";
$password = "dbpass";
$dbname = "proyectos";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT `horas`, `trabajador` FROM `horas` WHERE `proyecto` LIKE '$proyecto' ";
$result = $conn->query($sql);
$conn->close();

?>

It takes a parameter from a post request and do a search in the database database looks like this:

So I want to get as result the sum of all the hours (horas column) that are made by the same worker (trabajador column). Example of result:

Prueba1: 8 hours in total, Prueba2: 9 hours in total

I am stuck trying to dinf they way to sum when 1 or more fields must be the same, I hope someone can help me with this. Thanks!

Answer 1

You must use sum function to add the number of hours for each worker along with GROUP BY clause to group workers.Formatted Query is like:

SELECT SUM(horas) AS Hours,`trabajador` 
FROM `horas` 
WHERE `proyecto` 
LIKE '%".$proyecto."%' 
GROUP BY `trabajador`

In your code,

$select = "
    SELECT SUM(horas) AS Hours, `trabajador` 
    FROM `horas` 
    WHERE `proyecto` LIKE ? 
    GROUP BY `trabajador`
";

$sth = $conn->prepare($select);
$sth->execute(['%'.$proyecto.'%']);

/* Fetch all of the remaining rows in the result set */
print("Fetch all rows in the result set:\n");
$result = $sth->fetchAll(\PDO::FETCH_ASSOC);
print_r($result);

Note: You better switch to MYSQL prepared statements to keep your data secure and for better database connectivity practices.

Answer 2

Note answer by maniksidana explains how to use SUM() and GROUP BY and is in general valid. However, it mixes mysqli and PDO approches. Here you have sample how to use it with mysqli (as your question uses it) and why it's important to use prepared statements at all. Just add some dummy data to your table end execute it. Personally I'd suggest to go with PDO only instead, but it's matter of taste.

INSERT INTO `horas` (`fecha`, `horas`, `proyecto`, `trabajador`) VALUES
 ('2020-08-08', 3, 'foo bar baz', 'Joker1'),
 ('2020-08-09', 4, 'ello pomello', 'Joker2');

<?php 

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$proyecto = "ProyectpDePrueba'; DELETE FROM horas WHERE 1; -- bye bye data";
$proyecto = "ProyectpDePrueba";
$proyecto = "ProyectpDePrueba' OR 1=1 -- no more execution";


// Wrong
$sql = "
    SELECT SUM(horas) AS Hours, `trabajador` 
    FROM `horas` 
    WHERE `proyecto` LIKE '$proyecto'
    GROUP BY `trabajador`
";
$result = $conn->query($sql);
echo '<pre>Wrong' . PHP_EOL;
while ($row = mysqli_fetch_assoc($result)) {
    print_r($row);
}

// Correct
$sql = "
    SELECT SUM(horas) AS Hours, `trabajador` 
    FROM `horas` 
    WHERE `proyecto` LIKE ? 
    GROUP BY `trabajador`
";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $proyecto);
$stmt->execute();
$result = $stmt->get_result();
echo PHP_EOL . 'Corrcet' . PHP_EOL;
while ($row = $result->fetch_assoc()) {
    print_r($row);
}


$conn->close();