PHP and Mysql trying to get sum value between dates

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
		}

What are the values of $monthStart and $monthEnd?

Can you expand on that statement? I’m not sure what you mean by it.

What type of column is assemble_date? Is it a date, or a datetime, or something else?

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.

$monthStart will be for eg: ‘2020-05-01’ $monthEnd will be for eg: ‘2020-05-31’

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.

I can send more code and my tables to look at.

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.

Thank you very kindly for your help so far!!

What different tables? You’ve got one table - the linked tables of jobs and jobs_assembly.

What happens when you run the query against your database directly?

I gives me the correct value for that date range. I tried it with April and June and it returned the correct results every time.

But it is not showing the correct result in the program.
image
And June and every month thereafter which should be null show : Assemble Total Mass Month : 101.6 kgs

well PHP doesnt magically change the query result, so it’s obviously something in your PHP.

Show us the whole code for your page. Something is going wrong or not running the query properly.

OMG m_hutley! I found the problem!
THIS ->
echo $monthStart = $year.'-'.$monthNumber.'01';
is meant to be THIS ->
echo $monthStart = $year.'-'.$monthNumber.'-01';`

yeah, that’ll do it.

I’m traumatised …

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.

3 Likes

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.

2 Likes

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.

Also, unit tests help too :slight_smile:

1 Like