Searching using mySql together with PHP

Question

I'm busy developing an application where users can search within multiple table, now I'm not sure if I'm doing it the correct way.

My sample code looks as follows

        SELECT
   s.name,
   s.surname,
   s.id_nr,
   s.student_nr,
   s.createdate,
   s.enddate
  FROM
   Student AS s,
   Student_Results AS sr
  WHERE
   sr.innovation = "A"
  AND
   s.name = "Test"

Is it the correct way as I do above or should I rather use left joins etc?

Answer 1

At the very least, you need something linking the two tables together - right now you'll basically fetch every student for every 'A' grade, because nowhere in your WHERE clause do you specify that the grade and the student have to match each other.

Instead, you'd need something like this (no idea what your related ID fields would be, but you get the idea...):

SELECT
    s.name,
    s.surname,
    s.id_nr,
    s.student_nr,
    s.createdate,
    s.enddate
FROM
    Student AS s,
    Student_Results AS sr
WHERE
    sr.innovation = "A"
AND
    s.name = "Test"
AND
    s.id_nr = sr.student_nr