Fetch data from Mysql into php

Question

I have the following tables in my database:

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username varchar(100) NOT NULL,
  rol varchar(100) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE subjects (
  subject_id int(11) NOT NULL AUTO_INCREMENT,
  subject text,
  PRIMARY KEY (subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;


CREATE TABLE users_subjects (
  users_subjects_id int(11) NOT NULL AUTO_INCREMENT,
  user_id_fk int(11),
  subject_id_fk int(11),
  FOREIGN KEY(user_id_fk) REFERENCES users(id),
  FOREIGN KEY(subject_id_fk) REFERENCES subjects(subject_id),
  PRIMARY KEY (users_subjects_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

And in my code, a registered user whose role is 'teacher' and has assigned the subjects 'X', can see in a table all the registered students with their respective subjects.

However, I am trying to perform a SELECT where the users whose role is 'student' are shown and have the same 'X' subjects assigned to them as the registered teacher.

I'm trying to do it this way but it’s not working:

$sql = "SELECT * FROM users where rol ='student' and id in (select distinct u.id from users u,users_subjects us where u.username='".$_SESSION['username']."' and us.user_id_fk=u.id and e.subjects=us.subject_id_fk);";
$result = $conn->query($sql);

Can someone help me see what’s wrong with the SELECT? Thank you

Answer 1

/Select a database and run these demo data for test/

DROP TABLE IF EXISTS subjects; CREATE TABLE IF NOT EXISTS subjects ( subject_id int(11) NOT NULL AUTO_INCREMENT, subject text, PRIMARY KEY (subject_id) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

INSERT INTO subjects (subject_id, subject) VALUES (1, 'Health'), (2, 'Mathematics'), (3, 'Music'), (4, 'Geography'), (5, 'Science'), (6, 'Social Studies'), (7, 'Algebra'), (8, 'English'), (9, 'Physics'), (10, 'Journalism'), (11, 'Geometry'), (12, 'Dance'), (13, 'Dramatics');

DROP TABLE IF EXISTS users; CREATE TABLE IF NOT EXISTS users ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(100) NOT NULL, rol varchar(100) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=latin1;

INSERT INTO users (id, username, rol) VALUES (1, 'Zacharys', 'teacher'), (2, 'Xanders', 'teacher'), (3, 'Brendans', 'teacher'), (4, 'Jermaines', 'teacher'), (5, 'Danes', 'teacher'), (6, 'Sonias', 'teacher'), (7, 'Maliks', 'teacher'), (8, 'Willas', 'teacher'), (9, 'Jeromes', 'teacher'), (10, 'Simons', 'teacher'), (11, 'Ferriss', 'teacher'), (12, 'Hoyts', 'teacher'), (13, 'Jacobs', 'teacher'), (14, 'Chaneys', 'teacher'), (15, 'Megans', 'teacher'), (16, 'Jamals', 'teacher'), (17, 'Basias', 'student'), (18, 'Briannas', 'student'), (19, 'Logans', 'student'), (20, 'Hardings', 'student'), (21, 'Hasads', 'student'), (22, 'Shelbys', 'student'), (23, 'Lucians', 'student'), (24, 'Alexanders', 'student'), (25, 'Ayannas', 'student'), (26, 'Yuris', 'student'), (27, 'Carissas', 'student'), (28, 'Tallulahs', 'student'), (29, 'Dantes', 'student'), (30, 'Alvins', 'student'), (31, 'Salvadors', 'student'), (32, 'Camerons', 'student'), (33, 'Lances', 'student'), (34, 'Cleos', 'student'), (35, 'Abigails', 'student'), (36, 'Colorados', 'student'), (37, 'Leilanis', 'student'), (38, 'Hops', 'student'), (39, 'Zephrs', 'student'), (40, 'Ivorys', 'student'), (41, 'Tylers', 'student'), (42, 'Jaspers', 'student'), (43, 'Jelanis', 'student'), (44, 'Clintons', 'student'), (45, 'Marnys', 'student'), (46, 'Jeromes', 'student'), (47, 'Madisons', 'student'), (48, 'Kaseems', 'student'), (49, 'Jennifers', 'student'), (50, 'McKenzies', 'student'), (51, 'Dantes', 'student'), (52, 'Chancellors', 'student'), (53, 'Adeles', 'student'), (54, 'Leroys', 'student'), (55, 'Salvadors', 'student'), (56, 'Marvins', 'student'), (57, 'Lucians', 'student'), (58, 'Ignatiuss', 'student'), (59, 'Chantales', 'student'), (60, 'Britanneys', 'student'), (61, 'Leandras', 'student'), (62, 'Odysseuss', 'student'), (63, 'Lens', 'student'), (64, 'Ivorys', 'student'), (65, 'Hollys', 'student'), (66, 'Drakes', 'student'), (67, 'Clarks', 'student'), (68, 'Dieters', 'student'), (69, 'Kirstens', 'student'), (70, 'Lesters', 'student'), (71, 'Beverlys', 'student'), (72, 'Tads', 'student'), (73, 'Destinys', 'student'), (74, 'Alisas', 'student'), (75, 'Salvadors', 'student'), (76, 'Hunters', 'student'), (77, 'Julians', 'student'), (78, 'Lucys', 'student'), (79, 'Francescas', 'student'), (80, 'Ayannas', 'student'), (81, 'Macons', 'student'), (82, 'Solomons', 'student'), (83, 'Jeanettes', 'student'), (84, 'Audras', 'student'), (85, 'Victors', 'student'), (86, 'Vernons', 'student'), (87, 'Marvins', 'student'), (88, 'Zenas', 'student'), (89, 'Xanders', 'student'), (90, 'Gareths', 'student'), (91, 'Henrys', 'student'), (92, 'Eagans', 'student'), (93, 'Alexiss', 'student'), (94, 'Jelanis', 'student'), (95, 'Brennas', 'student'), (96, 'Ivans', 'student');

DROP TABLE IF EXISTS users_subjects; CREATE TABLE IF NOT EXISTS users_subjects ( users_subjects_id int(11) NOT NULL AUTO_INCREMENT, user_id_fk int(11) DEFAULT NULL, subject_id_fk int(11) DEFAULT NULL, PRIMARY KEY (users_subjects_id), KEY user_id_fk (user_id_fk), KEY subject_id_fk (subject_id_fk) ) ENGINE=MyISAM AUTO_INCREMENT=201 DEFAULT CHARSET=latin1;

INSERT INTO users_subjects (users_subjects_id, user_id_fk, subject_id_fk) VALUES (1, 48, 12), (2, 19, 5), (3, 5, 8), (4, 69, 4), (5, 73, 12), (6, 85, 11), (7, 33, 8), (8, 20, 3), (9, 2, 7), (10, 30, 6), (11, 21, 9), (12, 50, 13), (13, 14, 12), (14, 79, 3), (15, 36, 9), (17, 72, 10), (18, 89, 7), (19, 94, 8), (20, 56, 6), (21, 16, 13), (22, 4, 6), (23, 29, 2), (24, 20, 5), (25, 76, 4), (26, 53, 8), (27, 48, 11), (28, 10, 13), (29, 13, 13), (30, 84, 4), (31, 12, 6), (32, 46, 4), (33, 20, 7), (34, 19, 3), (35, 62, 11), (36, 84, 10), (37, 62, 4), (38, 94, 7), (39, 29, 8), (40, 73, 6), (41, 75, 12), (42, 50, 3), (43, 47, 6), (44, 73, 2), (45, 52, 3), (46, 28, 6), (47, 81, 2), (48, 96, 5), (49, 23, 4), (50, 57, 10), (51, 2, 5), (52, 36, 4), (53, 29, 13), (54, 88, 4), (55, 43, 11), (56, 69, 1), (57, 16, 1), (58, 18, 13), (59, 20, 2), (60, 25, 13), (61, 75, 8), (62, 32, 3), (63, 34, 6), (64, 47, 9), (66, 15, 1), (67, 53, 10), (68, 39, 8), (69, 91, 5), (70, 82, 5), (71, 65, 11), (72, 86, 2), (73, 61, 1), (74, 34, 2), (75, 64, 8), (76, 65, 6), (77, 10, 8), (78, 78, 6), (79, 33, 11), (80, 69, 5), (81, 51, 1), (82, 56, 13), (83, 6, 7), (84, 77, 12), (85, 23, 5), (86, 53, 7), (87, 71, 8), (88, 3, 4), (90, 6, 13), (91, 54, 10), (92, 1, 7), (93, 13, 4), (94, 55, 9), (95, 88, 8), (96, 22, 12), (97, 19, 1), (98, 93, 7), (100, 95, 13), (101, 8, 4), (102, 13, 12), (103, 41, 2), (104, 6, 6), (105, 54, 7), (106, 5, 11), (107, 58, 11), (108, 1, 5), (109, 20, 10), (110, 66, 3), (111, 28, 9), (112, 21, 2), (113, 91, 2), (114, 74, 6), (115, 50, 7), (116, 70, 4), (117, 47, 8), (118, 48, 2), (119, 20, 4), (120, 32, 6), (122, 25, 2), (123, 42, 7), (124, 89, 6), (125, 94, 3), (126, 27, 2), (127, 90, 8), (128, 95, 3), (129, 86, 9), (130, 65, 1), (131, 14, 4), (132, 61, 6), (133, 33, 13), (134, 43, 4), (135, 17, 7), (136, 69, 10), (137, 29, 5), (138, 43, 12), (139, 90, 9), (141, 88, 3), (142, 45, 8), (143, 52, 8), (144, 56, 8), (145, 72, 2), (146, 15, 2), (147, 25, 9), (148, 91, 4), (149, 16, 12), (151, 62, 10), (152, 64, 10), (153, 17, 8), (154, 55, 4), (155, 19, 4), (157, 14, 2), (158, 7, 9), (159, 90, 1), (160, 45, 11), (161, 55, 12), (162, 35, 2), (163, 1, 8), (164, 41, 3), (166, 28, 5), (167, 43, 10), (168, 91, 7), (169, 77, 11), (170, 38, 13), (171, 3, 9), (172, 72, 5), (174, 80, 5), (175, 46, 7), (176, 87, 4), (177, 76, 11), (178, 1, 9), (179, 34, 5), (180, 46, 10), (181, 86, 13), (182, 53, 4), (183, 48, 8), (184, 5, 3), (185, 58, 3), (186, 6, 2), (188, 81, 9), (189, 75, 6), (190, 40, 6), (191, 52, 4), (192, 65, 7), (193, 52, 11), (194, 59, 3), (195, 10, 3), (196, 64, 6), (197, 33, 3), (198, 3, 1), (199, 96, 3), (200, 66, 8);

/* Here is My Query Where If Teacher id is '5' and we have to find out the list of students. Where rol has two values 'student' or 'teacher' As we need only students so have added !=

Hope it will help you...

SELECT U.username, U.id, USM.subject_id_fk, S.subject FROM users_subjects USM LEFT JOIN users U ON USM.user_id_fk = U.id LEFT JOIN subjects S ON USM.subject_id_fk = S.subject_id WHERE USM.subject_id_fk IN (SELECT US.subject_id_fk FROM users_subjects US WHERE US.user_id_fk = '5') AND U.rol != 'teacher'

*/

Answer 2

You use the alias e but is not loaded. Also you test an equality between subject and subject_id_fk

SELECT * 
FROM users
where rol = 'student'
and id in (
    select distinct u.id 
    from users u, users_subjects us, subjects e
    where u.username='".$_SESSION['username']."'
    and us.user_id_fk=u.id 
    and e.subject_id=us.subject_id_fk
);

But this query will give you the user line related to $_SESSION['username'] if his rol is student. I guess the good query you are looking for is the following one :

SELECT *
FROM users u
JOIN users_subjects us ON (us.user_id_fk = u.id)
JOIN subjects s ON (s.subject_id = us.subject_id_fk)
WHERE u.username = '".$_SESSION['username']."'
AND u.rol = 'student'