"foreach" inside query using PHP PDO " $sql = $Pdo->prepare( )"

Question

Good morning everyone, I’m trying to create a runtime view.

For this I need to list all the fields of the table and play within the statement the problem is that I can’t transfer the $obj1[0] array to $variable and so list all or concatenate a foreach within the query.

any idea? I thank you.

  if(@$_POST['op']=='criaview'){
    $bancoview = $_POST['bancotabelas'];
    $tabeaview = $_POST['tabelasbanco'];

    $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$bancoview,DB_USER,DB_PASSWORD);
    $sql1 = $pdo->prepare("SHOW FIELDS FROM ".$tabeaview);
    $sql1->execute();
    foreach($sql1 as $obj1){
        $variavel = $obj1[0];
    }

    $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$bancoview,DB_USER,DB_PASSWORD);
    $sql = $pdo->prepare(
    "CREATE VIEW  view_".$tabeaview."  AS
    SELECT
    *
    /**
    ".$variavel."<----------------aqui devem aparecer todos os campos
    **/
    FROM
        ".$tabeaview."
    ");
    $sql->execute();
  }

Answer 1

Solved! I redid all the code.

So that’s how I was able to create a view dynamically.

    $dir = PATH.$_SESSION['database'].'\\';  //Pega o diretorio atual
    $filename = PATH.$_SESSION['database'].$_POST['tabelasbanco'].".view.php";  //Raiz onde vai ser salvo o arquivo

    $bancoview = $_POST['bancotabelas'];
    $tabeaview = $_POST['tabelasbanco'];

    if(file_exists($filename)){
       unlink($filename);
    }
    $file = fopen($filename, "w+");
    $filedate = date("d.m.Y");

    $view = 'CREATE VIEW view_'.$tabeaview.' AS SELECT';

    $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$_SESSION['bancotabelas'],DB_USER,DB_PASSWORD);
    $sql = $pdo->prepare("show fields FROM ".$tabeaview." ");
    $sql->execute();
    foreach($sql as $obj){
        $data[] = $tabeaview.'.'.$obj[0];
    }

    $view.=' '.implode(",", $data).' ';

    $view.=' FROM '.$tabeaview.' ';

    $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$_SESSION['bancotabelas'],DB_USER,DB_PASSWORD);
    $sql3 = $pdo->prepare("select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '".$tabeaview."' AND TABLE_SCHEMA = '".$_SESSION['bancotabelas']."' ");
    $sql3->execute();
    foreach($sql3 as $obj3){
        $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$_SESSION['bancotabelas'],DB_USER,DB_PASSWORD);
        $sql4 = $pdo->prepare("select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '".$obj3['REFERENCED_TABLE_NAME']."' AND TABLE_SCHEMA = '".$_SESSION['bancotabelas']."' LIMIT 1 "); 
        $sql4->execute();
        foreach($sql4 as $obj4){
            $view.=' LEFT JOIN '.$obj3['REFERENCED_TABLE_NAME'].' ON '.$tabeaview.'.'.$obj4[0].' = '.$obj3['REFERENCED_TABLE_NAME'].'.'.$obj4[0].' ';
        }   
    } $pdo = null;

    fwrite($file, $view);

    $pdo = new PDO(DB_SERVER.":host=".DB_HOST.";dbname=".$bancoview,DB_USER,DB_PASSWORD);
    $sql = $pdo->prepare("$view");
    $sql->execute();
    $pdo = null;