How to create columns dynamically with SQL?

Asked

Viewed 905 times

0

I’m running a script that returns 250 values that will be the columns of a database. I need to create the database if it doesn’t exist or simply truncate it and recreate all the 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 me returns 250 values that will become columns of the database. I run, no error appears but also does not create the table with the columns that comes from the foreach.

php mysql
  • 1

    Variables are not interpreted in single quotes. This will create 250 tables imovel, would not be to execute a ALTER TABLE .... ADD COLUMN ...

  • I made the correction by concatenating the field. He creates the bank and puts the first column. And then to !!! I think the CREATE within the foreach doesn’t happen, but then how?

  • Run the first create, and then run ALTER TABLE in the items after this.

1 answer

1


Try to do 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, it would store all items and then insert all at once, however, in a matter of performance I do not know if it would be efficient, since it would be 250 item, but in a loop it would be almost the same thing.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.