SQL JOIN returning 3 fields with the name 'id'
join, mysql, php, sql
Solution
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?
Problem
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.