Kali ini kita akan mengupas mysqli untuk kode statistik kunjungan, export ke csv/excel, xml juga print out dan pdf
sebagai berikut codenya :
statistik.php
<?php
$path = "statistik/counter0";
$pathicon = "statistik/counter0";
$ext = ".png";
$ip = $_SERVER['REMOTE_ADDR']; // Mendapatkan IP komputer user
$tanggal = date("Ymd"); // Mendapatkan tanggal sekarang
$waktu = time(); //
$sql="SELECT * FROM `statistik` WHERE ip='$ip' AND tanggal='$tanggal'";
$jum=getJum($conn,$sql);
if($jum == 0){
process($conn,"INSERT INTO `statistik`(ip, tanggal, hits, online) VALUES('$ip','$tanggal','1','$waktu')");
}
else{
process($conn,"UPDATE `statistik` SET hits=hits+1, online='$waktu' WHERE ip='$ip' AND tanggal='$tanggal'");
}
$pengunjung = getJum($conn,"SELECT * FROM `statistik` WHERE tanggal='$tanggal' GROUP BY ip");
$totalpengunjung = getJum($conn,"SELECT COUNT(hits) FROM `statistik`");
$hits = getJum($conn,"SELECT SUM(hits) FROM `statistik` WHERE tanggal='$tanggal' GROUP BY tanggal");
$totalhits = getJum($conn,"SELECT SUM(hits) FROM `statistik`");
$tothitsgbr = getJum($conn,"SELECT SUM(hits) FROM `statistik`");
$bataswaktu = time() - 300;
$pengunjungonline = getJum($conn,"SELECT * FROM `statistik` WHERE online > '$bataswaktu'");
$tothitsgbr = sprintf("%06d", $tothitsgbr);
for ( $i = 0; $i <= 9; $i++ ){
$tothitsgbr = str_replace($i, "<img src='$pathicon/$i$ext' alt='$i$ext'>", $tothitsgbr);
}
echo "<p align=left>$tothitsgbr </p>
<img src='$path/hariini.png'> Pengunjung hari ini : $pengunjung <br>
<img src='$path/total.png'> Total pengunjung : $totalpengunjung <br><br>
<img src='$path/hariini.png'> Hits hari ini : $hits <br>
<img src='$path/total.png'> Total Hits : $totalhits <br><br>
<img src='$path/online.png'> Pengunjung Online: $pengunjungonline";
?>
excel.php
<?php
require_once"../koneksivar.php";
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}
$buffer = "";
$separator = ","; //, atau ;
$newline = "\r\n";
$buffer = "kode_mahasiswa".$separator ."nama".$separator ."telepon".$separator ."email".$separator ."keterangan".$separator ."status".$separator;
$buffer .= $newline;
$sql="select `kode_mahasiswa`,`nama`,`telepon`,`email`,`keterangan`,`status` from `$tbmahasiswa` order by `kode_mahasiswa` desc";
$jum=getJum($conn,$sql);
if($jum>0){
$arr=getData($conn,$sql);
foreach($arr as $d) {
$value=$d["kode_mahasiswa"];$buffer .= "\"".$value."\"".$separator;
$value=$d["nama"];$buffer .= "\"".$value."\"".$separator;
$value=$d["telepon"];$buffer .= "\"".$value."\"".$separator;
$value=$d["email"];$buffer .= "\"".$value."\"".$separator;
$value=$d["keterangan"];$buffer .= "\"".$value."\"".$separator;
$value=$d["status"];$buffer .= "\"".$value."\"".$separator;
$buffer .= $newline;
}
}
else{
$buffer .= $newline;
}
header("Content-type: application/vnd.ms-excel");
header("Content-Length: ".strlen($buffer));
header("Content-Disposition: attachment; filename=report.csv");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
header("Pragma: public");
print $buffer;
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
function getJum($conn,$sql){
$rs=$conn->query($sql);
$jum= $rs->num_rows;
$rs->free();
return $jum;
}
function getData($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
$rs->free();
return $arr;
}
?>
print.php
<style type="text/css">body {width: 100%;} </style>
<body OnLoad="window.print()" OnFocus="window.close()">
<?php
include "../konmysqli.php";
echo"<link href='../ypathcss/$css' rel='stylesheet' type='text/css' />";
?>
<h3><center>Laporan data mahasiswa:</h3>
<table width="100%" border="0">
<tr>
<th width="5%"><center>no</td>
<th width="10%"><center>kode_mahasiswa</td>
<th width="25%"><center>nama</td>
<th width="25%"><center>telepon</td>
<th width="20%"><center>email</td>
<th width="10%"><center>keterangan</td>
<th width="5%"><center>status</td>
</tr>
<?php
$sql="select * from `$tbmahasiswa` order by `kode_mahasiswa` desc";
$jum=getJum($conn,$sql);
$no=0;
if($jum > 0){
$arr=getData($conn,$sql);
foreach($arr as $d) {
$no++;
$kode_mahasiswa=$d["kode_mahasiswa"];
$nama=$d["nama"];
$telepon=$d["telepon"];
$email=$d["email"];
$keterangan=$d["keterangan"];
$status=$d["status"];
if($no %2==1){
echo"<tr bgcolor='#999999'>
<td>$no</td>
<td>$kode_mahasiswa</td>
<td>$nama</td>
<td>$telepon</td>
<td>$email</td>
<td>$keterangan</td>
<td>$status</td>
</tr>";
}//no==1
else if($no %2==0){
echo"<tr bgcolor='#cccccc'>
<td>$no</td>
<td>$kode_mahasiswa</td>
<td>$nama</td>
<td>$telepon</td>
<td>$email</td>
<td>$keterangan</td>
<td>$status</td>
</tr>";
}
}//while
}//if
else{echo"<tr><td colspan='7'><blink>Maaf, Data mahasiswa belum tersedia...</blink></td></tr>";}
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
function getJum($conn,$sql){
$rs=$conn->query($sql);
$jum= $rs->num_rows;
$rs->free();
return $jum;
}
function getData($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
$rs->free();
return $arr;
}
?>
</table>
xml.php
<?php
header("Content-type: text/xml");
include "../konmysqli.php";
$sql = "select * from `$tbmahasiswa`";
if(getJum($conn,$sql)>0){
print "<mahasiswa>\n";
$arr=getData($conn,$sql);
foreach($arr as $d) {
$kode_mahasiswa=$d["kode_mahasiswa"];
$nama=$d["nama"];
$telepon=$d["telepon"];
$email=$d["email"];
$keterangan=$d["keterangan"];
$status=$d["status"];
print "<record>\n";
print " <nama>$nama</nama>\n";
print " <telepon>$telepon</telepon>\n";
print " <email>$email</email>\n";
print " <keterangan>$keterangan</keterangan>\n";
print " <status>$status</status>\n";
print " <kode_mahasiswa>$kode_mahasiswa</kode_mahasiswa>\n";
print "</record>\n";
}
print "</mahasiswa>\n";
}
else{
$null="null";
print "<mahasiswa>\n";
print "<record>\n";
print " <nama>$null</nama>\n";
print " <telepon>$null</telepon>\n";
print " <email>$null</email>\n";
print " <keterangan>$null</keterangan>\n";
print " <status>$null</status>\n";
print " <kode_mahasiswa>$null</kode_mahasiswa>\n";
print "</record>\n";
print "</mahasiswa>\n";
}
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
function getJum($conn,$sql){
$rs=$conn->query($sql);
$jum= $rs->num_rows;
$rs->free();
return $jum;
}
function getData($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
$rs->free();
return $arr;
}
?>
pdf.php
<?php
require_once"../koneksivar.php";
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
define('FPDF_FONTPATH', '../ypathcss/bantuan/fpdf/font/');
require('../ypathcss/bantuan/fpdf/fpdf.php');
class PDF extends FPDF{
function Header(){
$this->SetTextColor(128,0,0);
$this->SetFont('Arial','B','12');// $this->SetFont('Times','',12);
$this->Cell(20,0,'Data mahasiswa',0,0,'L');
$this->Ln();
$this->Cell(5,1,'Laporan data mahasiswa',0,0,'L');
$this->Ln();
}
function Footer(){
$this->SetY(-4,5);
$this->Image("../ypathfile/avatar.jpg", (8.5/2)-1.5, 9.8, 3, 1, "JPG", "http://www.lp2maray.com");
$this->SetY(-2,5);
$this->Cell(0,1,$this->PageNo(),0,0,'C');
}
}
$sql = "select * from `$tbmahasiswa`";
$jml = getJum($conn,$sql);
$i=0;
$arr=getData($conn,$sql);
foreach($arr as $d) {
$cell[$i][0]=$d["kode_mahasiswa"];
$cell[$i][1]=$d["nama"];
$cell[$i][2]=$d["telepon"];
$cell[$i][3]=$d["email"];
$cell[$i][4]=$d["keterangan"];
$cell[$i][5]=$d["status"];
$i++;
}
$pdf=new PDF('L','cm','A4');
//$pdf=new PDF("P","in","Letter");
$pdf->Open();
$pdf->AliasNbPages();
$pdf->AddPage();
$pdf->SetFont('Arial','B','9');
$pdf->SetFillColor(192,192,192);
$pdf->Cell(1,1,'no','LR',0,'L',1);
//$pdf->MultiCell(0, 0.5, $lipsum1, 'LR', "L");
$pdf->Cell(2,1,'kode_mahasiswa','LR',0,'C',1);
$pdf->Cell(7,1,'nama','LR',0,'C',1);
$pdf->Cell(5,1,'telepon','LR',0,'C',1);
$pdf->Cell(3,1,'email','LR',0,'C',1);
$pdf->Cell(9,1,'keterangan','LR',0,'C',1);
$pdf->Cell(1,1,'status','LR',0,'C',1);
$pdf->Ln();
$pdf->SetFont('Arial','','8');
for ($j=0;$j<$i;$j++){
$pdf->Cell(1,1,$j+1,'B',0,'L'); // no
$pdf->Cell(2,1,$cell[$j][0],'B',0,'L'); // kode_mahasiswa
$pdf->Cell(7,1,$cell[$j][1],'B',0,'L'); // nama
$pdf->Cell(5,1,$cell[$j][2],'B',0,'L'); // telepon
$pdf->Cell(3,1,$cell[$j][3],'B',0,'L'); // email
$pdf->Cell(9,1,$cell[$j][4],'B',0,'L'); // keterangan
$pdf->Cell(1,1,$cell[$j][5],'B',0,'L'); // status
$pdf->Ln();
}
$pdf->Output();
/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
function getJum($conn,$sql){
$rs=$conn->query($sql);
$jum= $rs->num_rows;
$rs->free();
return $jum;
}
function getData($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
$rs->free();
return $arr;
}
?>
penampakan print
penampakan xml
Selamat mencoba...:-)
Untuk Pendalaman rekan-rekan bisa kursus bersama kami di
Http://www.lp2maray.com
Download Source Code
Tidak ada komentar:
Posting Komentar