3

I have a SQL query using joins with 3 tables. It actually works fine, but all three tables have a field called 'id', so later when I try to access the id field, it only accesses the one from the last field. The SQL query is as follows:

"SELECT * FROM professors AS p 
            JOIN schools_professors AS sp 
            ON p.school_id = sp.id
            JOIN schools AS s ON sp.school_id = s.id  WHERE p.first_name LIKE '%".
                $search."%' OR p.last_name LIKE '%".$search.
                "%' LIMIT 0, 10;"

now then, the schools, professors, and schools_professors, all have the id column. How do I access the id of professors though? That's the only one I care about.

John Woo
  • 258,903
  • 69
  • 498
  • 492
samuraiseoul
  • 2,888
  • 9
  • 45
  • 65

1 Answers1

5

You should not be using * in this case but instead manually project the column names and add an ALIAS on it. Example,

SELECT   ....,
         p.ID AS ProfessorID,
         s.ID AS SchoolID,
FROM     .....

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • It's beign wrapped in PDO and such to prevent injection, the reason I wanted the * is cause I essentially want all but the id's of the second and third tables, that about 50 colums. It would be a pain to have to type all of them to get rid of two columns. – samuraiseoul Apr 14 '13 at 05:52
  • 1
    if you still want to use `*`, then add extra column for the `Professor and SchoolID` example: `SELECT *, p.ID AS ProfessorID, s.ID AS SchoolID FROM...` – John Woo Apr 14 '13 at 05:54