Not getting the needed values

[I-AM-OBODO]

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

[Barand]

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  

 

[Barand]

Sorry, I forgot to explain your probems

1. There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270")
2. For find_in_set() to work, the values should be comma-separated, not comma-space-separated.

[I-AM-OBODO]

5 hours ago, Barand said:

Sorry, I forgot to explain your probems

1. There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270")
2. 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.

[I-AM-OBODO]

On 9/11/2023 at 2:09 PM, Barand said:

Sorry, I forgot to explain your probems

1. There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270")
2. 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

[Barand]

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.

[I-AM-OBODO]

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

[Barand]

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 |
+-----------+------------------------------+

 

[Phi11W]

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. 

[I-AM-OBODO]

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!

[I-AM-OBODO]

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.