Learn the Rules, Break The Rules, and Create the New Ones...

Hi... My name is Rizky Prihanto. You can call me RQ, or Rizky either. I am currently living on Bandung, Indonesia. Had a lot of works and research about Enterprise Information Systems (majoring on education and e-governments). I have bunch of interests (some friends call it 'freakz') about MySQL Opensource Database and now I am one of the administrator of MySQL Indonesia User Group - the opensource community initialized by Sun Microsystems Indonesia.

My Company PT Cinox Media Insani, Bandung, Indonesia. I work here since 2008 and I take responsibility as Chief of Software Architect. My job is about planning, imaginating, fantasy-ing, concepting, and build the infrastructure of the new information systems (or app engines) which going to be implemented.

This blog This is my blog that represent my current opinion, research and experiences about anything in Software Engineering. Written since 2007 (actually) and has been vaccum for a lot of while. And now I wanna ressurrect this blog (optimistically) from the long-long-hibernation with something fresh and new ideas -- still about MySQL, software engineering, development, and may be something managerial here.

About the tagline I've learned the statement above from some paper written by Kent Beck about Extreme Programming (XP) methodology -- some sort of practical software development methods which have no boundaries. That's very inspiring me a lot. I have written some article on this blog that tell my interpretation about that statement here.

My Another Blogs I have classifying my blogs into some sort of genre. The blog that you read here right now is my primary blog that tell you (majoring) about IT stuff. But if you wanna look another side of me, you can visit here, here, here,or here. Hope it'll be interesting for some of you.

Credits I would thanks to Blogger for this great blog platform. Skinpress who designed this Wordpress template (which is bloggerized by Free Blogger Templates). My appreciate is also going to you who give your generously time for visiting my blog.

Object-Oriented Interface dalam PHP 5

Salah satu kelebihan PHP 5 adalah interface untuk OOP. Cara koneksi ke database MySQL pun dapat pula dilakukan dengan gaya pemrograman berorientasi obyek. Berikut ini adalah contoh interaksi antara PHP dengan MySQL melalui metode Object-Oriented interface :

$db = new mysqli('192.168.0.192', 'root', '');

$db->select_db('dbtesting');

$sql = "SELECT id, nama FROM tblorangkeren";

$dset = $db->query($sql); /*simpan hasil query ke dataset $dset*/

while ($rs = $dset->fetch_row()) /*retreive recordset ke $rs*/

{

echo "ID: $rs[0] Nama: $rs[1]
";

}

echo "


Jumlah record : " . $dset->num_rows;

$dset->close();

Untuk mengeksekusi stored procedures pada MySQL 5, hanya methods dalam mysqli yang bisa melakukannya. Stored procedure tidak akan dapat di-retreive dengan menggunakan PHP class library mysql klasik. Berikut ini akan dijelaskan mengenai cara memanggil stored procedure dalam berbagai kondisi :

1. Memanggil stored procedure yang tidak mengembalikan record

$sql = "CALL spInsertBuku(@IDBuku, 'PHP5', 'Qvezst', 'Elexmedia')";

$ok = $db->real_query($sql);

2. Memanggil stored procedure yang mengembalikan record (satu perintah SELECT)

$sql = "CALL spGetDataBuku()";

$dset = $db->query($sql); /*simpan hasil query ke dataset $dset*/

3. Memanggil stored procedure yang mengembalikan beberapa record dari beberapa perintah SELECT

$sql = "CALL spGetDataBukuTrusGetDataAnggota()";

$ok = $db->multi_query($sql);

if($ok) {

$i = 1;

do {

echo "Hasil Query : " . $i;

$dset = $db->store_result();

if($dset->num_rows>0 && $dset->field_count>0)

{

while($rs = $dset->fetch_row())

{

echo $rs[0] . " | " . $rs[1] . "
"
;

}

}

$i = $i + 1;

} while ($db->next_result());

}

$dset->close();

4. Membuat stored procedure

Khusus untuk jenis interaksi ini, method yang dapat dipergunakan hanyalah real_query(), dengan pengeksekusian untuk setiap baris SQL secara terpisah. Berikut ini adalah contohnya :

$sql = "DROP PROCEDURE IF EXISTS spBlablabla";

$ok = $db->real_query($sql);

$sql = "DELIMITER |";

$ok = $db->real_query($sql);

$sql = "CREATE PROCEDURE spBlablabla (OUT abc integer)

BEGIN

DECLARE nama VARCHAR(150);

END

| ";

$ok = $db->real_query($sql);

$sql = "DELIMITER ;";

$ok = $db->real_query($sql);

Pernyataan multi_query tidak dapat dipergunakan untuk pengiriman query DDL seperti pembuatan stored procedure ini. Hal ini dikarenakan arsitektur method multi_query sebenarnya melakukan split query string ke dalam array dengan delimiter ; (semicolon) sebagai penanda pemisahnya dan mengirimkan array berisi perintah-perintah SQL tersebut satu per satu ke DBMS. Dalam stored procedure, semicolon dipergunakan sebagai pemisah baris dalam body stored procedure. Jika mempergunakan multi_query, maka stored procedure tersebut tidak akan pernah dieksekusi secara utuh dan akan selalu gagal.

7 comments:

annots mengatakan...

Makasih buat sharenya, ditunggu artikel yang bahas PHP berikutnya.

Salam,

laling mengatakan...

wah bagus sekali ni,saya pun tengah mengkaji dan mempelajari benda ni semua terus menyambung informasi ya! bookmark blog kamu

Johan mengatakan...

$sql = "DROP PROCEDURE IF EXISTS spBlablabla";
$ok = $db->real_query($sql);
$sql = "DELIMITER |";
$ok = $db->real_query($sql);
$sql = "CREATE PROCEDURE spBlablabla (OUT abc integer)
BEGIN
DECLARE nama VARCHAR(150);

END
| ";
$ok = $db->real_query($sql);
$sql = "DELIMITER ;";
$ok = $db->real_query($sql);



Hi.. thx buat infonya..
saya ingin bertanya.. code diatas mengapa tidak dapat berjalan.. seperti nya fungsi real_query() tidak dapat terbaca.. ini code saya :

------------------------------------
$conn = mysqli_connect("localhost","root","","store_procedure") or die("Database not connected");

$sql = "DROP PROCEDURE IF EXISTS spBlablabla";
$ok = $db->real_query($sql);
$sql = "DELIMITER |";
$ok = $db->real_query($sql);
$sql = "CREATE PROCEDURE spBlablabla (OUT abc integer)
BEGIN
DECLARE nama VARCHAR(150);

END
| ";
$ok = $db->real_query($sql);
$sql = "DELIMITER ;";
$ok = $db->real_query($sql);
------------------------------------

Error yg saya dapatkan seperti ini :
Fatal error: Call to a member function real_query() on a non-object in C:\wamp\www\total_football\install\create_stored_procedure.php on line 5

qvezst mengatakan...

untuk laling :
terima kasih banyak udah mengunjungi situs saya. awak dari negri jiran ya? hehehe...

untuk johan,
kalo kulihat coding mu, yang 'kelolosan' ada baris pertama code mu:

$conn = mysqli_connect("localhost","root","","store_procedure") or die("Database not connected");

variabel yang kmu pake $conn kan? ya bener lah php akan raise error ngga bs jalanin $db->real_query() soalnya object $db lom kmu buat.. hehehe... coba deh ganti jd $conn->real_query()

bhletchl mengatakan...

buat mas johan -

ganti baris $conn = mysqli_connect(...
dengan $db = new mysqli(...

dimensi mengatakan...

function getLanguages()
{
$this->db->select('lang_code, lang_name');
$this->db->from('languages');
$langQuery = $this->db->get();
$languages = array();
if ($langQuery->num_rows() > 0)
{
foreach ($langQuery->result_array() as $langRow)
{
$languages[] = $langRow;
}
}
return $languages;
}
}


Saya dapat pesan error :

Fatal error: Call to a member function num_rows() on a non-object in /home/sloki/user/interpol/sites/aa.poconggaul.com/www/application/models/applicationmodel.php on line 89

Mohon Pencerahan mas...

Muhyazid mengatakan...

mungkin $this->db-> harus diganti,
dengan $this->$db->

Posting Komentar