CODEWITHSUNDEEP
phpmysql
John
John

Reputation: 13

MySQL Query: generate all the people that are in the same classes at the same school as the current user

I'm attempting to grab results from 2 tables, one that has the following structure:

CREATE TABLE `users` 
(
    `id` bigint (20) NOT NULL AUTO_INCREMENT , 
    `firstname` varchar (255) NOT NULL, 
    `lastname` varchar (255) NOT NULL, 
    `sex` varchar (255) NOT NULL, 
    `orientation` varchar (50), 
    `email` varchar (255) NOT NULL, 
    `altemail` varchar (255), 
    `username` varchar (255) NOT NULL, 
    `password` varchar (255) NOT NULL, 
    `school` varchar (255) NOT NULL, 
    `age` date NOT NULL, 
    `image1` varchar (255), 
    `image2` varchar (255), 
    `image3` varchar (255), 
    `image4` varchar (255), 
    `image5` varchar (255), 
    `music` longtext, 
    `movies` longtext, 
    `tv` longtext, 
    `books` longtext, 
    `saysomething` longtext, 
    `course` varchar (255), 
    `YEAR` integer (11), 
    `verified` varchar (255), 
    `verifieddatetime` datetime, 
    `string` varchar (255), 
    `alerts` varchar (50),
    PRIMARY KEY (`id`)
)

And one that has the following structure:

CREATE TABLE `timetable` 
(
    `id` bigint (20) NOT NULL AUTO_INCREMENT , 
    `studentid` varchar (255), 
    `classno` varchar (50), 
    `classname` varchar (255), 
    `classnumber` varchar (255), 
    `prof` varchar (255),
    PRIMARY KEY (`id`)
)

The username is stored as a foreign key in the timetable table. Each student enters in their courses in the timetable table. Attempting to do a search results that would generate all the people that are in the same classes at the same school as the current user. Need to return the id, firstname, lastname,image1 fields from the users table and the classname, classnumber of the timetable table. I have the following query to grab results from the users at the same school:

$query = "select users.id, users.firstname, users.lastname, users.age, users.image1, users.school, category.category from users, category where users.image1 !='male.gif' and users.image1 !='female.gif' and users.verified ='yes' and users.username !='$_SESSION[myusername]' and users.school = '$school' and users.school = category.cat_id";

But I cannot figure out how to grab the classes as well.

Upvotes: 1

Views: 123

Answers (2)

glglgl
glglgl

Reputation: 91149

I do not get where the table timetable gets interesting in this query - it is not included.

But nevertheless, I have some design questions/hints:

  1. timetable.studentid would better be a bigint (20) NOT NULL.
  2. You should have a separate field for the gender of the students. I suppose there is a picture which shows the gender - male.gif and female.gif? This should, then, be derived from the gender field, not other way round.
  3. Depending where $_SESSION[myusername] comes from, you are vulnerable to SQL injection. Do not forget to use mysql_real_escape().

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

SELECT
  users.id,
  users.firstname,
  users.lastname,
  users.age, 
  users.image1,
  users.school,
  category.category,
  timetable.classname,
  timetable.classnumber
FROM
  users
INNER JOIN
  category
ON
  users.school = category.cat_id
INNER JOIN
  timetable
ON
  timetable.studentid = users.id
INNER JOIN
  (
  SELECT
    classno
  FROM
    timetable
  WHERE
    studentid = $id_of_the_user
  ) classes_of_the_one_user
ON
  classes_of_the_one_user.classno = timetable.classno
WHERE
  users.image1 != 'male.gif'
AND
  users.image1 != 'female.gif'
AND
  users.verified = 'yes'
AND
  users.school = '$school'

Upvotes: 0

Related Questions