I have 2 tables. One has the qty and the other the mass. I have left joined these tables so that if there is a qty value, it connects with the correct mass value.
Now I need to multiply the mass by the quantity to create a total - easy enough. But now, I need to take all those totals, add them together to create a grand total of them all. I have no idea how to do this. Here is what I have so far:
$sql = "
SELECT *
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) { ?>
<div class="quote-table">
<table>
<tr>
<th colspan="2" style="border-bottom:0;">Description</th>
<th colspan="2"style="border-bottom:0;">Mass</th>
</tr>
<tr>
<th>Name of Job</th>
<th>Assembly</th>
<th>Qty</th>
<th>Kg</th>
</tr>
<?php
while($row = mysqli_fetch_assoc($result)) {
?>
<tr>
<td><?php echo $row['job_name']; ?></td>
<td><?php echo $row['assembly']; ?></td>
<td><?php echo $assemble_qty = $row['assemble_qty']; ?></td>
<td><?php $assemble_mass = $row['mass'] ?>
<?php
$assemble_mass_total = $assemble_qty * $assemble_mass;
echo $assemble_mass_total;
$assemble_mass_total_array = $assemble_mass_total[];
?>
</td>
</tr>
<?php
} //while ?>
<?php
print_r($assemble_mass_total);
?>
</table>
</div><!---quote-table-->
<?php
} //if
You need to create another variable such as $grand_total before the while() loop starts, and each time you loop through, add the value of $assemble_mass_total to it. Echo it after the end of the loop.
I don’t think this:
$assemble_mass_total = $assemble_qty * $assemble_mass;
echo $assemble_mass_total;
$assemble_mass_total_array = $assemble_mass_total[];
is going to do anything for you. You create a flat variable called $assemble_mass_total, give it a value, display it, then create another flat variable called $assemble_mass_total_array and give it the value of an array called $assemble_mass_total that you didn’t create in the first place. I just get a 500 error when I try to run those three lines. The first two are fine, of course.
The idea is not unreasonable. I guess you were intending to create an array of the total for each row, then calculate the total after the loop. You could do that, but if all you want is the grand total, it seems like overkill to me.
I create a post just now but cannot seem to find it to add to it. Anyhow, I have 2 tables, I need to multiply qty from the one table to the mass from the other to get a total, which I have achieved with this code:
$sql_total_mass = "
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
} //while
} //if
Now I need to take all these totals and make a grand total. So basically add all the results from $row_total_mass[‘sum’] together and show it.
It’s here. I just replied to it: Totalling output of a query
and each time you loop through, add the value of $assemble_mass_total to it
Droopsnoot, thank you for your reply. How would I go about adding each total to the $grand_total variable? I don’t how I would actually do that.
I have merged the 2 topics as we don’t want 2 on the same subject.
Well, you can have already written code in your first post to multiply two variables together and assign the result to another variable, so how would you think you would change that to add them instead?
No, I don’t see how what you are suggesting will work. I need the row totals first, and then add those totals up to create the grand total.
So the idea is this:
qty mass $row_total_mass
1 10 10
2 12 24
1 5 5
3 2 6
grand total: 45
I need to get the value of $row_total_mass first, which is what the sql statement is and what I have done. Then after I have those totals, I need to add them altogether. I don’t know how to add them together afterwards to have a grand total.
Please can someone help me to figure this out, I wouldn’t be here asking if I knew what the answer was and I don’t understand what you are suggesting droopsnoot.
That’s exactly what I am suggesting. Create a new variable and give it a value of zero before your loop opens. Each time the loop runs (i.e. where it did the multiplication in your first post, the one I answered, or where you display the calculation from the query in your second post) add the row total to your new variable, and after the loop has closed, echo your new variable.
By “change that to add them instead”, I meant for your new variable, not to replace your multiplication code. I can’t see how if you know how to multiply two variables together and get the result, you can’t use that knowledge to add to a different variable.
I’ve added a new variable in. That is what I am trying to do and don’t know how to do it. At least you do know what I am trying to achieve, but you are not describing how I am supposed to achieve it. I know I need to add them all to a grand total, but I still don’t know how. It’s ok if you don’t know either., I just really need someone to help out who knows how to do this.
I’ve added the variable that is now assigned 0, but I honestly don’t know what that is achieving.
$sql_total_mass = "
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
$grand_total_mass = 0;
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
} //while
} //if
That’s fine, what it achieves is to create your “grand total” variable and initialise its value. What you now need to do is add another line, inside your loop, which adds the line total to the grand total. So just after this line:
echo $row_total_mass['sum'].'<br />';
add another line that adds the value you just echoed to your new variable. If you look at this line of code from your first post:
$assemble_mass_total = $assemble_qty * $assemble_mass;
you can see in there that you multiply two variables together, and assign the result to the variable on the left. So, how would you think that you might add a value to a variable, based on that line of code?
I do know how, I’m trying not to just write the code out for you, that isn’t really what the forum is for. I’m just struggling with looking at the code that you have written, to then reconcile that with explaining how to add two variables together without sounding patronising (which I apologise for, if I have failed in that last bit). I keep thinking I’m missing something.
I’ve tried this after looking up more solutions online, but it is still not working.
$sql_total_mass = "
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
echo $grand_total_mass = 0;
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
$grand_total_mass += $row_total_mass['sum'];
} //while
} //if
Can you expand on that? What is the value of $grand_total_mass after your while loop has closed?
That should work, as far as I can see. Presumably it’s showing the correct value on each row?
Perhaps you don’t know what I mean. I’ve already got the totals from the sum. I want to take all those totals and add them together to make one final total, just one number for this query, that adds them altogether. So, all the row totals that are produced in the loop to show just one final figure of all of them added up.
No, I understand completely what you are trying to do, I just wanted you to expand on “still not working”. The code you have shown appears to do the totalling, but you don’t actually display the total at the end of your loop. So I was asking whether by “not working” you meant that it’s just not appearing (which is because you don’t echo it), or whether you meant it had the wrong value, or gave an error message, or anything else.
So, in what way does it not work?
I’ve got this now which is giving me the correct total, but is kicking out an error that $grand_total_mass is undefined.
<?php
$sql_total_mass = "
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
//echo $grand_total_mass = 0;
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
$grand_total_mass += $row_total_mass['sum'];
} //while
} //if
echo $grand_total_mass;
Show us the exact error message. If the error message includes a line number, please indicate in your code which line that is.
The only reason I could think of it not liking that variable is if the query didn’t return any results, which you’d probably have mentioned - unless it is complaining because you use it (when you add the first value to it) before creating it. You could get around that by defining the variable prior to the start of your if clause. That way it would exist regardless of whether any results were found.
So get the line you commented out that creates the variable and sets it to zero, lose the echo off the start, and move it to before the if() statement. (ETA - and obviously uncomment it).
Incredibly, I finally searched for the right thing “php how to get sum of outputted queries” which led me to this link: https://stackoverflow.com/questions/49454693/how-do-i-calculate-the-total-of-a-query-result-in-php
And from that compiled the below code which is working. I didn’t know you could += in a loop like that and output the value outside the loop. Setting the variable to 0 did take away the error.
$sql_total_mass = "
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 = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
$grand_total_mass = 0;
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
$grand_total_mass += $row_total_mass['sum'];
} //while
} //if
echo $grand_total_mass;
Excellent, glad it is now working.
Your next challenge is to change that query to use a prepared statement rather than concatenating the date into it like that. I’m sure you’ll have seen references to “Little Bobby Tables” if you’ve been on Stackoverflow (or here) for more than ten minutes, and if you’re new to this, you might as well start off with the right methods.