Membangun Dynamic Query (Class GetPets)

Gambaran Umum

Dua video ini (Part 1 & 2) membahas pembuatan class GetPets yang secara dinamis membangun SQL query berdasarkan URL parameters. Termasuk notes tentang PHP arrays dan isset() warnings.

---

Konsep: Dynamic Query Builder

URL seperti ?species=dog&favcolor=green&minweight=20&maxweight=50 harus menghasilkan:

SELECT * FROM wp_pets WHERE species = 'dog' AND favecolor = 'green' AND petweight >= 20 AND petweight <= 50 LIMIT 100

---

Struktur Class GetPets

File Terpisah (inc/GetPets.php)

<?php

class GetPets {
  function __construct() {
    global $wpdb;
    $this->tableName = $wpdb->prefix . 'pets';
    
    // Step 1: Kumpulkan & sanitize URL arguments
    $this->args = $this->getArgs();
    
    // Step 2: Buat array placeholder values untuk prepare()
    $this->placeholders = $this->createPlaceholders();
    
    // Step 3: Bangun query string
    $query = "SELECT * FROM {$this->tableName} ";
    $query .= $this->createWhereText();
    $query .= " LIMIT 100";
    
    // Step 4: Bangun count query
    $countQuery = "SELECT COUNT(*) FROM {$this->tableName} ";
    $countQuery .= $this->createWhereText();
    
    // Step 5: Execute queries
    $this->pets = $wpdb->get_results(
      $wpdb->prepare($query, $this->placeholders)
    );
    
    $this->count = $wpdb->get_var(
      $wpdb->prepare($countQuery, $this->placeholders)
    );
  }
}

Di Template (template-pets.php)

<?php
require_once plugin_dir_path(__FILE__) . 'GetPets.php';
$getPets = new GetPets();
?>

<p>Found <?php echo number_format($getPets->count); ?> results, 
   showing the first <?php echo count($getPets->pets); ?>.</p>

<table>
  <?php foreach($getPets->pets as $pet) { ?>
    <tr>
      <td><?php echo $pet->petname; ?></td>
      <!-- ... kolom lainnya ... -->
    </tr>
  <?php } ?>
</table>

---

Method getArgs() — Sanitize URL Parameters

Versi dengan isset() (Recommended)

function getArgs() {
  $temp = [];
  
  if (isset($_GET['favcolor'])) $temp['favcolor'] = sanitize_text_field($_GET['favcolor']);
  if (isset($_GET['species'])) $temp['species'] = sanitize_text_field($_GET['species']);
  if (isset($_GET['minyear'])) $temp['minyear'] = sanitize_text_field($_GET['minyear']);
  if (isset($_GET['maxyear'])) $temp['maxyear'] = sanitize_text_field($_GET['maxyear']);
  if (isset($_GET['minweight'])) $temp['minweight'] = sanitize_text_field($_GET['minweight']);
  if (isset($_GET['maxweight'])) $temp['maxweight'] = sanitize_text_field($_GET['maxweight']);
  if (isset($_GET['favhobby'])) $temp['favhobby'] = sanitize_text_field($_GET['favhobby']);
  if (isset($_GET['favfood'])) $temp['favfood'] = sanitize_text_field($_GET['favfood']);
  
  return $temp;
}

Catatan: Versi awal tanpa isset() menyebabkan PHP warning di environment modern. Selalu gunakan isset() sebelum mengakses $_GET.

Kenapa sanitize_text_field()?

• Membersihkan input dari HTML tags, encoding tricks, dll.
• Layer pertama keamanan (sebelum $wpdb->prepare())
• Hanya ambil parameter spesifik yang kita harapkan — abaikan sisanya

---

Method createPlaceholders()

function createPlaceholders() {
  return array_map(function($x) {
    return $x;  // Hanya ambil values, buang keys
  }, $this->args);
}

Catatan dari instruktur: Method ini sebenarnya tidak diperlukan — $this->args bisa langsung digunakan sebagai argumen kedua $wpdb->prepare(). PHP tidak membedakan indexed vs associative array saat dipass ke prepare().

---

Method createWhereText()

function createWhereText() {
  $whereQuery = '';
  
  if (count($this->args) > 0) {
    $whereQuery = "WHERE ";
    $currentPosition = 0;
    
    foreach($this->args as $index => $item) {
      $whereQuery .= $this->specificQuery($index);
      
      // Tambah AND kecuali item terakhir
      if ($currentPosition != count($this->args) - 1) {
        $whereQuery .= " AND ";
      }
      $currentPosition++;
    }
  }
  
  return $whereQuery;
}

Cara kerja:

1. Jika tidak ada args → return string kosong (query tanpa WHERE)
2. Loop setiap argument → tambah kondisi SQL
3. Antara setiap kondisi → tambah AND
4. Item terakhir → tanpa AND di belakangnya

---

Method specificQuery() — Switch Statement

function specificQuery($index) {
  switch($index) {
    case 'minweight':
      return "petweight >= %d";
    case 'maxweight':
      return "petweight <= %d";
    case 'minyear':
      return "birthyear >= %d";
    case 'maxyear':
      return "birthyear <= %d";
    default:
      return "$index = %s";
  }
}

Kenapa Switch Statement?

URL Parameter	Kolom Database	Operator
species	species	= %s (exact match)
favcolor	favecolor	= %s (exact match)
minweight	petweight	>= %d (greater or equal)
maxweight	petweight	<= %d (less or equal)
minyear	birthyear	>= %d
maxyear	birthyear	<= %d

• Default case menangani semua property dimana nama URL parameter = nama kolom database & tipenya string
• Specific cases untuk parameter yang namanya berbeda dari nama kolom, atau yang pakai operator >= / <=

---

Count Query dengan $wpdb->get_var()

$this->count = $wpdb->get_var(
  $wpdb->prepare($countQuery, $this->placeholders)
);

• get_var() → return satu nilai tunggal (bukan array/object)
• SELECT COUNT(*) ... → menghitung total records yang match
• Bisa ditampilkan sebagai "Found X results"
• Dua query terpisah (data + count) → lebih performant daripada satu query gabungan