Foreach error

[gmc1103]

Hi

 

I'm having a problem regarding the following code and i don't get whats is wrong with that

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
header('Content-type: application/json');
require_once("./dbconfig.php");
$esmaior = new ESMAIOR($DB_con);
$id_visita = 28;
try {
    $stmt = $DB_con->prepare("SELECT dpt FROM visitas_estudo where id_visita = :id_visita");
    $stmt->bindParam(':id_visita', $id_visita, PDO::PARAM_INT);
    $stmt->execute();
    $dpts = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if($stmt->rowCount() > 0) {
        $stmt = $DB_con->prepare("SELECT departamento FROM departamentos where id_departamento = :id_departamento");
        $departamento = implode(',', $dpts);
        $nome_dpt = array();
        echo $departamento;
        foreach ($departamento as $id) {
            $stmt->bindParam(':id_departamento', $id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
            foreach ($result as $departamentos) {
                $nome_dpt[] = $departamentos;
            }
        }
        $nomes = implode(';', $nome_dpt);
        echo $nomes;
    }
} catch (PDOException $e) {
    echo $e->getMessage();
}

The problem is the foreach loop

4,6,2,3<br />
<b>Warning</b>:  Invalid argument supplied for foreach() in <b>/home/esmaior/public_html/miga/db/testes.php</b> on line <b>18</b><br />

I have the echo (gives me 4,6,2,3) but when it enter the loop it say invalid argument...

 

Not sure what is wrong

[Barand]

Foreach () takes an array and loops through it.

 

You have the array, $dpts, from which you created the comma-delimited string $departmento.

[gmc1103]

Hi Barand

How are you?

Thanks for the answer but i still hava a problem....it only gives me one, when it shoud be 4

The code

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
header('Content-type: application/json');
require_once("./dbconfig.php");
$esmaior = new ESMAIOR($DB_con);
$id_visita = 28;
try {
    $stmt = $DB_con->prepare("SELECT dpt FROM visitas_estudo where id_visita = :id_visita");
    $stmt->bindParam(':id_visita', $id_visita, PDO::PARAM_INT);
    $stmt->execute();
    $dpts = $stmt->fetchAll(PDO::FETCH_COLUMN);
    var_dump($dpts);
    if($stmt->rowCount() > 0) {
        $stmt = $DB_con->prepare("SELECT departamento FROM departamentos where id_departamento = :id_departamento");
        foreach ($dpts as $id) {
            $stmt->bindParam(':id_departamento', $id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
            $nome_dpt = array();
            foreach ($result as $departamentos) {
                $nome_dpt[] = $departamentos;
            }
        }
    }
    var_dump($nome_dpt);
    $nomes = implode(';', $nome_dpt);
    echo $nomes;
} catch (PDOException $e) {
    echo $e->getMessage();
}

and the var_dump

It looks the loop it only executed one time only

array(1) {
  [0]=>
  string(7) "4,6,2,3"
}
array(1) {
  [0]=>
  string(6) "Artes "
}
Artes 

right?

[Jacques1]

The code in general doesn't make much sense.

 

First off, you need to learn some database basics like joins. This is how you connect tables in the relational model; you do not execute queries in loops in your application.

 

The queries themselves are also strange. You first fetch a bunch of department IDs. Then you look up the department for each ID. But you seem to expect multiple departments per ID, because you're again using fetchAll(). How is that possible? Isn't the ID supposed to be, you know, unique?

 

It would also help a lot if you used meaningful variable names in English instead of this English/Spanish(?) mixture.

[gmc1103]

Hi Jacques

No..let me explain

What is stored into the database is some id's, that's the way they want.

What i need is get all the id's and then return the values of those id's

for instance

id 

1 ......Math

2.......Portuguese

3.......English

and so on.

So, when i want to get all the subjects, i need to get them from the id stored in the database.

About the code,

• first i want to collect the id's stored in the dpt field;
• second, from the array, i execute the loop to get the values of each id and store them in an array;
• then i have all subjects in a string

Maybe isn't the best approach but i'm still learning 

 

Regards

[Jacques1]

I understand what you're trying to do. I'm saying that your code doesn't do what you want it to do. You don't understand your own code.

 

Your code is written as if multiple subjects could have the same ID (e. g. “Math”, “Portugese” and “English” would all have the ID 1). This is obviously not the case.

 

I assume what you actually mean is something like this:

<?php

$departments_stmt = $DB_con->prepare('
    SELECT
        deparatmentos.departamento
    FROM
        visitas_estudo
        JOIN deparatmentos ON visitas_estudo.dpt = departamentos.id_departamento
    WHERE
        visitas_estudo.id_visita = :id_visita
');
$departments_stmt->execute([
    'id_visita' => 28,
]);
$departments = $departments_stmt->fetchAll(PDO::FETCH_COLUMN);

// $departments is an array of all department names; you can now implode() it or whatever

Edited January 26, 2017 by Jacques1

[ginerjm]

A mock query would look like:

 

$q = 'select d.department, a.id, a.class, a.time, a.xxx, a.yyy....

from classes a, departments d

where a.id=d.id

order by department';

 

This would give you all the records in table a (I call it 'classes') and the department name for each class from the departments table (d).

[gmc1103]

Ok

Let me show what i have

Using this query

 SELECT dpt FROM visitas_estudo WHERE id_visita = 28

I have 

dpt      
---------
4,6,2,3  

this is 4 (id's) regarding the departamentos table

So i need to get those values (ids) and using a foreach loop get all the subjects of that. That's why i have 2 query, first one to get all the id's and then with those id's making another query to get the subject, attach all the an array and then send it to pdf file

the return should be something like

Artes, Ciências Sociais e Humanas,Linguas Estrangeiras,Matemática e Informática,Português

[Jacques1]

Oy vey. Like I said, you really, really need to learn the basics before you start typing code on your keyboard.

 

You do not store comma-separated lists in a database. I understand that you absolutely love comma-separated lists, but this is not how the relational model works. One field is for one value. Not four values. One.

 

So the first step is to repair your entire database. When that's done, we can go back to my query in #6.