Requête préparée et DELETE

[nicodu09]

Bonjour,

Je suis en train de développer une appli en php (CLI) et je suis face à un problème que nous n'arrivons pas à résoudre.

J'exécute une requête préparée en boucle avec des paramètres variables, or cette requête ne semble pas affecter la BDD, pourtant en rentrant directement la même requête sur ma base, elle fonctionne ...

Voici le code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<?php
    $core = Core::getInstance();
    $sql = "DELETE FROM kpistar.fact_platform WHERE id_dim_platform = :platform AND id_dim_time = :time AND id_di\
m_business_unit = :bu AND id_dim_location = :country AND id_dim_placement = :ads_type";
    $stmt = $core->dbh->prepare($sql);
    foreach ($t as $metric)
      {
        $stmt->bindParam(':platform', $metric->platform, PDO::PARAM_INT);
        $stmt->bindParam(':time', $metric->ts, PDO::PARAM_INT);
        $stmt->bindParam(':bu', $metric->bu, PDO::PARAM_INT);
        $stmt->bindParam(':ads_type', $metric->ads_type, PDO::PARAM_INT);
        $stmt->bindParam(':country', $metric_country, PDO::PARAM_INT);
        echo ("DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = " . $metric->platform . " AND id_dim_\
time = " . $metric->ts . " AND id_dim_business_unit = " . $metric->bu . " AND id_dim_location = " . $metric->coun\
try . " AND id_dim_placement = " . $metric->ads_type . ")" . PHP_EOL);                                            
        $stmt->execute();
        echo ("Lignes affectee : " . $stmt->rowCount() . PHP_EOL);                                              
      }
?>

Voici ce qui s'affiche :

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
 
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 1 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 5 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 4 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 6 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 3 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 2 AND id_dim_location = 2 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 2)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 3)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 4)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 5)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 6)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 2 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 3 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 4 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 5 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 6 AND id_dim_placement = 9)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 10)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 10)
Lignes affectee : 0
DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = 1 AND id_dim_time = 20110906 AND id_dim_business_unit = 1 AND id_dim_location = 1 AND id_dim_placement = 10)
Lignes affectee : 0

Je précise que la BDD est de type PostGreSQL.
Donc pour reprendre mon problème (et au risque de me répéter) :

Le nombre de lignes affectées est toujours égal à 0 alors qu'il ne devrait pas.

Si j'exécute manuellement la requête que j'affiche avec le "echo" sur la base (avec pgAdmin par exemple), elle fonctionne.

Un peu d'aide ?

Merci d'avance !

[Dominique49]

hum, je ne vois pas bien pourquoi cela fait ça ... néanmoins, je précise que bindParam lie à la référence d'une variable, tandis que bindValue "clone" le contenu de la variable. autrement dit, si tu utilise bindParam, pas besoin de les refaire à chaque fois ... et la valeur enregistrée sera la valeur actuelle de la variable.
mais dans ton cas, comme la variable $metric initialisée avant, tu devrais plutôt utiliser bindValue :

Donc on pourrait faire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?php
    $core = Core::getInstance();
    $sql = "DELETE FROM kpistar.fact_platform WHERE id_dim_platform = :platform AND id_dim_time = :time AND id_di\
m_business_unit = :bu AND id_dim_location = :country AND id_dim_placement = :ads_type";
    $stmt = $core->dbh->prepare($sql);
    foreach ($t as $metric)
      {
        $stmt->bindValue(':platform', $metric->platform, PDO::PARAM_INT);
        $stmt->bindValue(':time', $metric->ts, PDO::PARAM_INT);
        $stmt->bindValue(':bu', $metric->bu, PDO::PARAM_INT);
        $stmt->bindValue(':ads_type', $metric->ads_type, PDO::PARAM_INT);
        $stmt->bindValue(':country', $metric_country, PDO::PARAM_INT);
        echo ("DELETE FROM kpistar.fact_platform WHERE (id_dim_platform = " . $metric->platform . " AND id_dim_\
time = " . $metric->ts . " AND id_dim_business_unit = " . $metric->bu . " AND id_dim_location = " . $metric->coun\
try . " AND id_dim_placement = " . $metric->ads_type . ")" . PHP_EOL);                                            
        $stmt->execute();
        echo ("Lignes affectee : " . $stmt->rowCount() . PHP_EOL);                                              
      }
?>

tu pourrais aussi je crois initialisé $metric avant (puis lié avec bindParam), mais je ne peu pas aller plus loin ne connaissant pas la structure de l'objet metric.
après, je ne crois pas que ça va résoudre ton pb ...

[Dominique49]

hep, j'ai trouvé !

Code :

1
2
 
  $stmt->bindParam(':country', $metric_country, PDO::PARAM_INT);

$metric_country

tu as mis _ au lieu de ->

on est tous pareil, on cherche une erreur vicieuse là où il n'y a rien !
C'est l'avantage des EDI qui eux ne se laisse pas attraper pas ce genre d'erreurs sournoises
Je dis ça mais je n'ai jamais utilisé d'EDI en PHP.
amicalement.