Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- DROP TABLE IF EXISTS tbl_siswa;
- CREATE TABLE IF NOT EXISTS tbl_siswa(
- id INT AUTO_INCREMENT PRIMARY KEY,
- nis VARCHAR(10),
- nama VARCHAR(50),
- hobi VARCHAR(255)
- );
- INSERT INTO tbl_siswa(nis,nama,hobi)
- VALUES
- ('2017010001','Andi','1,4,5'),
- ('2017010002','Budi','1,2,4'),
- ('2017010003','Carla','2,3'),
- ('2017010004','Dewi','3,5'),
- ('2017010005','Edwin','2,3,4,5');
- SELECT * FROM tbl_siswa;
- +----+------------+-------+---------+
- | id | nis | nama | hobi |
- +----+------------+-------+---------+
- | 1 | 2017010001 | Andi | 1,4,5 |
- | 2 | 2017010002 | Budi | 1,2,4 |
- | 3 | 2017010003 | Carla | 2,3 |
- | 4 | 2017010004 | Dewi | 3,5 |
- | 5 | 2017010005 | Edwin | 2,3,4,5 |
- +----+------------+-------+---------+
- DROP TABLE IF EXISTS tbl_hobi;
- CREATE TABLE IF NOT EXISTS tbl_hobi(
- id INT AUTO_INCREMENT PRIMARY KEY,
- hobi VARCHAR(255)
- );
- INSERT INTO tbl_hobi(hobi)
- VALUES
- ('membaca'),
- ('olahraga'),
- ('traveling'),
- ('menyanyi'),
- ('melukis');
- SELECT * FROM tbl_hobi;
- +----+-----------+
- | id | hobi |
- +----+-----------+
- | 1 | membaca |
- | 2 | olahraga |
- | 3 | traveling |
- | 4 | menyanyi |
- | 5 | melukis |
- +----+-----------+
- SELECT
- a.id,a.nis,a.nama, GROUP_CONCAT(b.hobi) AS hobi
- FROM
- tbl_siswa a
- JOIN tbl_hobi b ON b.id REGEXP CONCAT('^(',REPLACE(a.hobi,',','|'),')$')
- GROUP BY a.id;
- +----+------------+-------+-------------------------------------+
- | id | nis | nama | hobi |
- +----+------------+-------+-------------------------------------+
- | 1 | 2017010001 | Andi | menyanyi,membaca,melukis |
- | 2 | 2017010002 | Budi | olahraga,menyanyi,membaca |
- | 3 | 2017010003 | Carla | traveling,olahraga |
- | 4 | 2017010004 | Dewi | traveling,melukis |
- | 5 | 2017010005 | Edwin | menyanyi,melukis,olahraga,traveling |
- +----+------------+-------+-------------------------------------+
- SELECT
- a.id,a.hobi,IF(b.id IS NULL,'',' selected') AS attribute
- FROM
- tbl_hobi a
- LEFT JOIN tbl_siswa b ON (a.id REGEXP CONCAT('^(',REPLACE(b.hobi,',','|'),')$') AND b.id=2)
- +----+-----------+-----------+
- | id | hobi | attribute |
- +----+-----------+-----------+
- | 1 | membaca | selected |
- | 2 | olahraga | selected |
- | 3 | traveling | |
- | 4 | menyanyi | selected |
- | 5 | melukis | |
- +----+-----------+-----------+
- */
- //---
- $id_siswa=$_GET['id_siswa'];
- $sql="
- SELECT
- a.id,a.hobi,IF(b.id IS NULL,'',' selected') AS attribute
- FROM
- tbl_hobi a
- LEFT JOIN tbl_siswa b ON (a.id REGEXP CONCAT('^(',REPLACE(b.hobi,',','|'),')\$') AND b.id={$id_siswa})
- ";
- $result=mysql_query($sql);
- echo "<select multiple name='hobi'>";
- while($row=mysql_fetch_assoc($result)){
- echo "<option value='{$row['id']}'{$row['attribute']}>{$row['hobi']}</option>";
- }
- ?>
- </select>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement