SHARE
TWEET

Crosstab/pivot table with php

cahyadsn Aug 12th, 2016 116 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. /*
  3. --
  4. -- Database: `crosstab`
  5. --
  6.  
  7. --
  8. -- Table structure for table `nm_anggota`
  9. --
  10.  
  11. CREATE TABLE IF NOT EXISTS `nm_anggota` (
  12.   `no_id` int(11) NOT NULL auto_increment,
  13.   `nama` varchar(100) NOT NULL,
  14.   PRIMARY KEY  (`no_id`)
  15. ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
  16.  
  17. --
  18. -- Dumping data for table `nm_anggota`
  19. --
  20.  
  21. INSERT INTO `nm_anggota` (`no_id`, `nama`) VALUES
  22. (1, 'Andi'),
  23. (2, 'Iwan'),
  24. (3, 'Santi'),
  25. (4, 'Didi'),
  26. (5, 'Rani'),
  27. (6, 'sinta'),
  28. (7, 'Novi');
  29.  
  30. -- --------------------------------------------------------
  31.  
  32. --
  33. -- Table structure for table `transaksi`
  34. --
  35.  
  36. CREATE TABLE IF NOT EXISTS `transaksi` (
  37.   `no` varchar(11) NOT NULL,
  38.   `no_id` varchar(11) NOT NULL,
  39.   `tgl` date NOT NULL,
  40.   `rupiah` varchar(11) NOT NULL
  41. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  42.  
  43. --
  44. -- Dumping data for table `transaksi`
  45. --
  46.  
  47. INSERT INTO `transaksi` (`no`, `no_id`, `tgl`, `rupiah`) VALUES
  48. ('1', '1', '2016-08-01', '1000'),
  49. ('2', '2', '2016-08-01', '1500'),
  50. ('3', '3', '2016-08-01', '3000'),
  51. ('4', '1', '2016-08-02', '2500'),
  52. ('5', '2', '2016-08-02', '2000'),
  53. ('6', '3', '2016-08-02', '1500'),
  54. ('7', '4', '2016-08-02', '3000'),
  55. ('8', '1', '2016-08-03', '3500'),
  56. ('9', '2', '2016-08-03', '2500'),
  57. ('10', '3', '2016-08-03', '2000'),
  58. ('11', '4', '2016-08-03', '2500');
  59. */
  60.  
  61. $dbhost='localhost';
  62. $dbuser='root';
  63. $dbpass='';
  64. $dbname='cosstab';
  65. $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
  66. $sql="SELECT b.nama,a.tgl,a.rupiah FROM transaksi a JOIN nm_anggota b USING(no_id) ORDER BY a.tgl,b.nama";
  67. $result=$db->query($sql);
  68. $data=array();
  69. $nama=array();
  70. while($row=$result->fetch_object()){
  71.     if(!isset($data[$row->tgl])){
  72.         $data[$row->tgl]=array();
  73.     }
  74.     $data[$row->tgl][$row->nama]=$row->rupiah;
  75.     $nama[]=$row->nama;
  76. }
  77. $column=array_unique($nama);
  78. ?>
  79. <table border='1'>
  80.     <tr>
  81.         <th>Tanggal</th>
  82.         <?php
  83.         foreach($column as $col){
  84.             echo "<th>{$col}</th>";
  85.         }
  86.         ?>
  87.         <th>Jumlah</th>
  88.     </tr>
  89.     <?php
  90.         $total=array();
  91.         foreach($data as $tgl=>$row){
  92.             echo "<tr>
  93.            <td>{$tgl}</td>";
  94.             $subtotal=0;
  95.             foreach($column as $col){
  96.                 echo "<td  align='right'>".(isset($row[$col])?$row[$col]:"0")."</td>";
  97.                 $subtotal+=$row[$col];
  98.                 if(!isset($total[$col]))$total[$col]=0;
  99.                 $total[$col]+=$row[$col];
  100.             }
  101.             echo "<td align='right'>{$subtotal}</td></tr>";
  102.         }
  103.     ?>
  104.     <tr>
  105.         <th>JUMLAH</th>
  106.         <?php
  107.         $grandTotal=0;
  108.         foreach($column as $col){
  109.             echo "<th align='right'>{$total[$col]}</th>";
  110.             $grandTotal+=$total[$col];
  111.         }
  112.         ?>
  113.         <th align='right'><?php echo $grandTotal;?></th>
  114.     </tr>
  115. </table>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top