0

I have problem in my school management software.

I have to count how many classes a teacher has in all week, for this the admin may assign more than one teacher to a class and that selected teacher is inserted in database by their ids. If there are multiple teachers they will be inserted in single column separated by commas.

I have to count the total number of classes for a teacher.

How could I create such a query

mysql_num_rows(mysql_query("select * from table_teacher where employee_id in (1))

closed as unclear what you're asking by hot2use, Tom V, James Anderson, mustaccio, SqlWorldWide Aug 24 '17 at 13:21

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Could you add table definitions and sample data + expected output? – Tom V Aug 24 '17 at 7:21
  • Start with not inserting IDs "in single column separated by commas". – mustaccio Aug 24 '17 at 11:47
2

The root cause of your problem is that database is not normalized. Assuming your table looks like:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ...
, TEACHERS VARCHAR(100) NOT NULL );

INSERT INTO COURSES (APPLICATION_CODE, TEACHERS)
VALUES ('MA101', '1,2,5');

The correct thing to do is to normalize this into two tables:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE COURSE_TEACHERS
( APPLICATION_CODE ... NOT NULL 
, TEACHER_ID INT NOT NULL 
,     PRIMARY KEY (APPLICATION_CODE, TEACHER_ID) );

INSERT INTO COURSES (APPLICATION_CODE)
VALUES ('MA101');

INSERT INTO COURSE_TEACHERS (APPLICATION_CODE, TEACHER_ID)
VALUES ('MA101', 1), ('MA101', 2), ('MA101', 5);

In this case, it is sufficient to count the rows where TEACHER_ID = 1

SELECT COUNT(1) 
FROM COURSE_TEACHERS
WHERE TEACHER_ID = 1;

Not only will it be easier to ask questions like this, the queries will be much more efficient since they can utilize indexes in a way that is not possible in your current model.

That said, if you know that the teacher string always looks like for example:

' 1, 2, 5,'

You can use LIKE to count the rows:

SELECT COUNT(1) 
FROM COURSES WHERE TEACHERS LIKE '% 2,%';

Pitfalls, if the last item is not terminated with the separator token (in this case ",") you need to handle that in a special predicate. The same goes for the start item, it needs to start the same way as the remaining ones.

If you can not trust the string to be in this simple form, you may be able to come up with a regular expression that describes a teacher item in the list of teachers.

  • i can't understands what are you trying to says in pitfalls.... – yamini soni Aug 24 '17 at 10:34
  • If you're using a LIKE predicate with the separator token included you won't confuse "1," with "15,". – Lennart Aug 24 '17 at 12:54
1

If the fields are separated by comma you can use mysql String function "FIND_IN_SET" instead of like query. Refer link https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

your query should be like

SELECT COUNT(1) 
FROM COURSES WHERE FIND_IN_SET('2',TEACHERS);

Not the answer you're looking for? Browse other questions tagged or ask your own question.