Q:

Trying to eliminate Temporary Tables MYSQL and combine process into one statement if possible

I have an application that is tracking sales for 11 salesmen.  This is a simple enough process except that sales can be shared amongst salesman which cuts their sale value in half.  Meaning if two salesmen share a job that has a value of $100 then each salesman only counts $50 sales value for themselves.  Below is the code I am currently using to accomplish this process but it seems clunky and performance is a bit sluggish for my taste.  Is it possible to combine this into a single process and eliminate the need for a Temporary Table (I also saw somewhere you shouldn't use Temporary Tables in production)
$sql = "DROP TEMPORARY TABLE IF EXISTS newbalancetbl" ;
  mysqli_query ($db, $sql ) or ( "Error " . mysqli_error () ) ;

  $newBalances = "
    CREATE TEMPORARY TABLE newbalancetbl (
      `custid` int NOT NULL,
      `assigned` int NOT NULL,
      `newBalance` double,
      PRIMARY KEY(custid)
    )
  ";

   mysqli_query($db, $newBalances) or die ("Sql error : ".mysqli_error());

  $year = date("Y");
  $start = "01/01/".$year;
  $today = date("Y-m-d");
  $first = $year."-01-01";

  $assignments = "SELECT leadid, price
                  FROM jobbooktbl 
                  WHERE convertdate >= '".$first."' AND convertdate<='".$today."' AND (status=4 OR status=6 OR status=7 OR status=8 OR status=11)";
  $assignmentsqry = mysqli_query($db,$assignments);
  while ($row = mysqli_fetch_array($assignmentsqry)) {
    $custid = $row["leadid"];
    $price = $row["price"];

    $statement = $db->prepare("INSERT INTO newbalancetbl (custid, newBalance) VALUES (?,?)");
    $statement->bind_param('id', $custid, $price);
    $statement->execute();
  }

  $sqlnewbal = "SELECT a.custid, COUNT(a.custid) AS assCnt
                  FROM assignmentstbl a, newbalancetbl b
                  WHERE a.custid=b.custid
                  GROUP BY a.custid";
  $qrynewbal = mysqli_query($db,$sqlnewbal);
  while ($row = mysqli_fetch_array($qrynewbal)) {
    $custid = $row['custid'];
    // $paid = $row["sumAmnt"];
    $assigned = $row['assCnt'];

    $usqlUpdate = $db->prepare("UPDATE newbalancetbl SET assigned=? WHERE custid=?");
    $usqlUpdate->bind_param('ii',$assigned,$custid);
    $usqlUpdate->execute();
  }

  $sqlnewbal = "SELECT *
                  FROM newbalancetbl";
  $qrynewbal = mysqli_query($db,$sqlnewbal);
  while ($row = mysqli_fetch_array($qrynewbal)) {
    $custid = $row['custid'];
    $assigned = $row['assigned'];
    $newBalance = $row['newBalance'];
    $newBal = $newBalance/$assigned;
    $newBal - number_format($newBal,2);

    $usqlUpdate = $db->prepare("UPDATE newbalancetbl SET newBalance=? WHERE custid=?");
    $usqlUpdate->bind_param('di',$newBal,$custid);
    $usqlUpdate->execute();
  }

  $salesArray = [];

  $tesql = "SELECT SUM(n.newBalance) AS newB, u.username
            FROM newbalancetbl n
            INNER JOIN assignmentstbl a 
              ON a.custid=n.custid 
            INNER JOIN usertbl u 
              ON a.userid=u.userid 
            -- WHERE u.salesman=1
            GROUP BY a.userid
            ORDER BY newB DESC";
  $teresult = mysqli_query($db,$tesql);
  while ($row = mysqli_fetch_array($teresult)) {
    $user = $row['username'];
    $sales = $row['newB'];

    array_push($salesArray, [$user,floatval($sales)]);
  }

  $arrayCount = count($salesArray);

  $total_sales = 0;
  $total_sales = array_sum( array_map(function($element){
                  return $element[1];
                }, 
             $salesArray));
  $pretotal_sales = number_format($total_sales, 2);
  $total_sales = '$' . number_format($total_sales, 2);

Table Schema:
jobbooktbl

assignmentstbl

A:

I will go over the code to understand what it does, and suggest modifications along the way.
CREATE TEMPORARY TABLE newbalancetbl (
  custid     int NOT NULL,
  assigned   int NOT NULL,
  newBalance double,
  PRIMARY KEY(custid)
) ENGINE=Memory

OK. The table seems small, so you might want to use ENGINE=Memory to make it faster. Also, consider using a DECIMAL type instead of DOUBLE. This is not mandatory, but it avoids rounding error headaches.
Anyway. Your first query. Filling your table using php while() loops is slow and unnecessary. Simply do:
INSERT INTO newbalancetbl (custid, newBalance)
SELECT leadid, price
FROM jobbooktbl 
WHERE convertdate BETWEEN '$first' AND '$today' 
AND status IN (4,6,7,8,11);

Note the use of IN() which is more readable. Also, the BETWEEN. INSERT INTO SELECT will be much faster than looping through query results in php. Also, I don't see "assigned" and "newBalance" in the inserted columns, yet the table does not specify any default values. You should make the default value explicit.
Now, the next query is:
SELECT custid, COUNT(*) AS assCnt
FROM assignmentstbl a JOIN newbalancetbl b USING (custid)
GROUP BY custid

I changed the ugly old JOIN syntax with the correct one (since about 1999). Also, COUNT(col) counts the rows where "col" is not null. So COUNT(a.custid) it implies that "a.custid" can actually be null. Since it can't, this syntax is just confusing. I replace it with count(*).
Then you look over the results in PHP, and do "UPDATE newbalancetbl SET assigned=$assCnt WHERE custid=?"
You should decide whether to use the name "assCnt" or "assigned". I like the first one better, since it is a count, having "cnt" in the name makes it less confusing. Now, this loop is unnecessary, we could use a single UPDATE with a JOIN, or better, build the values into the rable right from the start. Thus, the first query becomes:
INSERT INTO newbalancetbl (custid, newBalance, assigned)
SELECT j.leadid, j.price, 
    (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS assigned
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '$first' AND '$today' 
AND j.status IN (4,6,7,8,11);

I used a subselect. Feel free to use a JOIN instead.
Next query. I will ignore "$newBal - number_format($newBal,2);" which does nothing since you used "-" instead of "="... this would be solved by using a NUMERIC format, or just use this:
UPDATE newbalancetbl 
SET newBalance=ROUND(newBalance/assigned, 2)

Another php loop eliminated. But we can eliminate the update, and the temp table too while we're at it.
SELECT 
    j.leadid AS custid, 
    ROUND( j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid), 2) AS newBalance
FROM jobbooktbl j
WHERE j.convertdate BETWEEN '$first' AND '$today' 
AND j.status IN (4,6,7,8,11);

This should give the exact same results as what the temp table contained, minus the "assigned" column, which is not used in the rest of the code anyway, so we can drop it. Now, let's insert this into the next query...
SELECT ROUND(SUM(n.newBalance), 2) AS newB, u.username
FROM (
    SELECT 
        j.leadid AS custid, 
        j.price / (SELECT count(*) FROM assignmentstbl a WHERE a.custid=j.leadid) AS newBalance
    FROM jobbooktbl j
    WHERE j.convertdate BETWEEN '$first' AND '$today' 
    AND j.status IN (4,6,7,8,11)
    ) n
    JOIN assignmentstbl a USING (custid)
    JOIN usertbl u USING (userid)
    -- WHERE u.salesman=1
    GROUP BY a.userid
    ORDER BY newB DESC

This should do what you want. I moved the ROUND in the outside query.

