Baik.. jangan sampai kasus tersebut menjadi problematika hidup bagi kalian yang lagi bingung dan suntuk ngubek ngubek stackoverflow.
Berikut adalah query nya..
SELECT
SUBSTRING(enums.value, 2,CHAR_LENGTH(enums.value) - 2) as student_status,
SUBSTRING(enums.value, 2,CHAR_LENGTH(enums.value) - 2) as student_status
FROM
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6), ',', n.n), ',', -1) value
FROM information_schema.COLUMNS t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE
t.TABLE_SCHEMA='"db_name"' */ /* Data Base */
AND t.TABLE_NAME='table_name' /* Table */
AND t.COLUMN_NAME='column_name' /* Field */
AND n.n <= 1 + (LENGTH(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6)) - LENGTH(REPLACE(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6), ',', '')))
ORDER BY value) enums
Nah jika anda terbiasa dengan library adodb ini sangat memudahkan jika Anda memakai fungsi dari GetMenu2(), dan jika diimplementasikan akan menjadi seperti dibawah ini.
function cl_enum($db,$table_name,$column_name,$data,$property)
{
$sql = "SELECT
SUBSTRING(enums.value, 2,CHAR_LENGTH(enums.value) - 2) as student_status,
SUBSTRING(enums.value, 2,CHAR_LENGTH(enums.value) - 2) as student_status
FROM
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6), ',', n.n), ',', -1) value
FROM information_schema.COLUMNS t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE
/* t.TABLE_SCHEMA='".$mcf['db_name']."' */ /* Data Base */
/*AND*/ t.TABLE_NAME='$table_name' /* Table */
AND t.COLUMN_NAME='$column_name' /* Field */
AND n.n <= 1 + (LENGTH(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6)) - LENGTH(REPLACE(SUBSTRING(COLUMN_TYPE,6,CHAR_LENGTH(COLUMN_TYPE) - 6), ',', '')))
ORDER BY value) enums";
$rs = $db->Execute($sql);
$combo = $rs->GetMenu2($column_name,$data,true,false,0, $property);
return $combo;
}
Nah demikian tutorial singkat ini mudah-mudahan bermanfaat, terima kasih kepada Stackoverflow yang senantiasa memberikan pencerahan kepada kami programmer pemula.
bisa lebih di sederhanakan bos, saya lagi belajar
ReplyDelete