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.
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.