SQL JOIN返回3个名为'id'的字段

Question

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.

Answer 1

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.

• How to prevent SQL injection in PHP?