[help] Mau buat laporan bulanan dengan 2 table


Status
Not open for further replies.

crashblack

New Member
halo webmaster dan abg2 dan kk skalian bantuin donk..
saya mau buat laporan bulanan dan data nya diambil dari 2 table...
dan total semuanya di jumlahkan..

struktur table pertama
Code:
CREATE TABLE IF NOT EXISTS `pemesanmem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idtransaksi` varchar(20) NOT NULL,
  `username` varchar(100) NOT NULL,
  `nama_lkp` varchar(70) NOT NULL,
  `almt_lkp` varchar(300) NOT NULL,
  `almt_email` varchar(70) NOT NULL,
  `no_hp` varchar(70) NOT NULL,
  `cara_bayar` varchar(60) NOT NULL,
  `media_byr` varchar(20) NOT NULL,
  `email_pp` varchar(70) NOT NULL,
  `nama_bayar` varchar(70) NOT NULL,
  `pene_nm` varchar(70) NOT NULL,
  `pene_em` varchar(70) NOT NULL,
  `pene_no` varchar(60) NOT NULL,
  `pene_kowil` varchar(70) NOT NULL,
  `pene_alkap` varchar(300) NOT NULL,
  `total` double NOT NULL,
  `tanggal_pesan` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=120 ;

--
-- Dumping data for table `pemesanmem`
--

INSERT INTO `pemesanmem` (`id`, `idtransaksi`, `username`, `nama_lkp`, `almt_lkp`, `almt_email`, `no_hp`, `cara_bayar`, `media_byr`, `email_pp`, `nama_bayar`, `pene_nm`, `pene_em`, `pene_no`, `pene_kowil`, `pene_alkap`, `total`, `tanggal_pesan`) VALUES
(119, '101227150327', 'user', 'User Web Ini', 'Medan Medan', 'user@user.com', '5676', 'Transfer Bank BNI', '  ', '', '', 'User', 'kjdlaskd@jkdf.com', '453453453', 'MEDAN - BELAWAN', 'Jl. Jamin Ginting Km. 11,8 Gg Kenanga 1 Medan 20135', 4900000, '2010-12-27');

struktur table ke 2
Code:
CREATE TABLE IF NOT EXISTS `pemesannon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idtransaksi` varchar(20) NOT NULL,
  `nama_lkp` varchar(70) NOT NULL,
  `almt_lkp` varchar(300) NOT NULL,
  `almt_email` varchar(70) NOT NULL,
  `no_hp` varchar(70) NOT NULL,
  `cara_bayar` varchar(60) NOT NULL,
  `media_byr` varchar(20) NOT NULL,
  `email_pp` varchar(70) NOT NULL,
  `nama_bayar` varchar(70) NOT NULL,
  `pene_nm` varchar(70) NOT NULL,
  `pene_em` varchar(70) NOT NULL,
  `pene_no` varchar(60) NOT NULL,
  `pene_kowil` varchar(70) NOT NULL,
  `pene_alkap` varchar(300) NOT NULL,
  `total` double NOT NULL,
  `tanggal_pesan` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=149 ;

--
-- Dumping data for table `pemesannon`
--

INSERT INTO `pemesannon` (`id`, `idtransaksi`, `nama_lkp`, `almt_lkp`, `almt_email`, `no_hp`, `cara_bayar`, `media_byr`, `email_pp`, `nama_bayar`, `pene_nm`, `pene_em`, `pene_no`, `pene_kowil`, `pene_alkap`, `total`, `tanggal_pesan`) VALUES
(128, '101224043214', 'Crashblack', 'Jl. Jamin Ginting No. 49 Medan', 'crashblack99@yahoo.com', '088779787', 'Transfer Bank BNI', '  ', '', '', 'Crashblack', 'kjdlaskd@jkdf.com', '9873949', 'MEDAN - THAMRIN', 'Jl. Jamin Ginting Km. 11,8 Gg Kenanga 1 Medan 20135', 30980000, '2010-12-24');

trus script php nya
Code:
<table class="zebra" width="100%">
		            <thead>
		            <th><center><?php
						$tomorrow = mktime(0,0,0,date("m"),date("d"),date("Y"));
						echo "Laporan Pemesanan Bulan ".date("F  Y", $tomorrow);

					?></a></center></th>

		            </thead>
		            </table> <br/><br/>
		         <?php

		            require_once("../config/configad.php");
					$year = date("Y");
					$month = date("m");

					$sql = mysql_query("SELECT a.idtransaksi, b.idtransaksi, a.nama_lkp, a.almt_lkp, a.total, a.tanggal_pesan, b.nama_lkp, b.almt_lkp, b.total, b.tanggal_pesan FROM pemesannon a, pemesanmem b WHERE year(tanggal_pesan) = '".$year."' AND month(tanggal_pesan) = '".$month."'");

					echo "<table border='1' class='zebra' align='center'>";

					echo "<tr><td>Id Pemesan</td><td>Nama Lengkap</td><td>Alamat Pemesan</td><td>Produk Yang Di Pesan</td><td>Jumlah Pesan</td><td>Besar Bayar<td>Tanggal Pesan</td></tr>";
					while($cb = mysql_fetch_array($sql)){

					echo "<tr><td>".$cb['idtransaksi']."</td><td>".$cb['nama_lkp']."</td><td>"$.cb['almt_lkp']."</td><td>".$cb['total']."</td><td>".$cb['tanggal_pesan']."</td></tr>";
					}

					echo "</table>";

						$data = "";
						$total = $cb['total'];


					$query = mysql_query("select SUM($total) AS jumlah from pemesannon, pemesanmem WHERE month(tanggal_pesan)=".$month." AND year(tanggal_pesan) = ".$year);
					while($q=mysql_fetch_array($query)){
					   $data = $data.$q['jumlah']."";
					}
                 	echo "<div>Total : Rp.".$data."</div>";


					?>

saya bingung ama query nya ne,

mohon bantuan dan pencerahan abg2 skalian dan webmaster dan kk skalian :)
 
Status
Not open for further replies.

Top