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

may be you should look into joining tables . Looks like issue is here select distinct u.id from users u,users_subjects us , these are 2 tables .. is it? — zod
– zod, Commented May 16, 2019 at 18:10
@zod yes they are, users is a table and users_subjects is another table. — ana
– ana, Commented May 16, 2019 at 18:22

zod · Accepted Answer · 2019-05-16 18:33:41Z

1

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'

edited May 16, 2019 at 18:33

zod

12.5k25 gold badges74 silver badges107 bronze badges

answered May 16, 2019 at 18:12

Astrea

1469 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ana Over a year ago

Thank you for your code Astrea, but is not showing the users in the table, not even the students with any subject

zod Over a year ago

whats that 2 from users and subjects ??

Astrea Over a year ago

oups, is a mistake :)

ana Over a year ago

Sorry Astrea this is still not working :S It doesn't show any students in the table

zod Over a year ago

@ana did sql execute with out error? did yu execute in sql editor and see by passing username

|

Brain Developer · Accepted Answer · 2019-05-17 12:58:16Z

/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'

*/

Thank you for your answer @Brain! At the end this the query that works: $sql = "SELECT * FROM users uProf JOIN users_subjects usProf ON (usProf.user_id_fk = uProf.id) JOIN users_subjects usAlu ON (usProf.subject_id_fk = usAlu.subject_id_fk) JOIN users uAlu ON (usAlu.user_id_fk = uAlu.id) WHERE uProf.username = '".$_SESSION['username']."' AND uAlu.rol = 'student';";

Collectives™ on Stack Overflow

Fetch data from Mysql into php

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related