Uncaught PDOException: SQLSTATE[23000]: erreur where close

Version imprimable

Bonjour,

je comprend pas d'où provient l'erreur

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
$stmt = $dbh->prepare("
        SELECT
            DATE(debut) AS date,
            slots.*,
            appointments.*,
            slots.id AS slot_id,
            appointments.id AS appointment_id,
            TIME_TO_SEC(TIMEDIFF(TIME(debut), :min)) DIV TIME_TO_SEC(TIMEDIFF(fin, debut)) AS row
        FROM slots
        LEFT JOIN appointments_slots ON slots.id = appointments_slots.slot_id
        LEFT JOIN appointments ON appointments_slots.appointment_id = appointments.id
        WHERE
            TRUE
            AND debut >= :from
            AND debut <= :to
			AND user_soc = :user_soc
        ORDER BY debut
   "
);
$stmt->execute(
    [
        'min' => $stats['min'],
        'to' => $to->format('Y-m-d H:i:s'),
        'from' => $from->format('Y-m-d H:i:s'),
		'user_soc' => $userid,
    ]
);
$slots = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'user_soc' in where clause is ambiguous

J'ai bien user_soc dans ma base de donnée

15/10/2024, 15h23
tatayo

Bonjour,
L'erreur indique que le nom de la colonne est ambiguë, donc cette colonne est présente dans 2 tables utilisées dans la requête.
Il faut (toujours) préfixer le nom des colonnes avec le nom/l'alias des tables.

Tatayo.
15/10/2024, 15h25
john67350

ok et je fais ca comment
merci

J'ai deux requete mais je dois ajouter un user

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
$stmt = $dbh->prepare("
        /*SELECT
            t.*,
			slots.user_soc,
            TIMEDIFF(max, min) AS diff,
            TIME_TO_SEC(TIMEDIFF(max, min)) DIV TIME_TO_SEC(TIMEDIFF('09:30:00', '09:00:00')) AS x		
        FROM (*/
            SELECT
                MIN(TIME(debut)) AS min,
                MAX(TIME(fin)) AS max
            FROM slots
            WHERE
                TRUE
                AND debut >= :from
                AND debut <= :to
 
        /*) t*/
    "
);
$stmt->execute(
    [
		'to' => $to->format('Y-m-d H:i:s'),
        'from' => $from->format('Y-m-d H:i:s'),
		//'user_soc' => $userid,
    ]
);
$stats = $stmt->fetch(PDO::FETCH_ASSOC);
 
$stmt = $dbh->prepare("
        SELECT
            DATE(debut) AS date,
            slots.*,
			slots.user_soc,
            appointments.*,
            slots.id AS slot_id,
            appointments.id AS appointment_id,
            TIME_TO_SEC(TIMEDIFF(TIME(debut), :min)) DIV TIME_TO_SEC(TIMEDIFF(fin, debut)) AS row
        FROM slots
        LEFT JOIN appointments_slots ON slots.id = appointments_slots.slot_id
        LEFT JOIN appointments ON appointments_slots.appointment_id = appointments.id
        WHERE
            TRUE
            AND debut >= :from
            AND debut <= :to
			AND user_soc = :user_soc
        ORDER BY debut
   "
);
$stmt->execute(
    [
        'min' => $stats['min'],
        'to' => $to->format('Y-m-d H:i:s'),
        'from' => $from->format('Y-m-d H:i:s'),
		'user_soc' => $userid,
    ]
);
$slots = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);

15/10/2024, 15h32
tatayo

En mettant le nom de la table devant le nom des colonnes, dans le SELECT, dans le WHERE, tout comme tu l'as fait dans les jointures...

Tatayo.
15/10/2024, 15h36
john67350

Ok merci j'ai compris
16/10/2024, 08h30
escartefigue
Citation:

Envoyé par tatayo

En mettant le nom de la table devant le nom des colonnes, dans le SELECT, dans le WHERE, tout comme tu l'as fait dans les jointures...

Tatayo.

Ou bien, et c'est plus pratique, en utilisant des alias de table ou de vue, moins pénibles à manipuler que les noms in extenso :
Code:

1 2 3 4 5 6 7 8 select T1.Colonne1 , T1.Colonne2 , T2.ColonneX , T2.ColonneY from ma_table as T1 left join autre_table as T2 on T2.clef_etrangere = T1.clef_primaire where ...