Erblin Derguti Erblin Derguti - 1 year ago 136
MySQL Question

Need help building a QUERY in sql

Need help building a query in sql.

I have it and its working, but I also need to have the total amount of rows calculated for specific date.

$queryPR = "SELECT * FROM `raport` WHERE DATE(`timestamp`) = CURDATE() AND (lokacioni = 'PR')";


I need also to put on query the
SUM('pagesat')
somehow but I have no idea how.

Find the whole code below, maybe its all written wrong.

I am trying to build something simple to show the income and outcome for 3 locations that we have.

<table class="responsive-table">
<thead>
<tr>
<th>Vetura</th>
<th>Targa</th>
<th>Dite</th>
<th>Pagesa</th>
<th>Cmimi Ditore</th>
<th>Vazhdim</th>
<th>Te Tjera</th>
<th>Shpenzime</th>
<th>Komente</th>
</tr>
</thead>

<tbody>
<?php

$query = "SELECT * FROM `raport` WHERE DATE(`timestamp`) = CURDATE() AND (lokacioni = 'FR')";

$stmt = $DBcon->prepare( $query );
$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){

?>
<tr>
<td><?php echo $row['vetura']; ?></td>
<td><?php echo $row['targa']; ?></td>
<td><?php echo $row['dite']; ?></td>
<td><?php echo $row['pagesa']; ?>€</td>
<td><?php echo $row['cmimiditore']; ?>€</td>
<td><?php echo $row['vazhdim']; ?>€</td>
<td><?php echo $row['tetjera']; ?>€</td>
<td><?php echo $row['shpenzime']; ?>€</td>
<td><?php echo $row['koment']; ?></td>
</tr>
<?php
$pagesa = $row['pagesa'];
$tetjera = $row['tetjera'];
$vazhdime = $row['vazhdim'];
$shpenzime = $row['shpenzime'];
}?>
</tbody>
</table>

<?php


$totali = ($pagesa + $vazhdime + $tetjera);
$gtotal = ($totali - $shpenzime);



?>
<div class="card grey">
<hgroup class="totalDitorRaport">
<h2>Total</h2>
<h1><?php echo $gtotal; ?>€</h1>
</hgroup>
</div>

Answer Source

try this

get the sum of the row pagesa in the loop

$queryPR = "SELECT * FROM `raport` WHERE DATE(`timestamp`) = CURDATE() AND (lokacioni = 'PR')";

$stmt = $DBcon->prepare( $query );
                $stmt->execute();

   $sum = 0;
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){

 $sum += $row['pagesa'];

 }
echo $sum;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download