为什么我的左连接不能在我的php mysql数据库中工作？

Question

I'm trying to create an sql query that will show me the prices of the services selected by one user within a reservation.

So I want to select the id_service column from my table chosen_services where the id_reservation coresponds to the $_SESSION['idReservation'] and get the price of these services and finaly calculate the total.

here are my 2 tables in mysql : https://ibb.co/NtgLLh7 and https://ibb.co/jkB0Qm6

Here's my query for the moment :

$getPriceOfAllServices = $bdd->prepare(
    'SELECT id_service 
    FROM CHOSEN_SERVICES 
    LEFT JOIN SERVICES ON CHOSEN_SERVICES.id_chosen_service = SERVICES.id_service 
    WHERE id_reservation = ?'
);
$getPriceOfAllServices->execute(array($_SESSION['idReservation']));
$getPriceOfAllServices = $getPriceOfAllServices->fetch();

And I have this error : https://ibb.co/Wc1hLvz

Thanks in advance!

Answer 1

SELECT id_service

Both tables involved in the query have a column called id_service. Since you did not prefix the column name, MySQL cannot reliably tell which of the two you mean, hence it raises the 'ambiguous column name` error.

Since you are using LEFT JOIN, it is very likely that you want to return the column coming from table CHOSEN_SERVICES (since the other column might be NULL, when the join condition does not match).

Consider:

SELECT CHOSEN_SERVICES.id_service 
FROM CHOSEN_SERVICES 
LEFT JOIN SERVICES ON CHOSEN_SERVICES.id_chosen_service = SERVICES.id_service 
WHERE id_reservation = ?

---

NB: please note that column id_reservation in the WHERE clause is not prefixedeither. While this will not generate the same error (since that column only exists in one of the two tables), it is a good practice to always prefix columns when several tables are involved. Another good habit is to use table aliases.

Accordingly here is a new version of your query:

SELECT cs.id_service 
FROM chosen_services cs
LEFT JOIN services s ON cs.id_chosen_service = s.id_service 
WHERE cs.id_reservation = ?