How to create columns dynamically with SQL?

Question

How to create columns dynamically with SQL?

Navigation

#1 by (1 votes)

0

I'm running a script that returns me 250 values that will be the columns of a database. I need to create the database if it does not exist or simply truncate it and recreate all tables to update columns that will be added in the future. I tried to use this code but without success.

try {
    $pdo = new PDO('mysql:host=host;dbname=banco', $userdB, $passdB);
    foreach($res as $item){ 
        $sql = 'CREATE TABLE imovel ( '.$item["field"].' VARCHAR(300) );';
        $pdo->exec($sql);
    }
    $pdo = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

Just to ensure understanding, this foreach returns me 250 values that will become columns of the database. Rodo, no error appears but also does not create the table with the columns that come from foreach .

php mysql

asked by anonymous 26.12.2014 / 15:45

1 answer

Change background color of the title of a Dialog jquery upload file pass variable php to javascript

score 1 · Accepted Answer

Try the following:

   $pdo = new PDO('mysql:host=host;dbname=banco', $userdB, $passdB);

    foreach($res as $item){ 
        $sql .= $item['field']." VARCHAR(300),"; // O valor de $SQL sera "item VARCHAR(300),"
    }

    $sqlAux = trim($sql, ","); // Remove ultima virgula
    $pdo->exec("CREATE TABLE imovel (".$sqlAux.");"); // O valor sera "CREATE TABLE imovel (item VARCHAR(300),item2 VARCHAR(300),item3 VARCHAR(300),item4 VARCHAR(300));"

Maybe it's not ideal, but it works.

In idea, I would store all items and then insert them all at once, however, in terms of performance I do not know if it would be efficient, since they would be 250 items, but in a loop it would be almost the same thing. p>