Revisions for "MySQL SP+ PHP Pivot Table"

MySQL SP+ PHP Pivot Table

This is my first post on bountify, so please let me know if i am missing anything

I have a current stored procedure to pull employee expense data from a database and arrange it as a pivot table. I also have a PHP site to display the data in a table.

I am looking to add details to the PHP table.
1) Add a row under the date headers containging the MAX(endmileage) for each date
(mileage table not currently part of the store procedure. Table needs joined using the datefield, and employeeid)

2) Add a column to the far right of the table with totals of types for the queried date span

3) Add bottom row with total of entries that have CC = '1' (under Daily total row)

-- employee_pivot Stored Procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_pivot`(IN `sdate` DATE, IN `edate` DATE, IN `emp` INT(3) UNSIGNED)
begin
declare finish int default 0;
declare cdate date;
declare str varchar(10000) default "select type,";
-- Select date from calendar.Join Entry to fill gaps
declare curs cursor for select datefield from calendar where datefield between sdate and edate group by datefield;
declare continue handler for not found set finish = 1;
open curs;
my_loop:loop
fetch curs into cdate;
if finish = 1 then
leave my_loop;
end if;
-- set str = concat(str, "max(case when expdate = '",cdate,"' then amount else null end) as `",cdate,"`,");
set str = concat(str, "sum(case when expdate = '",cdate,"' then amount else null end) as `",cdate,"`,");
end loop;
close curs;
set str = substr(str,1,char_length(str)-1);
set @str = concat(str," from calendar c
           left join entry on entry.`expdate` = c.datefield 
           join mastertypes on mastertypes.id = entry.TypeID
                 where Employeeid = '",emp,"'
           group by typeid");

prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end


-- Entry Table
CREATE TABLE `entry` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `expdate` date NOT NULL,
 `TypeID` int(11) NOT NULL,
 `amount` decimal(10,2) NOT NULL,
 `cc` tinyint(1) DEFAULT NULL,
 `entered_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date expense was inpute',
 `EmployeeID` int(11) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8


-- Mileage Table
CREATE TABLE `mileage` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `EmployeeID` int(11) NOT NULL,
 `mildate` date NOT NULL,
 `endmileage` int(11) NOT NULL,
 `personalmileage` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8


--PHP Page display Result
// PULL Expense Entry DATA from database
$pivot = $mysqli->query("call employee_pivot('$sdate','$edate','$userid')");

if($pivot){  
    while($pivottable[] = mysqli_fetch_assoc($pivot));
      array_pop($pivottable);


    echo "<TABLE border=1><TR>";

    // PRINT DATE HEADERS
    $dateheader = array_keys($pivottable[0]);
    for($i=0; $i< count($dateheader); $i++){
            echo "<Th>".$dateheader[$i]."</Th>";
        }

   echo "</TR>";
// ADD MILEAGE ROW WITH MAX(ENDMILEAGE) FOR THE CORRESPONDING DATE

    $datasentcount = count($pivottable);
        $i = 0;
        foreach ($pivottable as $row)
        {
            echo "<TR>";
            $i++;

            while (list($key, $value) = each ($row)) {     
                echo "<TD>$value</TD>";
                $sums[$key] = isset($sums[$key]) ? $sums[$key] += $value : $value;
            }

            echo "</TR>";
        }

    //Add Daily Total Row
    echo "<TR>";
    foreach ($sums as $key=>$s) {
      echo "<td> $s</TD>";
    }
    echo "</TR>";
// ADD ANOTHER ROW WITH SUM OF ENTRIES WITH CC='1' FOR THE CORRESPONDING DAY
    echo "</TABLE>"; 
}
else{echo "<em>No Results . . .</em>";}
?>

I am open to solutions with query direct in PHP to retrieve the additional items,but think adding to the store procedure will be cleaner, with less calls.

MySQL SP+ PHP Pivot Table
EDIT : I will award for solving any 1 of the three objectives! I will tip $5 for each objective solved after This is my first post on bountify, so please let me know if i am missing anything I have a current stored procedure to pull employee expense data from a database and arrange it as a pivot table. I also have a PHP site to display the data in a table. I am looking to add details to the PHP table. 1) Add a row under the date headers containging the MAX(endmileage) for each date (mileage table not currently part of the store procedure. Table needs joined using the datefield, and employeeid) 2) Add a column to the far right of the table with totals of types for the queried date span 3) Add bottom row with total of entries that have CC = '1' (under Daily total row) -- employee_pivot Stored Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_pivot`(IN `sdate` DATE, IN `edate` DATE, IN `emp` INT(3) UNSIGNED) begin declare finish int default 0; declare cdate date; declare str varchar(10000) default "select type,"; -- Select date from calendar.Join Entry to fill gaps declare curs cursor for select datefield from calendar where datefield between sdate and edate group by datefield; declare continue handler for not found set finish = 1; open curs; my_loop:loop fetch curs into cdate; if finish = 1 then leave my_loop; end if; -- set str = concat(str, "max(case when expdate = '",cdate,"' then amount else null end) as `",cdate,"`,"); set str = concat(str, "sum(case when expdate = '",cdate,"' then amount else null end) as `",cdate,"`,"); end loop; close curs; set str = substr(str,1,char_length(str)-1); set @str = concat(str," from calendar c left join entry on entry.`expdate` = c.datefield join mastertypes on mastertypes.id = entry.TypeID where Employeeid = '",emp,"' group by typeid"); prepare stmt from @str; execute stmt; deallocate prepare stmt; end -- Entry Table CREATE TABLE `entry` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `expdate` date NOT NULL, `TypeID` int(11) NOT NULL, `amount` decimal(10,2) NOT NULL, `cc` tinyint(1) DEFAULT NULL, `entered_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date expense was inpute', `EmployeeID` int(11) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=utf8 -- Mileage Table CREATE TABLE `mileage` ( `id` int(11) NOT NULL AUTO_INCREMENT, `EmployeeID` int(11) NOT NULL, `mildate` date NOT NULL, `endmileage` int(11) NOT NULL, `personalmileage` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 --PHP Page display Result // PULL Expense Entry DATA from database $pivot = $mysqli->query("call employee_pivot('$sdate','$edate','$userid')"); if($pivot){ while($pivottable[] = mysqli_fetch_assoc($pivot)); array_pop($pivottable); echo ""; // PRINT DATE HEADERS $dateheader = array_keys($pivottable[0]); for($i=0; $i< count($dateheader); $i++){ echo ""; } echo ""; // ADD MILEAGE ROW WITH MAX(ENDMILEAGE) FOR THE CORRESPONDING DATE $datasentcount = count($pivottable); $i = 0; foreach ($pivottable as $row) { echo ""; $i++; while (list($key, $value) = each ($row)) { echo ""; $sums[$key] = isset($sums[$key]) ? $sums[$key] += $value : $value; } echo ""; } //Add Daily Total Row echo ""; foreach ($sums as $key=>$s) { echo ""; } echo ""; // ADD ANOTHER ROW WITH SUM OF ENTRIES WITH CC='1' FOR THE CORRESPONDING DAY echo "
".$dateheader[$i]."
$value
$s
"; } else{echo "No Results . . .";} ?> I am open to solutions with query direct in PHP to retrieve the additional items,but think adding to the store procedure will be cleaner, with less calls.
Back to question