iyvZiPea

1<?php
2/*
3USE test;
4
5DROP TABLE IF EXISTS pupuk;
6CREATE TABLE IF NOT EXISTS pupuk(
7    id_pupuk INT AUTO_INCREMENT,
8    nama_pupuk VARCHAR(50),
9    harga_pupuk INT,
10    PRIMARY KEY(id_pupuk)
11);
12
13INSERT INTO pupuk(nama_pupuk,harga_pupuk)
14VALUES
15('urea',2300),
16('npk',3000),
17('ts',3100),
18('kcl',9000);
19
20DROP TABLE IF EXISTS benih;
21CREATE TABLE IF NOT EXISTS benih(
22    id_benih INT AUTO_INCREMENT,
23    nama_benih VARCHAR(50),
24    harga_benih INT,
25    PRIMARY KEY(id_benih)
26);
27
28INSERT INTO benih(nama_benih,harga_benih)
29VALUES
30('IR64',1275),
31('Setra Ramos',1325),
32('Rojolele',1420),
33('Pandan wangi',1500),
34('Flint Corn',730),
35('Dent Corn',740),
36('Sweet Corn',765),
37('Baluran',1520),
38('Mahameru',1480);
39
40DROP TABLE IF EXISTS tanaman;
41CREATE TABLE IF NOT EXISTS tanaman(
42    id_tanaman INT AUTO_INCREMENT,
43    id_benih INT,
44    nama_tanaman VARCHAR(50),
45    patokan_benih INT,
46    PRIMARY KEY(id_tanaman)
47);
48
49INSERT INTO tanaman(id_benih,nama_tanaman,patokan_benih)
50VALUES
51(2,'Padi',1),
52(7,'Jagung',2),
53(8,'Kacang Kedelai',1);
54
55
56DROP TABLE IF EXISTS pupuk_tanaman;
57CREATE TABLE IF NOT EXISTS pupuk_tanaman(
58    id_tanaman INT,
59    id_pupuk INT,
60    patokan INT
61);
62
63INSERT INTO pupuk_tanaman
64VALUES
65(1,1,3),
66(1,2,2),
67(1,4,1),
68(2,1,1),
69(2,3,2),
70(3,2,2),
71(3,4,1);
72
73*/
74$dbhost='localhost';
75$dbuser='root';
76$dbpass='';
77$dbname='test';
78$con=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
79//-- luas lahan
80$jml_lahan=100;
81//-- data pupuk
82$sql='
83    SELECT 
84        id_pupuk,nama_pupuk,harga_pupuk 
85    FROM 
86        pupuk 
87    ORDER BY 
88        id_pupuk';
89$result=$con->query($sql);
90$data_pupuk=array();
91while($row=$result->fetch_object()){
92    $data_pupuk[]=$row;
93}
94$jml_pupuk=count($data_pupuk);
95//-- data tanaman
96$sql='
97    SELECT
98        a.id_tanaman,
99        a.nama_tanaman,
100        a.patokan_benih,
101        b.harga_benih,
102        d.id_pupuk,
103        c.patokan
104    FROM
105        tanaman a
106        JOIN benih b USING(id_benih)
107        JOIN pupuk_tanaman c USING(id_tanaman)
108        JOIN pupuk d USING(id_pupuk)
109    ORDER BY
110        a.id_tanaman,
111        d.id_pupuk';
112$result=$con->query($sql);
113$data=array();
114$tanaman='';
115$i=0;
116while($row=$result->fetch_object()){
117    if($tanaman<>$row->id_tanaman){
118        $tanaman=$row->id_tanaman;
119        ++$i;
120        $data[++$i]=array(
121            'nama'=>$row->nama_tanaman,
122            'patokan'=>$row->patokan_benih,
123            'harga'=>$row->harga_benih
124        );
125    }
126    $data[$i][$row->id_pupuk]=$row->patokan;
127}
128?>
129<table border='1'>
130    <thead>
131        <tr>
132            <th rowspan='3'>Tanaman</th>
133            <th rowspan='3'>Luas</th>
134            <th colspan='2' rowspan='2'>Benih</th>
135            <th colspan='<?php echo $jml_pupuk*2;?>'>Pupuk</th>
136        </tr>
137    <tr>
138    <?php 
139    foreach($data_pupuk as $pupuk){
140        echo "<th colspan='2'>{$pupuk->nama_pupuk}</th>";
141    }
142    ?>
143    </tr>
144    <tr>
145    <?php
146    for($i=0;$i<$jml_pupuk+1;++$i){
147        echo '<th>Q</th><th>TH</th>';
148    }
149    ?>
150    </tr>
151    </thead>
152    <tbody>
153    <?php 
154    foreach($data as $d){
155        echo "<tr>
156                <td>{$d['nama']}</td>
157                <td>{$jml_lahan}</td>
158                <td>".($d['patokan']*$jml_lahan)."</td>
159                <td>".($d['patokan']*$jml_lahan*$d['harga'])."</td>";
160        foreach($data_pupuk as $pupuk){
161            echo 
162                '<td>'
163                .(isset($d[$pupuk->id_pupuk])?$d[$pupuk->id_pupuk]:'&nbsp;')
164                .'</td><td>'
165                .(isset($d[$pupuk->id_pupuk])?$d[$pupuk->id_pupuk]*$pupuk->harga_pupuk:'&nbsp;')
166                .'</td>';
167        }
168        echo "</tr>";
169    }
170    ?>
171    </tbody>
172</table>