-1

Table Students

--------------------------------------------------------------
- student_id - student_name - student_image - student_course -
--------------------------------------------------------------
-          1 - Ana          - avatar.jpg    -           10,9 -
-          2 - David        - avatar.jpg    -         11,9,8 -
-          3 - Jasmine      - avatar.jpg    -          8,9,7 -
--------------------------------------------------------------

Table Courses

-------------------------------------------
- course_id - course_title - course_image -
-------------------------------------------
-         7 - Photoshop    - image.jpg    -
-         8 - Cinema 4D    - image.jpg    -
-         9 - Idesign      - image.jpg    -
-        10 - Illustrator  - image.jpg    -
-------------------------------------------

i want to separate id value(from table students/student_course) in multi rows without duplicates like this: 

------------------------------------------------
- student_course - course_title - course_image -
------------------------------------------------
-              7 - Photoshop    - image.jpg    -
-              8 - Cinema 4D    - image.jpg    -
-              9 - Idesign      - image.jpg    -
-             10 - Illustrator  - image.jpg    -
------------------------------------------------

Sql:

function selected_courses($connect){
$sentence = $connect->prepare('');
$sentence->execute(array());
return $sentence->fetchAll();
}
bdroid
  • 606
  • 2
  • 12
  • 27
  • Restructure your database. `11,9,8` should have each own row in another table. – Xorifelse Mar 14 '17 at 22:05
  • `student_course` should really be it's own table that has the student_id and course_id with one row for each of the students' courses. Combining the courses like this is considered bad practice and really means that any query will relatively perform poorly as you can't use indexes to join these tables and it will require a scan of every course row compared to student to get the course data for a student or to find all students with a specific course. I would suggest refactoring this before it gets too late. – Jonathan Kuhn Mar 14 '17 at 22:06
  • @JonathanKuhn How? Do you have any examples? I'm a beginner at this – bdroid Mar 14 '17 at 22:07
  • I just said how. Create a new table called `student_course` with the columns `student_id` and `course_id`. Then for each students course, insert a row with the student id and course id. Then you can join these tables together using a standard join with `student_course` as the pivot/join table. – Jonathan Kuhn Mar 14 '17 at 22:14
  • Check this out: http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?noredirect=1&lq=1 – Don't Panic Mar 14 '17 at 22:17
  • Also, just an FYI to give you something to google about, this sort of thing is called `database normalization` which is the process used to create a proper structure for database tables and columns. It leads into something called `normal form` which are a set of rules you should follow when creating database tables to give the "optimum" structure. Anything more than this would quickly take the question off topic as there are entire books/classes dedicated to this stuff. You should be able to find a basic tutorial though that covers it and why you should do it. – Jonathan Kuhn Mar 14 '17 at 22:22

1 Answers1

0

As others have suggested in the comments it is a bad idea to contain multiple identifier references to a different table in a single column.

You want to have a third table which reduces the complexity to achieve what you want and also gives you more flexibility.

Example http://sqlfiddle.com/#!9/3ac000/1

Mike Geise
  • 126
  • 1