I got the sum code to work, and now it is the bane of my coding life. when I throw in a BETWEEN clause in the SQL the $grand_month total doesn’t show up on the dates it is supposed to. It is showing on months where it doesn’t belong.
I don’t mind if there is a better way to do the sum thing, I cannot constrain the sum output inbetween the months because it is not in the while statement. I can’t get my head around this. Does anyone have some example code they could show me to help me to find a way to do this.
$sql_month = "
SELECT jobs_assembly.assemble_qty, jobs.mass,
(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
LEFT JOIN job_names ON jobs.job_names_id = job_names.job_id
WHERE jobs_assembly.assemble_date BETWEEN '$monthStart' AND '$monthEnd'
";
$result_month = mysqli_query($conn, $sql_month);
if (mysqli_num_rows($result_month) > 0) {
$grand_month = '0';
while($row_month = mysqli_fetch_assoc($result_month)) {
$grand_month += $row_month['sum'];
} //while
} //if
if(!empty($grand_month)){ ?>
<p>Assemble Total Mass Month : <?php echo $grand_month; ?> kgs</p>
<?php
}
Insert standard ‘use prepared statements’ response here.
If the goal of the query is just to get the total, why are we selecting the sub values? SELECT the SUM (as in, the actual sum function) and be done with it.
I am selecting qty from jobs_assembly, multiplying it by mass from jobs, and getting a total for each row. Then I am taking all those totals and adding them up to find a grand total WHERE certain conditions are true.
I have tried everything, so if you have a coded example that would be amazing.
assemble_date is a date column eg: 2020-05-12. I need to find the sum of 2 values from 2 related rows from 2 tables, add them together and then find the grand total sum of all similar values within the constrained date range.
Your query doesn’t use the job_names table, for starters. So we throw out the unnecessary join. Then we return only the desired number.
$sql_month = "
SELECT SUM(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
WHERE jobs_assembly.assemble_date BETWEEN ? AND ?
";
Then we should cast the strings as dates.
$sql_month = "
SELECT SUM(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
WHERE jobs_assembly.assemble_date BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)
";
$s = mysqli_prepare($conn, $sql_month);
mysqli_stmt_bind_param($s,'ss',$monthStart,$monthEnd);
mysqli_stmt_execute($s);
mysqli_stmt_bind_result($s,$total);
if(mysqli_stmt_fetch($s)) { ?>
<p>Assemble Total Mass Month : <?php echo $total; ?> kgs</p>
<?php }
Hi m_hutley,
It’s not giving an error, but $total is empty. I don’t know why I had job_names_id in, I don’t need it.
The whole bind_param, smt_execute - that whole script looks so scary and I have no idea how to edit it - I admit, I really have tried to find online tutorials for prepared statements and never found something I actually understood and could implement.
I will try use my regular mysqli_query with $sql_month to see if II can get $total to have a value.
Just want to add, you know that the rows from different tables must be added first, then all those totals must be added later to make a big total.
But it is not showing the correct result in the program.
And June and every month thereafter which should be null show : Assemble Total Mass Month : 101.6 kgs
OMG m_hutley! I found the problem!
THIS → echo $monthStart = $year.'-'.$monthNumber.'01';
is meant to be THIS → echo $monthStart = $year.'-'.$monthNumber.'-01’;`
Pffft. If I, or anyone else who’s been around these forums for any length of time, could count the number of times a tiny typo in one line has caused our entire code to go fubar, it’d be a very very high number. S’why I asked you to show us the whole code - sometimes the best solution is another pair of eyes.
That makes me feel a lot better! I am so relieved you helped me figure this out, thank you not only for taking the time to do that, but you vastly improved my code from what it was before. Also, saying that there aren’t two tables now but 1 joined from 2 really helped me see things around joins very differently.
Yeah, after a while you get a sixth sense for it when your own code misbehaves and you’ll go “I just know it’s going to be something really small and I know I’ll head myself in the head when I find it”.
Tools like Xdebug help immensely here. You can then run your code while you can keep track of what’s happening in your IDE, so for example you can check the values of all variables while the program is running, which makes it real easy to verify your assumptions.
