Mysqli ilu inu imu: 2017

Kamis, 05 Januari 2017

Kursus PHP+Mysqli dan Implementasinya

hari1:
+Pembuatan tabel kategori
+Praktek Simpan ke database
+Praktek lihat data dari database

+Penugasan tabel barang, tabel konsumen

hari2:
+Praktek Ubah dan Hapus database tabel kategori
+Relasi tabel melalu combobox dan radiobutton

+Penugasan tabel barang, tabel konsumen

+Materi Upload image/Gambar

hari3:

+Pembuatan tabel Order dan Order Detail

+Relasi tabel Order dan Order Detail

+Transaksi One to Many Transaksi

hari4:

+Implementasi cetak ke print

+Implementasi cetak ke pdf

+Implementasi cetak ke excel

+Implementasi cetak ke xml

+Implementasi cetak single data dan relasi data

hari5:

Studi Kasus + Review

Database Akademik

Database Penggajian

Database Training

Database Pengiriman

Kursus 5 hari Biaya 400.000;(mahasiswa)

Belajar bisa di rumah atau di tempat kursus

Hub :

Http://www.lp2maray.com

call:021-22712148

call: 021-78889003

hp/call/wa: 085279959498

Mysqli untuk statistik data kunjungan web, pdf, xml, csv/excell, print

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

Mysqli code Login dan autentikasi

menueruskan code sebelumnya...dengan menggunakan fungsi pada index.php

sbb adalah kode untuk otentikasi login :

<?php
session_start();
?>
<table width=100% border=0 cellspacing=0 cellpadding=1 bgcolor=#B19B68>
<tr><td class=textp>  Otentikasi</td></tr>
<tr><td><table width=100% cellspacing=5 cellpadding=0 bgcolor=#F8EED7>
<tr><td class=textblack>
<b>Otentikasi Data </b>
<form name="formLogin" method="post" action="">
<table width="284" border="0">
<tr>
<th colspan="2" bgcolor="#FF00FF"><marquee>
Silakan Tulis Data Login Anda / Register untuk membuat Acoount Baru
</marquee></th>
</tr>

<tr>
<td width="67">Username</td>
<td width="207">:
<input type="text" name="user" id="user" /></td>
</tr>

<tr>
<td>Password:</td>
<td>:
<input type="password" name="pass" id="pass">
</td>
</tr>

<tr>
<td colspan="2" align="right" valign="middle">
<input type="submit" name="Login" id="Login" value="Login">
<input type="Reset" name="Reset" id="Reset" value="Reset">
</td>
</tr>
</table>
</form>
</table></td></tr></table><br>
<?php
if(isset($_POST["Login"])){
$usr=$_POST["user"];
$pas=$_POST["pass"];

$sql1="select * from `$tbadmin` where `username`='$usr' and `password`='$pas' and `status`='Aktif'";
//$sql2="select * from `$tbadmin` where `username`='$usr' and `password`='$pas' and `status`='Aktif'";
//$sql3="select * from `$tbadmin` where `username`='$usr' and `password`='$pas' and `status`='Aktif'";

if(getJum($conn,$sql1)>0){
$d=getField($conn,$sql1);
$kode=$d["kode_admin"];
$nama=$d["username"];
$_SESSION["cid"]=$kode;
$_SESSION["cnama"]=$nama;
$_SESSION["cstatus"]="Administrator";
echo "<script>alert('Otentikasi ".$_SESSION["cstatus"]." ".$_SESSION["cnama"]." (".$_SESSION["cid"].") berhasil Login!');
document.location.href='index.php?mnu=home';</script>";
}
//elseif(getJum($conn,$sql2)>0){

// }
else{
session_destroy();
echo "<script>alert('Otentikasi Login GAGAL !,Silakan cek data Anda kembali...');
document.location.href='index.php?mnu=login';</script>";
}
}

?>

Download Source Code Lnk

Studi Kasus Mysqli CRUD

Buat database db_master dengan tabel sbb:

CREATE TABLE IF NOT EXISTS `admin` (
`kode_admin` varchar(8) COLLATE latin1_general_ci NOT NULL,
`username` varchar(25) COLLATE latin1_general_ci NOT NULL,
`password` varchar(25) COLLATE latin1_general_ci NOT NULL,
`telepon` varchar(15) COLLATE latin1_general_ci NOT NULL,
`email` varchar(50) COLLATE latin1_general_ci NOT NULL,
`gambar` varchar(50) COLLATE latin1_general_ci NOT NULL,
`status` enum('Aktif','Tidak Aktif') COLLATE latin1_general_ci NOT NULL DEFAULT 'Aktif',
PRIMARY KEY (`kode_admin`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `admin` (`kode_admin`, `username`, `password`, `telepon`, `email`, `gambar`, `status`) VALUES
('ADM02', 'a', 'a', '0234567845678', 'admin@yahoo.com', 'wifi.png', 'Aktif'),
('ADM03', 'array', 'array', '02345678923456', 'array@a.com', 'keys.jpg', 'Aktif'),
('ADM01', 'jokowi', 'jokowi', '021-11111111', 'presidenri@gmail.com', 'key.jpg', 'Aktif');

CREATE TABLE IF NOT EXISTS `mahasiswa` (
`kode_mahasiswa` varchar(15) COLLATE latin1_general_ci NOT NULL,
`nama` varchar(30) COLLATE latin1_general_ci NOT NULL,
`telepon` varchar(15) COLLATE latin1_general_ci NOT NULL,
`email` varchar(50) COLLATE latin1_general_ci NOT NULL,
`keterangan` text COLLATE latin1_general_ci NOT NULL,
`status` varchar(15) COLLATE latin1_general_ci NOT NULL,
PRIMARY KEY (`kode_mahasiswa`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `mahasiswa` (`kode_mahasiswa`, `nama`, `telepon`, `email`, `keterangan`, `status`) VALUES
('NIM03', 'Khanza Humaira Dinata', '021-78889003', 'adiarray@gmail.com', '-', 'Aktif'),
('NIM02', 'Rini Dyah Anggorowati', '021-92771708', 'adiarray@gmail.com', '-', 'Aktif'),
('NIM04', 'Riadi Marta Dinata', '085279959498', 'riadimrt@yahoo.com', '-', 'Aktif');

konmysqli.php

<?php
$DBServer = 'localhost';
$DBUser = 'root';
$DBPass = '';
$DBName = 'db_master';

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
if ($conn->connect_error) {
trigger_error('Database connection failed: ' . $conn->connect_error, E_USER_ERROR);
}

$gambar0="avatar.jpg";
$css="greenblack.css";//greenblack,gradient,flickr,amazon
$PATH="ypathcss";
$YPATH="ypathfile";

$tittle="CLIENT SERVER";
$header="Client Server";
$footer="Http://www.client-server.org";

$tbadmin="admin";
$tbmahasiswa="mahasiswa";
$tbstatistik="statistik";

?>

fungsi pada index.php

<?php
if (version_compare(phpversion(), "5.3.0", ">=") == 1)
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
else
error_reporting(E_ALL & ~E_NOTICE);
?>
<?php
session_start();
//error_reporting(0);
require_once"konmysqli.php";

$mnu=$_GET["mnu"];
date_default_timezone_set("Asia/Jakarta");

?>

<?php function RP($rupiah){return number_format($rupiah,"2",",",".");}?>
<?php
function WKT($sekarang){
$tanggal = substr($sekarang,8,2)+0;
$bulan = substr($sekarang,5,2);
$tahun = substr($sekarang,0,4);

$judul_bln=array(1=> "Januari", "Februari", "Maret", "April", "Mei","Juni", "Juli", "Agustus", "September","Oktober", "November", "Desember");
$wk=$tanggal." ".$judul_bln[(int)$bulan]." ".$tahun;
return $wk;
}
?>
<?php
function WKTP($sekarang){
$tanggal = substr($sekarang,8,2)+0;
$bulan = substr($sekarang,5,2);
$tahun = substr($sekarang,2,2);

$judul_bln=array(1=> "Jan", "Feb", "Mar", "Apr", "Mei","Jun", "Jul", "Agu", "Sep","Okt", "Nov", "Des");
$wk=$tanggal." ".$judul_bln[(int)$bulan]."'".$tahun;
return $wk;
}
?>
<?php
function BAL($tanggal){
$arr=split(" ",$tanggal);
if($arr[1]=="Januari"){$bul="01";}
else if($arr[1]=="Februari"){$bul="02";}
else if($arr[1]=="Maret"){$bul="03";}
else if($arr[1]=="April"){$bul="04";}
else if($arr[1]=="Mei"){$bul="05";}
else if($arr[1]=="Juni"){$bul="06";}
else if($arr[1]=="Juli"){$bul="07";}
else if($arr[1]=="Agustus"){$bul="08";}
else if($arr[1]=="September"){$bul="09";}
else if($arr[1]=="Oktober"){$bul="10";}
else if($arr[1]=="November"){$bul="11";}
else if($arr[1]=="Nopember"){$bul="11";}
else if($arr[1]=="Desember"){$bul="12";}
return "$arr[2]-$bul-$arr[0]";
}
?>

<?php
function BALP($tanggal){
$arr=split(" ",$tanggal);
if($arr[1]=="Jan"){$bul="01";}
else if($arr[1]=="Feb"){$bul="02";}
else if($arr[1]=="Mar"){$bul="03";}
else if($arr[1]=="Apr"){$bul="04";}
else if($arr[1]=="Mei"){$bul="05";}
else if($arr[1]=="Jun"){$bul="06";}
else if($arr[1]=="Jul"){$bul="07";}
else if($arr[1]=="Agu"){$bul="08";}
else if($arr[1]=="Sep"){$bul="09";}
else if($arr[1]=="Okt"){$bul="10";}
else if($arr[1]=="Nov"){$bul="11";}
else if($arr[1]=="Nop"){$bul="11";}
else if($arr[1]=="Des"){$bul="12";}
return "$arr[2]-$bul-$arr[0]";
}
?>

<?php
function process($conn,$sql){
$s=false;
$conn->autocommit(FALSE);
try {
$rs = $conn->query($sql);
if($rs){
$conn->commit();
$last_inserted_id = $conn->insert_id;
$affected_rows = $conn->affected_rows;
$s=true;
}
}
catch (Exception $e) {
echo 'fail: ' . $e->getMessage();
$conn->rollback();
}
$conn->autocommit(TRUE);
return $s;
}

function getJum($conn,$sql){
$rs=$conn->query($sql);
$jum= $rs->num_rows;
$rs->free();
return $jum;
}

function getField($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$d= $rs->fetch_assoc();
$rs->free();
return $d;
}

function getData($conn,$sql){
$rs=$conn->query($sql);
$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
//foreach($arr as $row) {
// echo $row['nama_kelas'] . '*<br>';
//}

$rs->free();
return $arr;
}

function getAdmin($conn,$kode){
$field="username";
$sql="SELECT `$field` FROM `tb_admin` where `kode_admin`='$kode'";
$rs=$conn->query($sql);
$rs->data_seek(0);
$row = $rs->fetch_assoc();
$rs->free();
return $row['$field'];
}
?>

CODE PADA ADMIN.PHP

<?php

$pro="simpan";

$gambar0="avatar.jpg";

$status="Aktif";

//$PATH="ypathcss";

$(document).ready(function(){

$("#tgllahir").datepicker({

dateFormat : "dd MM yy",

changeMonth : true,

changeYear : true

});

</script>

function PRINT(){

win=window.open('admin/print.php','win','width=1000, height=400, menubar=0, scrollbars=1, resizable=0, location=0, toolbar=0, status=0'); }

</script>

function buka(url) {window.open(url, 'window_baru', 'width=800,height=600,left=320,top=100,resizable=1,scrollbars=1');}

</script>

<?php

$sql="select `kode_admin` from `$tbadmin` order by `kode_admin` desc";

$jum= getJum($conn,$sql);

$kd="ADM";

if($jum > 0){

$d=getField($conn,$sql);

$idmax=$d['kode_admin'];

$urut=substr($idmax,3,2)+1;//01

if($urut<10){$idmax="$kd"."0".$urut;}

else{$idmax="$kd".$urut;}

}

else{$idmax="$kd"."01";}

$kode_admin=$idmax;

<?php

if($_GET["pro"]=="ubah"){

$kode_admin=$_GET["kode"];

$sql="select * from `$tbadmin` where `kode_admin`='$kode_admin'";

$d=getField($conn,$sql);

$kode_admin=$d["kode_admin"];

$username=$d["username"];

$password=$d["password"];

$telepon=$d["telepon"];

$email=$d["email"];

$status=$d["status"];

$gambar=$d["gambar"];

$gambar0=$d["gambar"];

$pro="ubah";

}

<tr>

<th width="66"><label for="kode_admin">kode_admin</label>

<th width="9">:

<tr>

<td><label for="username">username</label>

</td>

<?php

echo"<a href='#' onclick='buka(\"admin/zoom.php?id=$kode_admin\")'>

</a>

</center>

</td>

</tr>

<tr>

<td height="24"><label for="password">password</label>

</tr>

<tr>

<td height="24"><label for="telepon">telepon</label>

</td>

</tr>

<tr>

<td height="24"><label for="email">email</label>

</td>

</tr>

<tr>

<td><label for="status">status</label>

<input type="radio" name="status" id="statusya" checked="checked" value="Aktif" <?php if($status=="Aktif"){echo"checked";}?>/>Aktif

<input type="radio" name="status" id="statustidak" value="Tidak Aktif" <?php if($status=="Tidak Aktif"){echo"checked";}?>/>Tidak Aktif

</td></tr>

<tr>

<td height="24">gambar

=> <a href='#' onclick='buka("admin/zoom.php?id=<?php echo $kode_admin;?>")'><?php echo $gambar0;?></a></td>

</tr>

<tr>

<td>

</td></tr>

</table>

</form>

<br />

Data Admin:

| <a href="admin/pdf.php"><img src='ypathicon/pdf.png' alt='PDF'></a>

| <a href="admin/xls.php"><img src='ypathicon/xls.png' alt='XLS'></a>

| <a href="admin/xml.php"><img src='ypathicon/xml.png' alt='XML'></a>

| <img src='ypathicon/print.png' alt='PRINT' OnClick="PRINT()"> |

<br>

<th width="10%">kode_admin</td>

<th width="20%">username</td>

<th width="30%">email</td>

<th width="20%">telepon</td>

<th width="10%">gambar</td>

</tr>

<?php

$sql="select * from `$tbadmin` order by `kode_admin` desc";

$jum=getJum($conn,$sql);

if($jum > 0){

//--------------------------------------------------------------------------------------------

$batas = 2;

$page = $_GET['page'];

if(empty($page)){$posawal = 0;$page = 1;}

else{$posawal = ($page-1) * $batas;}

$sql2 = $sql." LIMIT $posawal,$batas";

$no = $posawal+1;

//--------------------------------------------------------------------------------------------

$arr=getData($conn,$sql2);

foreach($arr as $d) {

$kode_admin=$d["kode_admin"];

$username=$d["username"];

$password=$d["password"];

$telepon=$d["telepon"];

$email=$d["email"];

$status=$d["status"];

$gambar=$d["gambar"];

$gambar0=$d["gambar"];

$color="#dddddd";

if($no %2==0){$color="#eeeeee";}

echo"<tr bgcolor='$color'>

<td>$kode_admin</td>

<td>$username</td>

<td>$email</td>

<td>$telepon</td>

<td><div align='center'>";

echo"<a href='#' onclick='buka(\"admin/zoom.php?id=$kode_admin\")'>

<img src='$YPATH/$gambar' width='40' height='40' /></a></div>";

echo"</td>

<a href='?mnu=admin&pro=hapus&kode=$kode_admin'><img src='ypathicon/h.png' alt='hapus'

onClick='return confirm(\"Apakah Anda benar-benar akan menghapus $nama pada data admin ?..\")'></a></div></td>

</tr>";

$no++;

}//while

}//if

else{echo"<tr><td colspan='6'><blink>Maaf, Data admin belum tersedia...</blink></td></tr>";}

</table>

<?php

$jmldata = $jum;

if($jmldata>0){

if($batas<1){$batas=1;}

$jmlhal = ceil($jmldata/$batas);

echo "<div class=paging>";

if($page > 1){

$prev=$page-1;

echo "<span class=prevnext><a href='$_SERVER[PHP_SELF]?page=$prev&mnu=admin'>« Prev</a></span> ";

}

else{echo "<span class=disabled>« Prev</span> ";}

for($i=1;$i<=$jmlhal;$i++)

if ($i != $page){echo "<a href='$_SERVER[PHP_SELF]?page=$i&mnu=admin'>$i</a> ";}

else{echo " <span class=current>$i</span> ";}

if($page < $jmlhal){

$next=$page+1;

echo "<span class=prevnext><a href='$_SERVER[PHP_SELF]?page=$next&mnu=admin'>Next »</a></span>";

}

else{ echo "<span class=disabled>Next »</span>";}

echo "</div>";

}//if jmldata

$jmldata = $jum;

echo "<p align=center>Total data <b>$jmldata</b> item</p>";

<?php

if(isset($_POST["Simpan"])){

$pro=strip_tags($_POST["pro"]);

$kode_admin=strip_tags($_POST["kode_admin"]);

$kode_admin0=strip_tags($_POST["kode_admin"]);

$username=strip_tags($_POST["username"]);

$password=strip_tags($_POST["password"]);

$telepon=strip_tags($_POST["telepon"]);

$email=strip_tags($_POST["email"]);

$status=strip_tags($_POST["status"]);

$gambar0=strip_tags($_POST["gambar0"]);

if ($_FILES["gambar"] != "") {

@copy($_FILES["gambar"]["tmp_name"],"$YPATH/".$_FILES["gambar"]["name"]);

$gambar=$_FILES["gambar"]["name"];

}

else {$gambar=$gambar0;}

if(strlen($gambar)<1){$gambar=$gambar0;}

if($pro=="simpan"){

$sql=" INSERT INTO `$tbadmin` (

`kode_admin` ,

`username` ,

`password` ,

`telepon` ,

`email` ,

`status` ,

`gambar`

) VALUES (

'$kode_admin',

'$username',

'$password',

'$telepon',

'$email',

'$status',

'$gambar'

)";

$simpan=process($conn,$sql);

if($simpan) {echo "<script>alert('Data $kode_admin berhasil disimpan !');document.location.href='?mnu=admin';</script>";}

else{echo"<script>alert('Data $kode_admin gagal disimpan...');document.location.href='?mnu=admin';</script>";}

}

else{

$sql="update `$tbadmin` set `username`='$username',`password`='$password',`telepon`='$telepon' ,`email`='$email',`status`='$status',

`gambar`='$gambar' where `kode_admin`='$kode_admin0'";

$ubah=process($conn,$sql);

if($ubah) {echo "<script>alert('Data $kode_admin berhasil diubah !');document.location.href='?mnu=admin';</script>";}

else{echo"<script>alert('Data $kode_admin gagal diubah...');document.location.href='?mnu=admin';</script>";}

}//else simpan

}

<?php

if($_GET["pro"]=="hapus"){

$kode_admin=$_GET["kode"];

$sql="delete from `$tbadmin` where `kode_admin`='$kode_admin'";

$hapus=process($conn,$sql);

if($hapus) {echo "<script>alert('Data $kode_admin berhasil dihapus !');document.location.href='?mnu=admin';</script>";}

else{echo"<script>alert('Data $kode_admin gagal dihapus...');document.location.href='?mnu=admin';</script>";}

}

Penampakan :

SIMPAN-UBAH DAN HAPUS ADMIN BERHASIL.......

code mahasiswa.php

<?php

$pro="simpan";

$tanggal=WKT(date("Y-m-d"));

$(document).ready(function(){

$("#tanggal").datepicker({

dateFormat : "dd MM yy",

changeMonth : true,

changeYear : true

});

</script>

function PRINT(){

win=window.open('mahasiswa/print.php','win','width=1000, height=400, menubar=0, scrollbars=1, resizable=0, location=0, toolbar=0, status=0'); }

</script>

function buka(url) {window.open(url, 'window_baru', 'width=800,height=600,left=320,top=100,resizable=1,scrollbars=1');}

</script>

<?php

$sql="select `kode_mahasiswa` from `$tbmahasiswa` order by `kode_mahasiswa` desc";

$jum= getJum($conn,$sql);

$kd="MHS";

if($jum > 0){

$d=getField($conn,$sql);

$idmax=$d['kode_mahasiswa'];

$urut=substr($idmax,3,3)+1;//01

if($urut<10){$idmax="$kd"."00".$urut;}

else if($urut<100){$idmax="$kd"."0".$urut;}

else{$idmax="$kd".$urut;}

}

else{$idmax="$kd"."01";}

$kode_mahasiswa=$idmax;

<?php

if($_GET["pro"]=="ubah"){

$kode_mahasiswa=$_GET["kode"];

$sql="select * from `$tbmahasiswa` where `kode_mahasiswa`='$kode_mahasiswa'";

$d=getField($conn,$sql);

$kode_mahasiswa=$d["kode_mahasiswa"];

$kode_mahasiswa0=$d["kode_mahasiswa"];

$nama=$d["nama"];

$telepon=$d["telepon"];

$email=$d["email"];

$keterangan=$d["keterangan"];

$status=$d["status"];

$pro="ubah";

}

<tr>

<th width="66"><label for="kode_mahasiswa">kode_mahasiswa</label>

<th width="9">:

</tr>

<tr>

<td>:

</tr>

<tr>

<td height="24"><label for="telepon">telepon</label>

</td>

</tr>

<tr>

<td height="24"><label for="email">email</label>

<td>:

</tr>

<tr>

<td height="24"><label for="keterangan">keterangan</label>

</td>

</tr>

<tr>

<td><label for="status">status</label>

<tr>

<td>

</td></tr>

</table>

</form>

Data mahasiswa:

| <a href="mahasiswa/pdf.php"><img src='ypathicon/pdf.png' alt='PDF'></a>

| <a href="mahasiswa/xls.php"><img src='ypathicon/xls.png' alt='XLS'></a>

| <a href="mahasiswa/xml.php"><img src='ypathicon/xml.png' alt='XML'></a>

| <img src='ypathicon/print.png' alt='PRINT' OnClick="PRINT()"> |

<br>

<th width="10%"><center>telepon</th>

<th width="30%"><center>email</th>

<th width="15%"><center>keterangan</th>

<th width="10%"><center>Status</th>

</tr>

<?php

$sql="select * from `$tbmahasiswa` order by `kode_mahasiswa` desc";

$jum=getJum($conn,$sql);

if($jum > 0){

//--------------------------------------------------------------------------------------------

$batas = 2;

$page = $_GET['page'];

if(empty($page)){$posawal = 0;$page = 1;}

else{$posawal = ($page-1) * $batas;}

$sql2 = $sql." LIMIT $posawal,$batas";

$no = $posawal+1;

//--------------------------------------------------------------------------------------------

$arr=getData($conn,$sql2);

foreach($arr as $d) {

$kode_mahasiswa=$d["kode_mahasiswa"];

$nama=$d["nama"];

$telepon=$d["telepon"];

$email=$d["email"];

$keterangan=$d["keterangan"];

$status=$d["status"];

$color="#dddddd";

if($no %2==0){$color="#eeeeee";}

echo"<tr bgcolor='$color'>

<td>$kode_mahasiswa</td>

<td>$telepon</td>

<td>$email</td>

<td>$keterangan</td>

<td align='center'>$status</td>

<a href='?mnu=mahasiswa&pro=hapus&kode=$kode_mahasiswa'><img src='ypathicon/h.png' alt='hapus'

onClick='return confirm(\"Apakah Anda benar-benar akan menghapus $nama pada data mahasiswa ?..\")'></a></td>

</tr>";

$no++;

}//while

}//if

else{echo"<tr><td colspan='7'><blink>Maaf, Data mahasiswa belum tersedia...</blink></td></tr>";}

</table>

<?php

//Langkah 3: Hitung total data dan page

$jmldata = $jum;

if($jmldata>0){

if($batas<1){$batas=1;}

$jmlhal = ceil($jmldata/$batas);

echo "<div class=paging>";

if($page > 1){

$prev=$page-1;

echo "<span class=prevnext><a href='$_SERVER[PHP_SELF]?page=$prev&mnu=mahasiswa'>« Prev</a></span> ";

}

else{echo "<span class=disabled>« Prev</span> ";}

// Tampilkan link page 1,2,3 ...

for($i=1;$i<=$jmlhal;$i++)

if ($i != $page){echo "<a href='$_SERVER[PHP_SELF]?page=$i&mnu=mahasiswa'>$i</a> ";}

else{echo " <span class=current>$i</span> ";}

// Link kepage berikutnya (Next)

if($page < $jmlhal){

$next=$page+1;

echo "<span class=prevnext><a href='$_SERVER[PHP_SELF]?page=$next&mnu=mahasiswa'>Next »</a></span>";

}

else{ echo "<span class=disabled>Next »</span>";}

echo "</div>";

}//if jmldata

$jmldata = $jum;

echo "<p align=center>Total Data <b>$jmldata</b> Item</p>";

<?php

if(isset($_POST["Simpan"])){

$pro=strip_tags($_POST["pro"]);

$kode_mahasiswa=strip_tags($_POST["kode_mahasiswa"]);

$kode_mahasiswa0=strip_tags($_POST["kode_mahasiswa0"]);

$nama=strip_tags($_POST["nama"]);

$telepon=strip_tags($_POST["telepon"]);

$email=strip_tags($_POST["email"]);

$keterangan=strip_tags($_POST["keterangan"]);

$status=strip_tags($_POST["status"]);

if($pro=="simpan"){

$sql=" INSERT INTO `$tbmahasiswa` (

`kode_mahasiswa` ,

`nama` ,

`telepon` ,

`email` ,

`keterangan` ,

`status`

) VALUES (

'$kode_mahasiswa',

'$nama',

'$telepon',

'$email',

'$keterangan',

'$status'

)";

$simpan=process($conn,$sql);

if($simpan) {echo "<script>alert('Data $kode_mahasiswa berhasil disimpan !');document.location.href='?mnu=mahasiswa';</script>";}

else{echo"<script>alert('Data $kode_mahasiswa gagal disimpan...');document.location.href='?mnu=mahasiswa';</script>";}

}

else{

$sql="update `$tbmahasiswa` set

`nama`='$nama',

`telepon`='$telepon' ,

`email`='$email',

`status`='$status',

`keterangan`='$keterangan'

where `kode_mahasiswa`='$kode_mahasiswa0'";

$ubah=process($conn,$sql);

if($ubah) {echo "<script>alert('Data $kode_mahasiswa berhasil diubah !');document.location.href='?mnu=mahasiswa';</script>";}

else{echo"<script>alert('Data $kode_mahasiswa gagal diubah...');document.location.href='?mnu=mahasiswa';</script>";}

}//else simpan

}

<?php

if($_GET["pro"]=="hapus"){

$kode_mahasiswa=$_GET["kode"];

$sql="delete from `$tbmahasiswa` where `kode_mahasiswa`='$kode_mahasiswa'";

$hapus=process($conn,$sql);

if($hapus) {echo "<script>alert('Data mahasiswa $kode_mahasiswa berhasil dihapus !');document.location.href='?mnu=mahasiswa';</script>";}

else{echo"<script>alert('Data mahasiswa $kode_mahasiswa gagal dihapus...');document.location.href='?mnu=mahasiswa';</script>";}

}

penampakan saat data NIM04 di klik ->siap di hapus atau diubah

SIMPAN UBAH HAPUS JUGA PAGING DATA MAHASISWA BERHASIL

Rabu, 04 Januari 2017

Rangkuman dan Fungsi Mysqli

Pada versi PHP 5.5 extension mysql tidak lagi disarankan penggunaannya dan berstatus :
deprecated.

Dalam versi terbaru ini kita akan beralih kepada Mysqli atau PDO . Mysqli adalah kepanjangan dari Mysql Improved extension , dalam versi ini dibuat untuk mendukung tipe tipe terbaru dari Mysql Serve versi 4.1 keatas.

Perbedaannya dari mysql dan mysqli yaitu sebagai contoh jika kita memasukan kode seperti di bawah ini pada versi sebelumnya :

dalam Mysqli harus menggunakan seperti di bawah ini :

Selain menambah huruf “i” di dalam nama fungsi, argumen-argumen yang dibutuhkan juga hampir mirip.

Perbedaannya, jika di dalam extension mysql umumnya kita meletakkan argumen resources di akhir fungsi, maka di dalam mysqli, argumen ini diteletakkan di awal.

Agar proses “migrasi” dari mysql ke mysqli tidak terlalu menyusahkan, PHP memberikan 2 alternatif cara penulisan mysqli.

1. Cara yang pertama adalah menggunakan procedural style. Cara ini mirip dengan extension mysql, dimana kita menggunakan fungsi-fungsi untuk mengakses database MySQL.

2. Cara kedua adalah dengan object oriented style. Dengan cara ini, kita menggunakan aturan penulisan pemrograman objek untuk berkomunikasi dengan MySQL.

Sebagai berikut adalah code-code mysqli cara kedua.....

<?php
require_once"konmysqli.php";

$sql="SELECT * FROM tb_kelas";
$rs=$conn->query($sql);
if($rs){
$jum= $rs->num_rows;
}

//menarik data
$rs->data_seek(0);
while($row = $rs->fetch_row()){
echo $row[1] . '@<br>';
}

$rs->data_seek(0);
while($row = $rs->fetch_assoc()){
echo getGuru($conn,$row['kode_guru']) . '#<br>';
}

$rs->data_seek(0);
$arr = $rs->fetch_all(MYSQLI_ASSOC);
foreach($arr as $row) {
echo $row['nama_kelas'] . '*<br>';
}

$rs->free();

//=======================================insert
$sql="INSERT INTO `tb_kelas` (`kode_kelas`, `nama_kelas`, `kode_ruang`, `kode_guru`, `periode`, `catatan`) VALUES ('KDK009', 'IX', 'KDR003', 'KDG003', 'Genap 2016-2017', '-')";

//$s=process($conn,$sql);
//if($s){echo"sukses simpan";}
//else{echo "gagal simpan";}
//============

$conn->close();
?>

<?php
function process($conn,$sql){
$s=false;
$conn->autocommit(FALSE);
try {
$rs = $conn->query($sql);
if($rs){
$conn->commit();
$last_inserted_id = $conn->insert_id;
$affected_rows = $conn->affected_rows;
$s=true;
}
}
catch (Exception $e) {
echo 'fail: ' . $e->getMessage();
$conn->rollback();
}
$conn->autocommit(TRUE);
return $s;
}

function getGuru($conn,$kode){
$sql="SELECT `nama_guru` FROM `tb_guru` where `kode_guru`='$kode'";
$rs=$conn->query($sql);
$rs->data_seek(0);
$row = $rs->fetch_assoc();
return $row['nama_guru'];
}
?>

Mysqli Code PHP to Database Mysql

1.Perispan database :Download XAMPP-Install dan aktifkan

2.Buat database dan tabel :

Database name: db_akademik

CREATE TABLE `tb_kelas` (
`kode_kelas` varchar(15) NOT NULL,
`nama_kelas` varchar(30) NOT NULL,
`kode_ruang` varchar(15) NOT NULL,
`kode_guru` varchar(15) NOT NULL,
`periode` varchar(30) NOT NULL,
`catatan` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Demikian pula buat tabel-tabel yang lainnya.....

3. Buat Koneksi php ke mysqli

Connect

Define connection parameters:

$DBServer = 'server name or IP address'; // e.g 'localhost' or '192.168.1.100'

$DBUser   = 'DB_USER';

$DBPass   = 'DB_PASSWORD';

$DBName   = 'DB_NAME';

Connection using the object oriented way (RECOMMENDED).

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

// check connection

if ($conn->connect_error) {

  trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);

}

Connection using the procedural way (NOT RECOMMENDED).

MySQLi also offers a procedural API, except the object-oriented API. Procedural API might be easier for newcomers to understand, as it is similar with the old PHP MySQL extension API. Here is an example:

$conn = mysqli_connect($DBServer, $DBUser, $DBPass, $DBName);

// check connection

if (mysqli_connect_errno()) {

  trigger_error('Database connection failed: '  . mysqli_connect_error(), E_USER_ERROR);

}

(I will not quote further examples using the proceduaral API, as I recommend to use the object-oriented API. Of course it is available in PHP MySQLi documentation). A useful summary is also available.

Select

Use the following syntax:

$sql='SELECT col1, col2, col3 FROM table1 WHERE condition';

$rs=$conn->query($sql);

if($rs === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $rows_returned = $rs->num_rows;

}

Iterate over recordset

Using column names - recommended

$rs->data_seek(0);

while($row = $rs->fetch_assoc()){

    echo $row['col1'] . '<br>';

}

Using column index

$rs->data_seek(0);

while($row = $rs->fetch_row()){

    echo $row[0] . '<br>';

}

Store all values to array

$rs=$conn->query($sql);

if($rs === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $arr = $rs->fetch_all(MYSQLI_ASSOC);

}

foreach($arr as $row) {

  echo $row['co1'];

}

Using MYSQLI_ASSOC an associated array is returned, MYSQLI_NUM an enumerated one and MYSQLI_BOTH both of them.

WARNING: fetch_all is available only with MySQL Native Driver.

Store row values to array

The following example will return an array with first row (using $rs->data_seek(n); we can get any row).

$rs=$conn->query($sql);

if($rs === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $rs->data_seek(0);

  $arr = $rs->fetch_array(MYSQLI_ASSOC);

}

Record count

$rows_returned = $rs->num_rows;

Move inside recordset

$rs->data_seek(10);

Free memory

Optional:

$rs->free();

Insert

Use the following syntax:

real_escape_string is used to escape special characters NUL (ASCII 0), \n, \r, \, ', ", and Control-Z in string values before insert to Database (mainly to prevent SQL injection).

WARNING: real_escape_string does not add quotes, you have to do it manually.

$v1="'" . $conn->real_escape_string('col1_value') . "'";

$sql="INSERT INTO tbl (col1_varchar, col2_number) VALUES ($v1,10)";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $last_inserted_id = $conn->insert_id;

  $affected_rows = $conn->affected_rows;

}

Update

Use the following syntax:

$v1="'" . $conn->real_escape_string('col1_value') . "'";

$sql="UPDATE tbl SET col1_varchar=$v1, col2_number=1 WHERE id>10";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $affected_rows = $conn->affected_rows;

}

Delete

Use the following syntax:

$sql="DELETE FROM tbl WHERE id>10";

if($conn->query($sql) === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

} else {

  $affected_rows = $conn->affected_rows;

}

Transactions

Use the following syntax:

try {

  /* switch autocommit status to FALSE. Actually, it starts transaction */

  $conn->autocommit(FALSE);

  $res = $conn->query($sql1);

  if($res === false) {

    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);

  }

  $res = $conn->query($sql2);

  if($res === false) {

    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);

  }

  $res = $conn->query($sql3);

  if($res === false) {

    throw new Exception('Wrong SQL: ' . $sql . ' Error: ' . $conn->error);

  }

  $conn->commit();

  echo 'Transaction completed successfully!';

} catch (Exception $e) {

  echo 'Transaction failed: ' . $e->getMessage();

  $conn->rollback();

}

/* switch back autocommit status */

$conn->autocommit(TRUE);

According to http://www.php.net/manual/en/mysqli.commit.php#89976, calling $conn->commit() will NOT automatically set autocommit() back to 'true'. That means that any queries following $conn->commit() will be rolled back when your script exits, if autocommit() will be not switched back to TRUE.

WARNING: some MySQL statements cause an implicit commit, so the cannot be used inside a transaction. For example, you cannot rollback MySQL CREATE TABLE or TRUNCATE TABLE inside a transcaction. A useful comparison is available here.

Quoting and escaping strings

You have probably noticed that every string value is escaped before inserted to database as special characters may break SQL and, moreover, to prevent SQL injection.

$safe_string = $conn->real_escape_string($string);

Example: bla"bla\bla will be converted to bla\"bla\\bla.

real_escape_string does not add quotes, you have to do it manually.

However, these are not required, if you use Prepared statements (see below).

Prepared statements

What are Prepared Statements and why they are important?

Prepared Statement objects are used with an SQL statement which, typically but not necessary, takes parameters (using the symbol ? in our case or using other placeholders in different DBMS, e.g. $1, $2 etc in PostgreSQL).

After an SQL Statement has been prepared, the DBMS does not have to recompile it and prepare an execution plan. The Database engine simply runs (executes) the statement. This is can optimize performance. Performance advantage is remarkable when a single session is being used to execute a large number of similar statements.

These parameters inside a prepared statement don't need to be escaped and quoted. Driver takes care of this. So, using of Prepared Statements eliminates the possibility of SQL injection.

If you're not familiar with the use of Prepared Statements, you should do it, as it is very important for web applications security.

Connect to database as decribed above.

Select queries

$sql='SELECT lastname, email FROM customers WHERE id > ? AND firstname = ?';

$id_greater_than = 5;

$firstname = 'John';

/* Prepare statement */

$stmt = $conn->prepare($sql);

if($stmt === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */

$stmt->bind_param('is',$id_greater_than,$firstname);

/* Execute statement */

$stmt->execute();

Iterate over results

$stmt->bind_result($lastname, $email);

while ($stmt->fetch()) {

  echo $lastname . ', ' . $email . '<br>';

}

Store all values to array

$rs=$stmt->get_result();

$arr = $rs->fetch_all(MYSQLI_ASSOC);

WARNING: get_result is available only with MySQL Native Driver.

Close statement

$stmt->close();

Insert queries

$sql='INSERT INTO customers (firstname, lastname) VALUES (?,?)';

$firstname = 'John';

$lastname = 'Doe';

/* Prepare statement */

$stmt = $conn->prepare($sql);

if($stmt === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */

$stmt->bind_param('ss',$firstname,$lastname);

/* Execute statement */

$stmt->execute();

echo $stmt->insert_id;

echo $stmt->affected_rows;

$stmt->close();

Update queries

$sql='UPDATE customers SET firstname = ?, lastname = ? WHERE id > ?';

$firstname = 'John';

$lastname = 'Doe';

$id_greater_than = 5;

/* Prepare statement */

$stmt = $conn->prepare($sql);

if($stmt === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */

$stmt->bind_param('ssi',$firstname,$lastname,$id_greater_than);

/* Execute statement */

$stmt->execute();

echo $stmt->affected_rows;

$stmt->close();

Delete queries

$sql='DELETE FROM customers WHERE id > ?';

$id_greater_than = 5;

/* Prepare statement */

$stmt = $conn->prepare($sql);

if($stmt === false) {

  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);

}

/* Bind parameters. TYpes: s = string, i = integer, d = double,  b = blob */

$stmt->bind_param('i',$id_greater_than);

/* Execute statement */

$stmt->execute();

echo $stmt->affected_rows;

$stmt->close();

Disconnect

Optional:

$conn->close();