Sum of different values received using Array in query of foreach loop


#1

I want to have sum of each value of brkgst_no got by array $mondates, where $mondates are the dates in Y-m-d format of all Mondays of current month. The code is below and not giving the correct sum of values received.

$ttl_valid_spl_monbrk_gstmls = 0;
foreach ($mondates as $mondate) {
$query = “Select brkgst_no
from pin_mls_detail
where brkgst_no != ‘’ AND pin_pin = ‘$pinid’ AND date_pin IN(”.implode(’,’,$mondates).")";
//print_r($query);

$sql = mysql_query($query);
$rows = mysql_fetch_array($sql);
while($rows = mysql_fetch_array($sql))
{
$ttl_valid_spl_monbrk_gstmls = $rows[‘brkgst_no’];
$ttl_valid_spl_monbrk_gstmls++;
}
}
echo $ttl_valid_spl_monbrk_gstmls;
It is showing wrong sum value.
I am very confused about Php Arrays.I hate working with them.Please help.


#2

Before you do anything you need to stop using obsolete and dangerous MySQL code. You need to use PDO with Prepared Statements.

This tutorial will get you going https://phpdelusions.net/pdo


#3

Yes, you are right. but my website is programmed few years ago in PHP 5.6. For that I have to check whole website. Before that I want to complete little additions in it and after that I will change it because it is a cumbersome task. I think I have to correct each mysql query in whole website. Also I am using Ioncube encoded website. Also tell me does PHP 5.6 supports PDO ?
So If any body can give solution to this code it will be appreciated. After that I will be free to convert whole website.
Thanks


#4

In your last thread, you learned that you can get a count of matching rows using the mysql COUNT() function in the query.

There’s also a way you can sum data in the query. See this link - https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_sum

The sql query would look like -

$query = “Select SUM(brkgst_no) as total
from pin_mls_detail
where brkgst_no != ‘’ AND pin_pin = ‘$pinid’ AND date_pin IN(”.implode(’,’,$mondates).")";

Unfortunately, because you didn’t post your code using bbcode code tags or as preformatted text, the forum software reformatted it as text and converted the quotes to ‘smart’ quotes, so, you won’t be able to just copy the above and use it.

You would just execute the query and fetch the $row[‘total’] value. And you still are using an unnecessary foreach() loop. All this loop is doing is executing the same exact query over and over.


#5

Thanks again for your valuable advice and help. I removed the Foreach loop from my previous code as well. Both previous and this code works well. There is no issue.
I want to share something: Actually the programmer who made my website was novice, inexperienced and confused about Php and what he did is actually made my whole website in my presence and made me confused too. After learning little bit of PHP I concluded that his approach was totally wrong. So first impression is the last impression. I need to start learning from a professional right from start and go through Php OOPS etc.
But you helped me a lot.I am indebted to you. Thanks for that again. The corrected code is below:

$ttl_valid_spl_monbrk_gstmls = 0;

$query ="Select SUM(brkgst_no) as total from pin_mls_detail where brkgst_no != '' AND pin_pin = '$pinid' AND date_pin IN(".implode(',',$mondates).")";

//print_r($query);

$sql = mysql_query($query);

$rows = mysql_fetch_array($sql);

$ttl_valid_spl_monbrk_gstmls = $rows['total'];

echo $ttl_valid_spl_monbrk_gstmls;