Bagi yang baru belajar bahasa pemrograman, entah apapun bahasanya maka bisa dipastikan akan melakukan hal ini: meletakkan query database di dalam loop (perulangan).
Hal ini juga terjadi bagi sebagian programmer yang sudah lama mengerti tentang programming dan malah sudah diposting pada blog mereka!!!
Hal ini harus diantisipasi sejak awal.. kenapa? karena jika aplikasi sudah kompleks maka bisa dipastikan akan “lelah” untuk memperbaikinya, untuk itu mari kita antisipasi sejak dini..
Kembali ke topik…
Apa maksud Query MySQL di Dalam Loop PHP? ya sesuai artinya, kita mengeksekusi query mysql di dalam perulangan PHP, bisa jadi perulangan for, while, dan foreach…
Query yang dimaksud disini adalah semua statemen SQL ( baik SELECT, INSERT, maupun UPDATE) kecuali DELETE.
Agar tidak terlalu panjang, kita batasi pembahasan hanya pada statemen yang sering digunakan, yaitu: INSERT dan SELECT.
I. Query Insert MySQL Pada Perulangan PHP
Pada bagian pertama ini, kita bahas query INSERT di dalam loop PHP.
Langsung saja, kita bahas beberapa kasus…
Studi Kasus #1
Beberapa waktu lalu saya mendapat email dari pengunjung web ini dengan permasalahan kurang lebih seperti ini:
Rekan kita ingin memasukkan data sebanyak kurang lebih 400 data, namun baru sampai data ke 8, data tidak mau masuk, tidak ada pesan kesalahan apapun….!!!
Lantas dimana kesalahannya?
Ketika saya melihat kode yang ada, saya langsung cari apakah ada query insert di dalam loop? dan ternyata ada!!! Saya langsung tahu, pasti disini masalahnya, kodenya kurang lebih seperti ini:
...
<tbody>
<?php
$urut = 0;
for ($i=0;$i<count($hasilrangking);$i++){
?>
<tr>
<td><?php echo $urut=($i+1);?></td>
<td><?php echo $alternatifrangking[$i];?></td>
<td><?php echo number_format($hasilrangking[$i],4); ?></td>
</tr>
<tbody>
<?php
$sql="INSERT INTO ranking_saw (nama, hasil, rankingsaw) VALUES
('".$alternatifrangking[$i]."','".$hasilrangking[$i]."','".$urut."')";
mysql_query($sql);
}?>
</tbody>
...Sudah terlihat kan kesalahannya dimana?
Yup, betul sekali, ada fungsi mysql_query di dalam loop for, jika banyaknya loop ada 400, maka akan ada 400 statemen INSERT, bagaimana jika 1.000?
Dampaknya?
Overhead!!!, server MySQL Anda akan hang…. proses insert akan berhenti dan data tidak masuk.
Solusinya?
Lantas bagaimana solusinya?
Solusinya, gunakan hanya sekali statemen INSERT untuk banyak row sekaligus, format SQL Querynya adalah sebagai berikut:
INSERT INTO tabel (col1, col2, col3) VALUES (val1, val2, val3), (val1, val2, val3), ...Bagaimana penerapannya pada PHP? Caranya, gunakan loop untuk menyusun query INSERT nya, setelah itu eksekusi query tersebut.
Perhatikan script berikut:
...
<tbody>
<?php
$sql="INSERT INTO ranking_saw (nama, hasil, rankingsaw) VALUES ";
$urut = 0;
for ($i=0;$i<count($hasilrangking);$i++)
{?>
<tr>
<td><?php echo $urut=($i+1);?></td>
<td><?php echo $alternatifrangking[$i];?></td>
<td><?php echo number_format($hasilrangking[$i],4); ?></td>
</tr>
<?php
$sql .= "('".$alternatifrangking[$i]."','".$hasilrangking[$i]."','".$urut."'), ";
}
$sql = rtrim($sql, ', ');
mysql_query($sql);
?>
</tbody>
...Pada script diatas, loop for akan membentuk query berikut:
$sql='INSERT INTO ranking_saw (nama, hasil, rankingsaw) VALUES ("..." , "..." , "..."), ("..." , "..." , "..."), ...';Selanjutnya, kita hanya perlu mengeksekusi satu statement INSERT tersebut…
INSERT, maka kita perlu memperhatikan kekuatan server, untuk amannya, maksimal 3.000 data sekali insert, jika lebih dari itu, buat statemen INSERT yang baruStudi Kasus #2
Kasus kedua ini berkaitan dengan form input HTML, perhatikan script berikut:
<?php
if($_POST['submit']){
$conn = mysqli_connect('localhost', 'root', '', 'universitas');
foreach ($_POST['matkul'] AS $kd_matkul) {
$sql = 'INSERT INTO mahasiswa_matkul (nim, kd_matkul) VALUES ("'.$_POST['nim'].'", "'.$kd_matkul.'")';
$insert = mysqli_query($conn, $sql);
}
}Data tersebut akan memasukkan data mata kuliah yang dipilih oleh mahasiswa.
Model seperti ini banyak kita temui di internet, jika penasaran googling sendiri ya…
Apakah ada yang salah dengan kode ini?
Tidak…. kode ini 100% work dan berjalan dengan baik.
Lalu, dimana letak kesalahannya?
Dengan script diatas, jika data yang diinput sedikit, tidak masalah, namun jika datanya banyak, maka akan berpotensi menimbulkan masalah serius.
Kenapa?
Karena kita mengeksekusi statemen INSERT berkali kali sebanyak perulangan yang ada, seperti yang terjadi pada Kasus #1
Lalu seharusnya seperti apa?
Sama seperti Kasus #1, perhatikan query berikut:
<?php
if($_POST['submit']) {
$conn = mysqli_connect('localhost', 'root', '', 'universitas');
$sql = 'INSERT INTO mahasiswa_matkul (nim, kd_matkul) VALUES ';
foreach ($_POST['matkul'] AS $kd_matkul) {
$sql .= '("'.$_POST['nim'].'", "'.$kd_matkul.'"),';
}
$sql .= rtrim($sql, ',');
$insert = mysqli_query($conn, $sql);
}Pada query diatas, kita hanya mengeksekusi satu perintah sql, berapapun jumlah data yang mahasiswa yang akan dimasukkan…
II. Perulangan Query Select Pada PHP Loop
Pada bagian kedua ini kita akan membahas query SELECT didalam loop PHP
Ada ya bentuk seperti itu?
Ada, dan ini riil terjadi dilapangan, coba Anda googling sendiri ya….
Studi Kasus #1
Pada contoh kasus pertama ini kita akan menampilkan data kategori beserta sub kategorinya, misal kita memiliki tabel kategori sebagai berikut:
+-------------+------------------+--------+
| id_kategori | nama_kategori | parent |
+-------------+------------------+--------+
| 1 | Kategori 1 | 0 |
| 2 | Kategori 2 | 0 |
| 3 | Kategori 3 | 0 |
| 4 | Kategori 4 | 0 |
| 5 | Kategori 5 | 0 |
| 6 | Sub Kategori 1-1 | 1 |
| 7 | Sub Kategori 1-2 | 1 |
| 8 | Sub Kategori 1-3 | 1 |
| 9 | Sub Kategori 1-4 | 1 |
| 10 | Sub Kategori 1-5 | 1 |
| 11 | Sub Kategori 1-6 | 1 |
| 12 | Sub Kategori 4-1 | 4 |
| 13 | Sub Kategori 4-2 | 4 |
| 14 | Sub Kategori 4-3 | 4 |
| 15 | Sub Kategori 4-4 | 4 |
| 16 | Sub Kategori 4-5 | 4 |
| 17 | Sub Kategori 2-1 | 2 |
| 18 | Sub Kategori 2-2 | 2 |
| 19 | Sub Kategori 2-3 | 2 |
| 20 | Sub Kategori 2-4 | 2 |
| 21 | Sub Kategori 2-5 | 2 |
| 22 | Sub Kategori 3-1 | 3 |
| 23 | Sub Kategori 3-2 | 3 |
| 24 | Sub Kategori 3-3 | 3 |
| 25 | Sub Kategori 3-4 | 3 |
| 26 | Sub Kategori 3-5 | 3 |
| 27 | Sub Kategori 3-6 | 3 |
| 28 | Sub Kategori 5-1 | 5 |
| 29 | Sub Kategori 5-2 | 5 |
| 30 | Sub Kategori 5-3 | 5 |
| 31 | Sub Kategori 5-4 | 5 |
| 32 | Sub Kategori 5-5 | 5 |
+-------------+------------------+--------+Format output yang kita inginkan adalah:
<ul class="main-category">
<li>Kategori 1
<ul>
<li>Sub Kategori 1-1</li>
<li>Sub Kategori 1-2</li>
<li>Sub Kategori 1-3</li>
<li>Sub Kategori 1-4</li>
<li>Sub Kategori 1-5</li>
<li>Sub Kategori 1-6</li>
</ul>
</li>
<li>Kategori 2
<ul>
<li>Sub Kategori 2-1</li>
<li>Sub Kategori 2-2</li>
<li>Sub Kategori 2-3</li>
<li>Sub Kategori 2-4</li>
<li>Sub Kategori 2-5</li>
</ul>
</li>
<li>Kategori 3
<ul>
<li>Sub Kategori 3-1</li>
<li>Sub Kategori 3-2</li>
<li>Sub Kategori 3-3</li>
<li>Sub Kategori 3-4</li>
<li>Sub Kategori 3-5</li>
<li>Sub Kategori 3-6</li>
</ul>
</li>
<li>Kategori 4
<ul>
<li>Sub Kategori 4-1</li>
<li>Sub Kategori 4-2</li>
<li>Sub Kategori 4-3</li>
<li>Sub Kategori 4-4</li>
<li>Sub Kategori 4-5</li>
</ul>
</li>
<li>Kategori 5
<ul>
<li>Sub Kategori 5-1</li>
<li>Sub Kategori 5-2</li>
<li>Sub Kategori 5-3</li>
<li>Sub Kategori 5-4</li>
<li>Sub Kategori 5-5</li>
</ul>
</li>
</ul>Nah, bagaimana script yang seharusnya kita buat?
Kebanyakan akan membuat script seperti ini:
$conn = mysqli_connect('localhost', 'root', '', 'tutorial_web');
$sql = 'SELECT * FROM kategori WHERE parent = 0';
$query = mysqli_query($conn, $sql);
$first = true;
$html = '<ul class="main-category">';
while($row = mysqli_fetch_array($query))
{
$html .= '<li>' . $row['nama_kategori'] . '<ul>';
$sql_subkat = 'SELECT nama_kategori FROM kategori WHERE parent = ' . $row['id_kategori'];
$query_subkat = mysqli_query($conn, $sql);
while($row_subkat = mysqli_fetch_array($query_subkat)) {
$html .= '<li>' . $row_subkat['nama_kategori'] . '</li>';
}
$html .= '</ul></li>';
}
$html .= '</ul>';
echo $html;Kenapa?
Karena mudah dilogika, betul? yup memang betul, alurnya mudah di mengerti, sehingga alur logikanya mudah disusun.
Apakah Anda juga seperti itu? jika ya, tidak apa apa? karena sebentar lagi Anda akan selangkah lebih maju dari rekan Anda.
Baiklah, mari kita hitung ada berapa query yang akan di eksekusi oleh MySQL: 1 untuk kategori, dan 5 untuk sub kategori, total ada 6, bagaimana jika ada ribuan user yang mengakses aplikasi/web anda…?
Belum lagi jika di bagian lain dari aplikasi kita terdapat hal serupa (kasus #2), tambah berat lagi kinerja database kita…
Seharusnya bagaimana?
Solusi
Untuk model data berjenjang seperti pada contoh diatas, gunakan recursive, perhatikan contoh script versi saya berikut ini:
<?php
$conn = mysqli_connect('localhost', 'root', '', 'tutorial_web');
$sql = 'SELECT id_kategori, nama_kategori, parent FROM kategori';
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query))
{
$tmp[$row['id_kategori']]['nama_kategori'] = $row['nama_kategori'];
if ($row['parent'] == 0) {
$data[$row['id_kategori']] = &$tmp[$row['id_kategori']];
}
else {
$tmp[$row['parent']]['child'][$row['id_kategori']] = &$tmp[$row['id_kategori']];
}
}
function build_cat($array, $child = false) {
$html = '<ul>';
foreach ($array as $arr) {
$html .= '<li>' . $arr['nama_kategori'];
if (key_exists('child', $arr)) {
$html .= build_cat($arr['child'], true);
}
$html .= '</li>' . "\r\n";
}
$html .= '</ul>';
return $html;
}
echo build_cat($data);Pada contoh diatas, kita hanya perlu mengeksekusi satu perintah SQL untuk memperoleh hasil yang sama persis, jauh lebih sedikit dari sebelumnya yang perlu mengeksekusi enam perintah SQL
Jauh lebih efisien bukan?
Studi Kasus #2
Contoh kasus lainnya adalah ketika kita ingin menampilkan data artikel beserta kategorinya dengan kriteria: setiap kategori akan menampilkan 5 artikel terbaru yang diurutkan berdasarkan tanggal terbit.
Familiar dengan model ini? yup… model ini sering kita jumpai pada portal berita, seperti republika, vivanews, dll, atau pada web ecommerce untuk menampilkan item per kategori.
Untuk contoh datanya, kita gunakan tabel kategori pada contoh kasus sebelumnya dan kita gunakan tabel artikel dengan data sebagai berikut:
+------------+---------------+-------------+------------+
| id_artikel | judul_artikel | id_kategori | tgl_terbit |
+------------+---------------+-------------+------------+
| 1 | Artikel 1-1 | 1 | 2017-06-01 |
| 2 | Artikel 1-2 | 1 | 2017-06-02 |
| 3 | Artikel 1-3 | 1 | 2017-06-05 |
| 4 | Artikel 1-4 | 1 | 2017-06-04 |
| 5 | Artikel 1-5 | 1 | 2017-06-06 |
| 6 | Artikel 2-1 | 2 | 2017-05-04 |
| 7 | Artikel 2-2 | 2 | 2017-05-03 |
| 8 | Artikel 2-3 | 2 | 2017-05-13 |
| 9 | Artikel 2-4 | 2 | 2017-05-07 |
| 10 | Artikel 2-5 | 2 | 2017-05-22 |
| 11 | Artikel 3-1 | 3 | 2017-05-04 |
| 12 | Artikel 3-2 | 3 | 2017-05-01 |
| 13 | Artikel 3-3 | 3 | 2017-05-05 |
| 14 | Artikel 3-4 | 3 | 2017-05-10 |
| 15 | Artikel 3-5 | 3 | 2017-05-01 |
| 16 | Artikel 4-1 | 4 | 2017-04-01 |
| 17 | Artikel 4-2 | 4 | 2017-04-02 |
| 18 | Artikel 4-3 | 4 | 2017-03-30 |
| 19 | Artikel 4-4 | 4 | 2017-04-24 |
| 20 | Artikel 4-5 | 4 | 2017-03-11 |
| 21 | Artikel 5-1 | 5 | 2017-03-01 |
| 22 | Artikel 5-2 | 5 | 2017-03-07 |
| 23 | Artikel 5-3 | 5 | 2017-03-02 |
| 24 | Artikel 5-4 | 5 | 2017-03-17 |
| 25 | Artikel 5-5 | 5 | 2017-03-04 |
+------------+---------------+-------------+------------+Hasil yang diharapkan adalah seperti ini:
<ul class="artikel">
<li>Kategori 1
<ul>
<li>Artikel 1-5</li>
<li>Artikel 1-3</li>
<li>Artikel 1-4</li>
<li>Artikel 1-2</li>
<li>Artikel 1-1</li>
</ul>
</li>
<li>Kategori 2
<ul>
<li>Artikel 2-5</li>
<li>Artikel 2-3</li>
<li>Artikel 2-4</li>
<li>Artikel 2-1</li>
<li>Artikel 2-2</li>
</ul>
</li>
<li>Kategori 3
<ul>
<li>Artikel 3-4</li>
<li>Artikel 3-3</li>
<li>Artikel 3-1</li>
<li>Artikel 3-2</li>
<li>Artikel 3-5</li>
</ul>
</li>
<li>Kategori 4
<ul>
<li>Artikel 4-4</li>
<li>Artikel 4-2</li>
<li>Artikel 4-1</li>
<li>Artikel 4-3</li>
<li>Artikel 4-5</li>
</ul>
</li>
<li>Kategori 5
<ul>
<li>Artikel 5-4</li>
<li>Artikel 5-2</li>
<li>Artikel 5-5</li>
<li>Artikel 5-3</li>
<li>Artikel 5-1</li>
</ul>
</li>
</ul>Bagaimana query yang seharusnya kita buat?
atau…
Anda coba bayangkan sejenak query seperti apa yang akan Anda buat?
Kebanyakan akan membuat query seperti ini:
<?php
$conn = mysqli_connect('localhost', 'root', '', 'tutorial_web');
$sql = 'SELECT id_kategori, nama_kategori, parent FROM kategori WHERE parent = 0';
$query = mysqli_query($conn, $sql);
$html = '<ul class="artikel">';
while($row = mysqli_fetch_array($query)) {
$sql_artikel = 'SELECT judul_artikel
FROM artikel
WHERE kategori = '.$row['id_kategori'] . '
ORDER BY tgl_terbit DESC
LIMIT 3';
$query_artikel = mysqli_query($conn, $sql_artikel);
$html .= '<li>' . $row['nama_kategori'] . '<ul>';
while($row_artikel = mysqli_fetch_array($query_artikel)) {
$html .= '<li>' . $row_artikel['judul_artikel'] . '</li>';
}
$html .= '</ul></li>';
}
$html .= '</ul>';
echo $html;Kenapa?
Ya sama seperti contoh sebelumnya.. alur query diatas mudah dimengerti dan dicerna sehingga mudah membuat alur logikanya…
Apakah Anda juga seperti itu? Jika ya, tidak apa-apa sebentar lagi skill Anda akan lebih mumpuni dibanding yang lain.
Baiklah coba kita hitung berapa query yang akan dieksekusi: 1 untuk kategori dan 5 untuk artikel, totalnya 6. Jika ditambah dengan contoh sebelumnya jumlahnya 12, jika di kali 1.000 user, maka sudah 12.000 eksekusi query.
Seharusnya bagaimana?
Solusi…
Coba perhatikan query versi saya berikut ini:
$conn = mysqli_connect('localhost', 'root', '', 'tutorial_web');
$sql = 'SELECT nama_kategori, judul_artikel, tgl_terbit
FROM
( SELECT id_kategori, nama_kategori, judul_artikel, tgl_terbit,
IF ( @curr_idkat = id_kategori, @urut := @urut + 1, @urut := 1) AS urut,
IF ( @curr_idkat != id_kategori, @curr_idkat := id_kategori, NULL) AS curr
FROM artikel
LEFT JOIN kategori USING(id_kategori)
ORDER BY id_kategori, tgl_terbit DESC
) AS tmp
WHERE urut <= 3';
$query = mysqli_query($conn, $sql);
$curr_cat = '';
$html = '<ul class="news">';
$first = true;
while ($row = mysqli_fetch_array($query))
{
if ($curr_cat != $row['nama_kategori']) {
$curr_cat = $row['nama_kategori'];
$close_tag = '</ul></li>';
if ($first) {
$close_tag = '';
$first = false;
}
$html .= $close_tag . '<li>' . $row['nama_kategori'] . '<ul>';
}
$html .= '<li>' . $row['judul_artikel'] . '</li>';
}
$html .= '</ul></li></ul>';
echo $html;Lebih rumit?
Yup, bagi sebagian orang query diatas lebih rumit, tetapi jika mindset Anda sudah terbentuk, Anda akan berusaha bagaimana caranya membuat aplikasi se-efisien mungkin terutama dari sisi database.
Pada contoh script diatas, kita hanya perlu mengeksekusi satu perintah SQL, dibanding cara sebelumnya yang perlu mengeksekusi enam perintah SQL..
Bagaimana jika kategorinya 10… ?
Query kedua tetap hanya perlu satu kali eksekusi perintah SQL, sedangkan cara kedua? Anda tinggal mengalikan saja…
III. Perulangan Query Update Pada PHP Loop
Terkadang kita perlu melakukan updating data dengan kriteria yang ada di dalam loop. Dalam kondisi tertentu model updating data ini tidak dapat terelakkan, sehigga terkadang tidak ada cara lain untuk melakukan optimasi.
Namun demikian, pada kondisi tertentu query update didalam loop dapat dioptimasi jika kriteria updatenya sama, sebagai contoh kita akan mengupdate status user dari non aktif menjadi aktif dengan id user tertentu, misal:
<?php
$sql = "SELECT id_user FROM user WHERE tgl_daftar = "2019-08-24"
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query)) {
$sql = 'UPDATE user SET status = "Aktif" WHERE id_user = ' . $row['id_user'];
mysqli_query($conn, $sql);
}Pada script diatas, perintah update dijalankan sebanyak id_user yang dihasilkan dari query pertama.
Lantas, apakah bisa dioptimasi?
Pada kondisi diatas ya, bisa, caranya kita kumpulkan id_user menjadi satu kemudian kita jalankan perintah update sekali saja, misal:
<?php
$sql = "SELECT id_user FROM user WHERE tgl_daftar = "2019-08-24"
$query = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($query)) {
$id_user[] = $row['id_user'];
}
$sql = 'UPDATE user SET status = "Aktif" WHERE id_user IN (' . join($id_user, ',') . ')';
mysqli_query($conn, $sql);Pada script diatas, terlihat bahwa query update hanya dilakukan sekali sehingga tidak memberatkan server
Penutup
Ketika membangun aplikasi, banyak hal yang harus diperhatikan, tidak hanya bagian front end, tapi back end juga tidak kalah penting.
So.. selalu bentuk mindset kita, bagaimana agar kode yang kita buat selalu efisien.
Demikian pembahasan mengenai meletakkan query MySQL di dalam PHP loop, semoga bermanfaat.