I-AM-OBODO Posted September 11, 2023 Share Posted September 11, 2023 Hello all, In a column in my table is store an array 58100, 47270, 95437, 52652 which represents in table1 class_id, class_name 58100 JSS 47270 PRY 95437 SSS in table2 subjects, subj_levels English 58100, 47270, 95437 Maths 58100, 47270 Physics 47270, 95437 I have two problems Problem One when i do a select with join, instead of getting JSS, PRY, SSS as result, i am getting only JSS and the other values not showing up. $stmt=$pdo->query(" SELECT t1.subj_name, t1.subj_levels, t2.class_id FROM tbl_school_subjects t1 LEFT JOIN tbl_classes t2 ON t1.subj_levels = t2.class_id "); WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){ echo '<tr> <td>'.$row['subj_name'].'</td> <td>'.$row['class_name_small'].'</td> <td>'; } Problem Two when i do a select find_in_set, i get no result. $ids = $_GET['id']; $stmt = $pdo->query(" SELECT * FROM tbl_school_subjects WHERE FIND_IN_SET($ids, subj_levels) > 0 "); what could be the problem? Thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted September 11, 2023 Share Posted September 11, 2023 Normalize your data and store it correctly... +-------------+ +-------------+ | subject | | class | +-------------+ +-------------+ +---------------+ +----| id | | id |---+ | class_subject | | | subj_name | | class_name | | +---------------+ | +-------------+ +-------------+ | | id | | +---<| class_id | | | subject_id |>--+ +---------------+ so that each class_id/subject_id pair is in it's own row... class_id subject_id | 58100 | 1 | 47270 | 1 | 95437 | 1 | 58100 | 2 | 47270 | 2 | 47270 | 3 | 95437 | 3 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted September 11, 2023 Solution Share Posted September 11, 2023 Sorry, I forgot to explain your probems There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270") For find_in_set() to work, the values should be comma-separated, not comma-space-separated. Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 11, 2023 Author Share Posted September 11, 2023 5 hours ago, Barand said: Sorry, I forgot to explain your probems There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270") For find_in_set() to work, the values should be comma-separated, not comma-space-separated. Thank you very much. Lemme work on the code again. Will feed you back. Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 13, 2023 Author Share Posted September 13, 2023 On 9/11/2023 at 2:09 PM, Barand said: Sorry, I forgot to explain your probems There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270") For find_in_set() to work, the values should be comma-separated, not comma-space-separated. Thanks once again. I was able to get it working as wanted but the problem i am having now is that it is omitting the general subject. I guess FIND_IN_SET only finds and displays what is in the set and disregards what is not found in the set. To make myself clearer, when all the classes offer a subject, it means the subject is termed "General". $stmt=$pdo->query(" SELECT *, GROUP_CONCAT(t2.class_name_small ORDER BY t2.class_id) classLevel FROM tbl_school_subjects t1 INNER JOIN tbl_classes t2 ON FIND_IN_SET(t2.class_id, t1.subj_levels) > 0 GROUP BY t1.subj_levels "); WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){ if($row['subj_levels'] == "General"){ $class_name = "General"; }else{ $class_name = $row['classLevel']; } echo '<tr> <td></td> <td>'.$row['subj_code'].'</td> <td>'.$row['subj_name'].'</td> <td>'.$row['subj_abbr'].'</td> <td>'.$row['subj_short_name'].'</td> <td>'.$class_name.'</td> All the general column is totally ignored. How can i make them show up in the list. thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted September 13, 2023 Share Posted September 13, 2023 You are grouping by a comma-separated string value. Where's the sense in that? I'll come back when you have a normalized database and I don't have to sieve through that dog's breakfast. Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 14, 2023 Author Share Posted September 14, 2023 (edited) 21 hours ago, Barand said: You are grouping by a comma-separated string value. Where's the sense in that? I'll come back when you have a normalized database and I don't have to sieve through that dog's breakfast. Does this means that storing comma separated arrays into a database is wrong practice? Cos for instance, I want to list all the classes that offers a particular subject in one column, I feel using an array would be appropriate. I'm just asking just to know better. Thanks Edited September 14, 2023 by I-AM-OBODO Quote Link to comment Share on other sites More sharing options...
Barand Posted September 14, 2023 Share Posted September 14, 2023 20 minutes ago, I-AM-OBODO said: Does this means that storing comma separated arrays into a database is wrong practice? Yes, positively evil. 21 minutes ago, I-AM-OBODO said: Cos for instance, I want to list all the classes that offers a particular subject in one column, I feel using an array would be appropriate. You can easily display them like that on output but don't store them like that. For example... mysql> select * from project; +----+---------------+-----------+------------+ | id | project_name | client_id | start_date | +----+---------------+-----------+------------+ | 1 | Project Alpha | 4 | 2022-12-01 | | 2 | Proect Beta | 2 | 2023-01-15 | | 3 | Project Gamma | 4 | 2023-03-01 | | 4 | Project Delta | 1 | 2023-03-20 | +----+---------------+-----------+------------+ mysql> select client_id -> , group_concat(project_name separator ', ') as projects -> from project -> group by client_id; +-----------+------------------------------+ | client_id | projects | +-----------+------------------------------+ | 1 | Project Delta | | 2 | Proect Beta | | 4 | Project Alpha, Project Gamma | +-----------+------------------------------+ 1 Quote Link to comment Share on other sites More sharing options...
Phi11W Posted September 15, 2023 Share Posted September 15, 2023 On 9/14/2023 at 10:17 AM, Barand said: Does this means that storing comma separated arrays into a database is wrong practice? Almost always - Yes. The only exception is when you only ever access or retrieve the whole value, commas and all, as a single unit via some other identifier (perhaps to supply that value to some application code that expects it in that "shape"). But ... as soon as you decide you want to pick that value apart, to extract part of the value or, worse, to find rows by part of that value, then you must reconsider its storage and "re-shape" it something that your database can better work with. Databases are generally: Really good at finding small pieces of "stuff" and bolting them together, but Really rubbish at taking big chunks of "stuff" and pulling them apart. Why do you think string manipulation functions are so "under-developed" in most DBMSes, compared to most programming languages? It's just not what they're built to do. Regards, Phill W. Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 16, 2023 Author Share Posted September 16, 2023 On 9/14/2023 at 10:17 AM, Barand said: Yes, positively evil. You can easily display them like that on output but don't store them like that. For example... mysql> select * from project; +----+---------------+-----------+------------+ | id | project_name | client_id | start_date | +----+---------------+-----------+------------+ | 1 | Project Alpha | 4 | 2022-12-01 | | 2 | Proect Beta | 2 | 2023-01-15 | | 3 | Project Gamma | 4 | 2023-03-01 | | 4 | Project Delta | 1 | 2023-03-20 | +----+---------------+-----------+------------+ mysql> select client_id -> , group_concat(project_name separator ', ') as projects -> from project -> group by client_id; +-----------+------------------------------+ | client_id | projects | +-----------+------------------------------+ | 1 | Project Delta | | 2 | Proect Beta | | 4 | Project Alpha, Project Gamma | +-----------+------------------------------+ Thank you so much. I've fixed the database as suggested and after doing so, I realised that there was no need for me using find_in_set again! Had to do a join with group_contact. All is working as expected now. Thank you very much! Quote Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 16, 2023 Author Share Posted September 16, 2023 23 hours ago, Phi11W said: Almost always - Yes. The only exception is when you only ever access or retrieve the whole value, commas and all, as a single unit via some other identifier (perhaps to supply that value to some application code that expects it in that "shape"). But ... as soon as you decide you want to pick that value apart, to extract part of the value or, worse, to find rows by part of that value, then you must reconsider its storage and "re-shape" it something that your database can better work with. Databases are generally: Really good at finding small pieces of "stuff" and bolting them together, but Really rubbish at taking big chunks of "stuff" and pulling them apart. Why do you think string manipulation functions are so "under-developed" in most DBMSes, compared to most programming languages? It's just not what they're built to do. Regards, Phill W. Thanks for the addition. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.