Advertisement
cdsatrian

Serialized values

Feb 23rd, 2017
219
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 2.86 KB | None | 0 0
  1. /*
  2. DROP TABLE IF EXISTS tbl_siswa;
  3. CREATE TABLE IF NOT EXISTS tbl_siswa(
  4.     id INT AUTO_INCREMENT PRIMARY KEY,
  5.     nis VARCHAR(10),
  6.     nama VARCHAR(50),
  7.     hobi VARCHAR(255)
  8. );
  9.  
  10. INSERT INTO tbl_siswa(nis,nama,hobi)
  11. VALUES
  12. ('2017010001','Andi','1,4,5'),
  13. ('2017010002','Budi','1,2,4'),
  14. ('2017010003','Carla','2,3'),
  15. ('2017010004','Dewi','3,5'),
  16. ('2017010005','Edwin','2,3,4,5');
  17.  
  18. SELECT * FROM tbl_siswa;
  19. +----+------------+-------+---------+
  20. | id | nis        | nama  | hobi    |
  21. +----+------------+-------+---------+
  22. |  1 | 2017010001 | Andi  | 1,4,5   |
  23. |  2 | 2017010002 | Budi  | 1,2,4   |
  24. |  3 | 2017010003 | Carla | 2,3     |
  25. |  4 | 2017010004 | Dewi  | 3,5     |
  26. |  5 | 2017010005 | Edwin | 2,3,4,5 |
  27. +----+------------+-------+---------+
  28.  
  29. DROP TABLE IF EXISTS tbl_hobi;
  30. CREATE TABLE IF NOT EXISTS tbl_hobi(
  31.     id INT AUTO_INCREMENT PRIMARY KEY,
  32.     hobi VARCHAR(255)
  33. );
  34.  
  35. INSERT INTO tbl_hobi(hobi)
  36. VALUES
  37. ('membaca'),
  38. ('olahraga'),
  39. ('traveling'),
  40. ('menyanyi'),
  41. ('melukis');
  42.  
  43. SELECT * FROM tbl_hobi;
  44. +----+-----------+
  45. | id | hobi      |
  46. +----+-----------+
  47. |  1 | membaca   |
  48. |  2 | olahraga  |
  49. |  3 | traveling |
  50. |  4 | menyanyi  |
  51. |  5 | melukis   |
  52. +----+-----------+
  53.  
  54. SELECT
  55.     a.id,a.nis,a.nama, GROUP_CONCAT(b.hobi) AS hobi
  56. FROM
  57.     tbl_siswa a
  58.     JOIN tbl_hobi b ON b.id REGEXP CONCAT('^(',REPLACE(a.hobi,',','|'),')$')
  59. GROUP BY a.id;
  60. +----+------------+-------+-------------------------------------+
  61. | id | nis        | nama  | hobi                                |
  62. +----+------------+-------+-------------------------------------+
  63. |  1 | 2017010001 | Andi  | menyanyi,membaca,melukis            |
  64. |  2 | 2017010002 | Budi  | olahraga,menyanyi,membaca           |
  65. |  3 | 2017010003 | Carla | traveling,olahraga                  |
  66. |  4 | 2017010004 | Dewi  | traveling,melukis                   |
  67. |  5 | 2017010005 | Edwin | menyanyi,melukis,olahraga,traveling |
  68. +----+------------+-------+-------------------------------------+
  69.  
  70.  
  71. SELECT
  72.     a.id,a.hobi,IF(b.id IS NULL,'',' selected') AS attribute
  73. FROM
  74.     tbl_hobi a
  75.     LEFT JOIN tbl_siswa b ON (a.id REGEXP CONCAT('^(',REPLACE(b.hobi,',','|'),')$') AND b.id=2)
  76.  
  77. +----+-----------+-----------+
  78. | id | hobi      | attribute |
  79. +----+-----------+-----------+
  80. |  1 | membaca   |  selected |
  81. |  2 | olahraga  |  selected |
  82. |  3 | traveling |           |
  83. |  4 | menyanyi  |  selected |
  84. |  5 | melukis   |           |
  85. +----+-----------+-----------+
  86. */
  87.  
  88. //---
  89. $id_siswa=$_GET['id_siswa'];
  90. $sql="
  91.     SELECT
  92.         a.id,a.hobi,IF(b.id IS NULL,'',' selected') AS attribute
  93.     FROM
  94.         tbl_hobi a
  95.         LEFT JOIN tbl_siswa b ON (a.id REGEXP CONCAT('^(',REPLACE(b.hobi,',','|'),')\$') AND b.id={$id_siswa})
  96. ";
  97. $result=mysql_query($sql);
  98. echo "<select multiple name='hobi'>";
  99. while($row=mysql_fetch_assoc($result)){
  100.     echo "<option value='{$row['id']}'{$row['attribute']}>{$row['hobi']}</option>";
  101. }
  102. ?>
  103. </select>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement