MySQL SP+ PHP Pivot Table
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

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 "<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.

Tags
PHP
MySQL
pivot

Crowdsource coding tasks.

0 Solutions