:: Someday i will understand ::

Hmm.. jd berhubung gw udah lama bgt ga coding, dan penyakit mendadak amnesia gw semakin parah. Jadi kayanya ilmu2 kudu (red:kudu belajar yg rajin) hrs dipindahkan pada tempat yg mudah dan gampang diakses kapan aja saat gw butuh . Yah salah satunya adalah blog gw ini, krn kmrn sampai td siang menjelang sore ini bantuin coding KKP(red:Kuliah Kerja Praktek). Dan hal yg sangat disayangkan adl kebetulan chargernya my leppy bermasalah trs si leppy ngambek2 klo ga ada chargernya dia ga mau idup *lebayyyy*, alhasil sbg programer kupipes gw agak kerepotan kehilangan source2 gw yg berharga . Sampai akhirnya gw memutuskan buat posting2 ttg PHP . eMmm krn ga terbiasa nulis tutorial jd maaf2 aja klo bahasanya hanya bisa dimengerti antara Tuhan dan gw hahahaha .

Langsung aja yah, bikin contohnya yg sederhana2 aja

form_biodata.php

<table>

<form action=”proses_biodata.php” method=”post”>
<tr>
<td>Nama</td>
<td><input name=”nama” type=”text”></td>
</tr>
<tr>
<td>Alamat</td>
<td><textarea cols=”20″ rows=”3″ name=”alamat”></textarea>
</td>
</tr>
<tr>
<td>Jenis Kelamin</td>
<td>
<select name=”agama”>
<option value=”Islam”>Islam</option>
<option value=”Katolik”>Katolik</option>
<option value=”Protestan”>Protestan</option>
<option value=”Hindu”>Hindu</option>
<option value=”Budha”>Budha</option>
</select>
</td>
</tr>
<tr>
<td>Jenis Kelamin</td>
<td><input name=”sex” type=”radio” value=”pria” /> Pria
<input name=”sex” type=”radio” value=”wanita” /> Wanita</td>
</tr>
<tr>
<td></td>
<td><input name=”Submit” type=”submit” value=”Submit” /></td>
</tr>
</form>
</table>

Tampilan sbb :

Dari file form_biodata akan mengepost isi form tsb, sblm membuat script cara insert data pd database. Baiknya file koneksi ke database Mysqlnya dibuat file dipisah agar memudahkan saat pemanggilan yg berulang-ulang jika akan digunakan pd file2 yg lain.

connection.php

<?php

#Connection To DataBase

$dbhost = “localhost”; //ini adl letak server Mysqlnya
$dbuser = “root”; // ini diisi sesuai dgn user MySqlnya
$dbpass = “”;  //ini diisi sesuai dngn password MySqlnya
$dbname = “biodata”; // ini adl nama database yg akan digunakan

mysql_connect(”$dbhost”,”$dbuser”,”$dbpass”) or die (”Koneksi gagal”);
mysql_select_db(”$dbname”);

?>

Berikut ini adalah script insert ke databasenya.

proses_biodata.php

<?php
include “connection.php”;

if(isset($_POST['Submit']))
{

$sql    = “INSERT INTO biodata (nama, alamat, agama, sex)
VALUE(’$nama’,'$alamat’,'$agama’,'$sex’)”;
$query         = mysql_query($sql);
}
?>
<script>
alert(” Data telah tersimpan”);
document.location=”form_biodata.php”;
</script>

Fuihh yah begitulah kira2 bikin form biodata sampai insert ke databasenya klo ada salah2 kata boleh dikoreksi , sampai jumpa di script2 lainnya *elap2 keringet*. Ada pesan sponsornya kata MasQu “Cape deh!”.  Me *gubraks* >-

eMmm catetan kecil hari ini yah ini..

C14 - sQL[1]

Jadi ini perintah buat select tgl, trus ngejumlahin per status id, total kesuluruhan dari tableA dimana produk id sama dengan 1.

select to_char(tgl,’YYYYmmdd’) tgl,
sum(decode(status_id,0,1)) st0,
sum(decode(status_id,2,1)) st2,
count ( * ) jml_total
from tableA
where prod_id=’1′
group by To_char(tgl_klm,’YYYYmmdd’)

C14 - sQL[2]

select user_id, sum(st0) as jc0 , sum (jk0) as mo0, sum (st2) as jc2,
sum (jk2)as mo2, sum (jml_total) as jt, sum(jk0+jk2) as mot
from (
select distinct
sum(decode(a.status_id,0,1)) st0,
sum(decode(a.status_id,2,1)) st2,
sum(decode(a.status_id,0,a.jml_klaim)) jk0,
sum(decode(a.status_id,2,a.jml_klaim)) jk2,
b.user_id, count ( * ) jml_total
from tableA a, tableB b
where a.user_id=b.user_id and prod_id=’1′
group by user_id, jml_klaim
)
group by user_id

eMmm special thx buat pa khaidar .Dah ah segitu dulu, mauu maksi ahh laper…

eMmm kadang klo lagi query2 kadang masih suka lupa, jadi sebenernya postingan ini cuma buat catetan kecil yang kapan aja bisa dibuka .

[C17-High Usage]

select * from tableA
where (field1 =’1024′ and field2 between ‘730′ and ‘749′)
or (field1 =’750′ and field2 between ‘730′ and ‘749′)
or (field1 = ‘2048′ and field2 between ‘950′ and ‘1000′)
or (field1 =’2560′ and field2 between ‘950′ and ‘1000′)

dan ternyata bisa digabung jd begini loh, barusan dikoreksi ma bpk guru chiank,makasih yah…

select * from tableA
where (field1 in (’1024′,’750′)and field2 between ‘730′ and ‘749′) or
(field in (’2048′, ‘2560′) and field2 between ‘950′ and ‘1000′)

[C17 - Quota Alert]

select * from tableA
where (field1 =’1024′ and field2 >= ‘750′)
or (field1 =’750′ and field2 >= ‘750′)
or (field1 = ‘2048′ and field2 >= ‘1001′)
or (field1 =’2560′ and field2 >= ‘1001′)

[C17-No Usage]

select * from tableA where field1=’0′

[C12]

select a.field1,a.field2,a.field3
from tableA a, tableB b, tableC c
where a.field1=b.field1 and a.field1 not in (select field1 from tableC)

eMmm segitu dulu deh, lanjut nanti lagi . Spesial thx buat chiank

Nah akhirnya jadi juga semua program dah kelar, sebenernya sih dah kelar dari sebelum libur lebaran kmrn tapi krn blom sempet update blog, yo wes jadinya skrng deh di lengkapinnya. Yup kmrn dah bikin Mysql ke Csv. Nah sekarang cara ngebalikinnya.

Csv to Mysql Code :


< ?php
include "upload_class.php";
include "koneksi.php";
$max_size = 1024*1000;
$my_upload = new file_upload;
$my_upload->upload_dir =”xls/”;
$my_upload->extensions = array(”.xls”,”.txt”,”.csv”);
$my_upload->max_length_filename = 50;
$my_upload->rename_file = false;

if(isset($_POST['Submit']))
{
if(empty($_FILES['upload']['name'])) {
?> < ? }
else {
$my_upload->the_temp_file = $_FILES['upload']['tmp_name'];
$my_upload->the_file = $_FILES['upload']['name'];
$my_upload->http_error = $_FILES['upload']['error'];
$my_upload->replace = (isset($_POST['replace'])) ? $_POST['replace'] : “n”;
$my_upload->do_filename_check = (isset($_POST['check'])) ? $_POST['check'] : “n”;
$new_name = (isset($_POST['name'])) ? $_POST['name'] : “”;
$my_upload->upload($new_name);
$data_j=$my_upload->file_copy;

echo $data_j;
open_connection();
if(empty($data_j)) { ?>
< ? } else {
$dbname='logbook'; mysql_select_db("$dbname");
$CSVFile = $_GET['data_j'];
echo $CSVFile;
if (file_exists("xls/".$CSVFile)) {
$row = 1;
$handle = fopen ("$CSVFile","r");
while ($data = fgetcsv ($handle, 1000, ",")) {
$sql = "INSERT INTO ticket (t_ticket,jn_prog,uraian,estimasi,nik,status,id_ticket) VALUES ('".$data[0]."','".$data[2]."','".$data[3]."','".$data[4]."' ,'".$data[1]."','".$data[7]."','".$data[8]."'); INSERT INTO kegiatan (t_ticket,t_giat,kegiatan,jn_prog) VALUES ('".$data[0]."','".$data[5]."','".$data[6]."','".$data[2]."')";
$do = mysql_query($sql) or die(mysql_error());
echo "$sql";
echo "\n"; echo "\n";
} fclose ($handle);
} else { echo "No file in folder!!"; }
}
}
?>


Source : irene87.blogsome.com/csv2mysql

eMmm ini script Mysql ke Cvs, yah niatnya mo di jadiin koleksi *halah barang kalee koleksi *. Dari kemaren keq ketemu ilhamnya *semalem ketemu ilham dikampus*, ilham orang beneran tp dia bukan inspirasi codingnya koq jd ga ada hubungannya. Tinggal bikin Csv ke Mysqlnya, yang ini masih underconstruction belum nemu ilham2 yang lain *halah *.

Mysql to CSV code :

include "koneksi.php";
open_connection();
$filename = "".$x."_".$y.".csv";
$sql = "select a.id_ticket,a.t_ticket, a.nik, a.jn_prog, a.uraian, a.estimasi,a.status, b.t_giat, b.kegiatan from ticket a, kegiatan b where a.t_ticket=b.t_ticket and a.t_ticket >= ‘$tggl1′ and a.t_ticket < = '$tggl2' and a.jn_prog=b.jn_prog";
//echo $sql;
$query = mysql_query($sql);

while ($row = mysql_fetch_object($query))
{
$t_ticket = $row->t_ticket;
$nik = $row->nik; $jn_prog =
$row->jn_prog; $uraian = $row->uraian;
$estimasi = $row->estimasi;
$t_giat = $row->t_giat;
$kegiatan = $row->kegiatan;
$somecontent .= $t_ticket.”,”.$nik.”,”.$jn_prog.”,”.$uraian.”,”.$estimasi.”,”.$t_giat.”,”.$kegiatan.”\n”;
}
$fp = fopen($filename, “w”);
$oke=fputs($fp , $somecontent);

if ($oke) {
echo “Data ${x}_${y}.csv Successfully create.”;
echo “”; echo “[back]“;
} else
echo “Data not create”;
?>


source : My old blogsome Mysql2csv