I'm trying to get the content of 3 different tables.
table A = Is containing our users list, table B = Is returning contracts related to users,table C = Is returning formula details related to the contracts.
In order to make it the right way, I'm using the following multi table request:
SELECT * FROM rscm_students A, rscm_files B, rscm_formulas C
WHERE B.dossier_status = 0
AND A.student_agency = :agency
AND B.file_student_id = A.id
AND B.file_formula_id = C.id
AND C.formula_place = 0
GROUP BY A.student_uniqid
ORDER BY B.file_date_create";
This is where the whole damn thing become a little complicated. It is returning the correct datas, but as the primary key of every table here is called "id". I can't do some foreach in php. If I got 3 contracts on 1 user, it impossible for me to regroup every contract in the same user array.
I'm still not an expert in SQL, that's why I'm using Phinx to control my database. This is also why my primary keys are named "id".
If you have a good idea, please let me know!
Alright, I will make an answer out of it.
First off, don't use
select *
The above select is fine for quick and dirty development prior to production. But it makes a mess out of things such as your joins with common column names coming out of multiple tables (like id and others).
Use modern explicit join syntax. Don't use the older join style. So use join and on.
Lastly with table aliases, create unique output column names for the id columns or other clashes such as
A.id as aid, B.id as bid