Still Working On Flickerbooks Totals Calculator

So this time I had an opportunity to try some new things with SQL. For both, payments and deductions, I made it possible to add custom payment/deduction with a user’s provided name:

Now since some payments can have and identical names (for example additional payment 1 can have the same name as additional payment 2), if that is the case I want to group the results by name. Currently each additional payment has a column for name and amount values to be stored, so what I want to do is to group payments and get the sum for it by payment name.

Using SQL Temp Tables

In order to be able to group these 3 name columns and value columns, I have created a temp table with two columns: one to store name and one to store value. Then I combined and INSERTO INTO query with SELECT query 3 times: each time a name and value for payments are selected and inserted into the temp table. Once all data has been added to temp table, I query it to select payment sums by payment names. The code for this is demonstrated below:

	//create temp table
        CREATE TEMPORARY TABLE AdditionalPayments
	(
		addPayName varchar(50),
		addPayValue decimal(6,2)
	);
        //select additional payments 1 values and insert them into temp table
	INSERT INTO AdditionalPayments (addPayName, addPayValue)
	SELECT add_pay_N, add_pay FROM weekly_payments_amount
	WHERE 
		user_id = '$user_id' 
		AND add_pay <> 0
		AND taxPeriodNr BETWEEN '$firstTaxPeriodOfYear' AND '$taxPeriodNumber'
		AND employer = '$employer' 
		AND job_title = '$jobTitle';
        //select additional paymets 2 values and insert them into temp table
	INSERT INTO AdditionalPayments (addPayName, addPayValue)
	SELECT add_pay_N2, add_pay2 FROM weekly_payments_amount
	WHERE 
		user_id = '$user_id'
		AND add_pay2 <> 0
		AND taxPeriodNr BETWEEN '$firstTaxPeriodOfYear' AND '$taxPeriodNumber'
		AND employer = '$employer' 
		AND job_title = '$jobTitle';
         //select additional payments 1 values and insert them into temp table
	INSERT INTO AdditionalPayments (addPayName, addPayValue)
	SELECT add_pay_N3, add_pay3 FROM weekly_payments_amount
	WHERE 
		user_id = '$user_id'
		AND add_pay3 <> 0
		AND taxPeriodNr BETWEEN '$firstTaxPeriodOfYear' AND '$taxPeriodNumber'
		AND employer = '$employer' 
		AND job_title = '$jobTitle';
	SELECT SUM(addPayValue) AS addPaySum, addPayName AS addPayName FROM AdditionalPayments 
	GROUP BY 
                addPayName 
        ORDER BY 
                addPaySum DESC;"

$symbol = "£";
// Execute multi query
if (mysqli_multi_query($weeklyPaymentsDeductions, $queryAddPayPaySums)) {
  do {
    // Store first result set
    if ($result = mysqli_store_result($weeklyPaymentsDeductions)) {
      while ($row = mysqli_fetch_row($result)) {
		 //add names and values to array
		$additionalPaymentsArray[] = array("earnings"=>$row[0], "name"=>$row[1], "symbol"=>$symbol);
      }
      mysqli_free_result($result);
    }
     //Prepare next result set
  } while (mysqli_next_result($weeklyPaymentsDeductions));
}

For the first time I was working with multi queries in PHP, and glad I figured out how to store retrieved data in PHP array. And to sum this up: SQL temp tables do the work: if I run this query on my server I receive these results:

However after using SQL temp tables, I tried another way of achieving this, but it didn’t work out.

Attempt To Use SQL UNION Operator

I have tried to achieve the same results using SQL UNION operator, and ran the query below on my database:

SELECT name, value
	FROM(
		SELECT other_ded_name AS name, other_de AS value 
			FROM weekly_deductions_amount
		WHERE 
			user_id = 1
			AND other_de<>0
			AND taxPeriodNr BETWEEN 1 AND 900
			AND employer = 'Bakkavor' 
			AND job_title = 'Logistics Administrator'
		UNION ALL
		SELECT add_ded_N2, add_ded2
			FROM weekly_deductions_amount
		WHERE 
			user_id = 1 
			AND add_ded2<>0
			AND taxPeriodNr BETWEEN 1 AND 900
			AND employer = 'Bakkavor' 
			AND job_title = 'Logistics Administrator'
		UNION ALL
		SELECT add_ded_N3, add_ded3
			FROM weekly_deductions_amount
		WHERE 
			user_id = 1 
			AND add_ded3<>0
			AND taxPeriodNr BETWEEN 1 AND 900
			AND employer = 'Bakkavor' 
			AND job_title = 'Logistics Administrator'
        ) AS unitedResults	

The result for this query are demonstrated below:

This query does return the results that I wanted to, however if I add a GROUP BY name statement the query, it does make the grouping by name part, however it does not calculate the sum of values for each name, instead it just prints out the first value for each payment.

I don’t want to spend to much time figuring out why is this, and as I already have a working option, I just discarded the idea of using UNION ALL for this matter.

I have started to work on animating the table height, text and chart. It did took me a while to figure out a way to do it, but I came up with something that should work, but a lot of rewriting of the code in JavaScript will have to be done 😉