multiple foreach help

[gmc1103]

Hi 

 

I'm having problem in my last function. I need to add into a relational table several records

$myArray = explode(',', $id_escola);
            foreach ($myArray as $id) {
                print_r($id);
                $stmt->bindParam(':id_escola', $id, PDO::PARAM_INT);
                $result = $stmt->execute();
                $idAtividade = $this->db->lastInsertId();
                $this->insertByLastId($idAtividade,$id_professor);
                $this->setDadosEstatisticosAtividades($idAtividade, $id_escola, $dominios);
            }

The return of this is ok.

id_prof  id_atividades  avaliacao  path    
-------  -------------  ---------  --------
    364              1          0  0       
    364              2          0  0       
    364              3          0  0  

Exemple... array of 3 returns in 3 records, the problem is this last function

$this->setDadosEstatisticosAtividades($idAtividade, $id_escola, $dominios);

This function is the following

try{
            $stmt = $this->db->prepare("INSERT INTO `estatistica_atividades` (`id_atividades`,`id_escola`,`id_dominios`) 
                                        VALUES(:id_atividades,:id_escola,:id_dominios) ;");
            $escolas = explode(',', $id_escola);
            $dominio = explode(',', $dominios);
            foreach ($escolas as $id) {
                foreach ($dominio as $ids) {
                    $stmt->bindparam(":id_atividades", $idAtividade);
                    $stmt->bindParam(':id_escola', $id, PDO::PARAM_INT);
                    $stmt->bindParam(':id_dominios', $ids, PDO::PARAM_INT);
                    $result = $stmt->execute();
                }
                if (!$result) {
                   print_r($stmt->errorInfo());
                }
            }
        }catch (PDOException $e) {
            echo $e->getMessage();
        }

Instead of having 3 records.....i have 9

id_atividades  id_escola  id_dominios  
-------------  ---------  -------------
            1          1              1
            1          2              1
            1          3              1
            2          1              1
            2          2              1
            2          3              1
            3          1              1
            3          2              1
            3          3              1

any explanation why?

[Jacques1]

Because your setDados...() function again splits the list of all escola IDs and makes an insert for every escola ID and every dominio ID. If you call the function three times (once of every escola ID), it performs 3 * 3 * [number of dominio IDs] insertions.

 

Appearently you want to pass just one escola ID to the function, namely $id from the outer foreach loop.

 

The function design in general is odd. If you want to pass multiple values, use an array, not those weird comma-separated lists.

Edited June 2, 2016 by Jacques1

[gmc1103]

Hi Jacques1

 

I have fixed that....i can't believe, it was really stupid...look at those two

 

I've posted this one

$myArray = explode(',', $id_escola);
            foreach ($myArray as $id) {
                $stmt->bindParam(':id_escola', $id, PDO::PARAM_INT);
                $result = $stmt->execute();
                $idAtividade = $this->db->lastInsertId();
                $this->insertByLastId($idAtividade,$id_professor);
                $this->setDadosEstatisticosAtividades($idAtividade, $id_escola, $dominios);
            }


Function called
$escolas = explode(',', $id_escola);
                    $dominio = explode(',', $dominios);
                    foreach ($escolas as $id) {
                            foreach ($dominio as $ids) {
                                    $stmt->bindparam(":id_atividades", $idAtividade);
                                    $stmt->bindParam(':id_escola', $id, PDO::PARAM_INT);
                                    $stmt->bindParam(':id_dominios', $ids, PDO::PARAM_INT);
                                    $result = $stmt->execute();
                            }
                            if (!$result) {
                                   print_r($stmt->errorInfo());
                            }
                    }

Now i have this one and it works

 foreach ($myArray as $id) {
                $stmt->bindParam(':id_escola', $id, PDO::PARAM_INT);
                $result = $stmt->execute();
                $idAtividade = $this->db->lastInsertId();
                $this->insertByLastId($idAtividade,$id_professor);
                foreach ($dominio as $id_dominio){
                        $this->setDadosEstatisticosAtividades($idAtividade, $id, $id_dominio);
                }
            }

As you can the variable $id_escola is in 2 different places...tha's why i had 9 instead of 3

 

Thanks