PDO Statement and WHERE IN() Clause

Contoh :

......................

/**

* @param mixed<integer:array> $kd_kelas

* @param integer $bulan

* @param integer $tahun

* @param integer $semester

* @param string $jenis_absen

* @param string $nis

*/

public static function getDetailInformasiAbsensi($kd_kelas = '', $bulan = '', $tahun = '', $semester = '', $jenis_absen = '',

$nis = '')

{

if(!is_array($kd_kelas)) {

$kd_kelas = explode(",", $kd_kelas);

}

$sum_jenis_absen_sql = ' ';

$jenis_absen = strtolower($jenis_absen);

switch ($jenis_absen) {

case 'hadir':

$sum_jenis_absen_sql = ' sum(absensi.hadir) AS jumlah';

$where_jenis_absen_sql = ' absensi.hadir=1 ';

break;

case 'terlambat':

$sum_jenis_absen_sql = ' sum(absensi.terlambat) AS jumlah ';

$where_jenis_absen_sql = ' absensi.terlambat=1 ';

break;

case 'sakit':

$sum_jenis_absen_sql = ' sum(absensi.sakit) AS jumlah ';

$where_jenis_absen_sql = ' absensi.sakit=1 ';

break;

case 'izin':

$sum_jenis_absen_sql = ' sum(absensi.izin) AS jumlah ';

$where_jenis_absen_sql = ' absensi.izin=1 ';

break;

case 'alpa':

$sum_jenis_absen_sql = ' sum(absensi.alpa) AS jumlah ';

$where_jenis_absen_sql = ' absensi.alpa=1 ';

break;

case 'bolos':

$sum_jenis_absen_sql = ' sum(absensi.bolos) AS jumlah ';

$where_jenis_absen_sql = ' absensi.bolos=1 ';

break;

}

$connection = Propel::getConnection();

$query = "SELECT absensi.nis AS nis,

siswa.nama AS nama,

{$sum_jenis_absen_sql},

GROUP_CONCAT(DATE_FORMAT(absensi.tanggal_absen,'%W, %d-%m-%Y') SEPARATOR ':') AS tanggal

FROM (absensi, siswa) INNER JOIN siswa_kelas

ON (absensi.NIS=siswa_kelas.NIS)

WHERE (siswa_kelas.KD_KELAS IN(".utils::pdo_param_repeat("?",count($kd_kelas)).")

AND absensi.KD_KELAS IN(".utils::pdo_param_repeat("?",count($kd_kelas))."))

AND (siswa_kelas.TAHUN=? AND absensi.TAHUN=?)

AND {$where_jenis_absen_sql} AND date_format(absensi.TANGGAL_ABSEN, '%c') = ?

AND absensi.SEMESTER=? ";

if (!empty($nis)) {

$query .= ' AND absensi.NIS=? ';

}

$query .= ' AND absensi.NIS=siswa.NIS group by absensi.nis ';

$statement = $connection->prepareStatement($query);

$idx = 1 ;

for($i=0;$i<count($kd_kelas);$i++) {

$statement->setInt($idx, $kd_kelas[$i]);

$idx += 1 ;

}

for($i=0;$i<count($kd_kelas);$i++) {

$statement->setInt($idx, $kd_kelas[$i]);

$idx += 1 ;

}

$statement->setInt($idx, $tahun);

$idx += 1 ;

$statement->setInt($idx, $tahun);

$idx += 1 ;

$statement->setInt($idx, $bulan);

$idx += 1 ;

$statement->setInt($idx, $semester);

if (!empty($nis)) {

$idx += 1 ;

$statement->setString($idx, $nis);

}

$resultset = $statement->executeQuery();

return $resultset;

}

......................

utils.class.php

......................

public static function pdo_param_repeat($string, $multiplier) {

$ret = "";

for($i=0;$i<$multiplier;$i++) {

if ($i) $ret.=",";

$ret.= " ".$string." ";

}

return $ret ;

}

......................