MVC criar database

[li0n.coder 3]

Estou usando um sistema MVC para criar uma aplicação, e uma das funções do sistema é criar um novo banco de dados. Porém, não sei como faço para escolher a collation e nem cria nenhuma database nova.

 

Controller:

<?php
 
/**
 * Class Home
 *
 * Please note:
 * Don't use the same name for class and method, as this might trigger an (unintended) __construct of the class.
 * This is really weird behaviour, but documented here: http://php.net/manual/en/language.oop5.decon.php
 *
 */
class Home extends Controller
{
    /**
     * PAGE: index
     * This method handles what happens when you move to http://yourproject/home/index (which is the default page btw)
     */
    public function index()
    {
        // load views
        require APP . 'view/_templates/header.php';
        require APP . 'view/home/index.php';
        require APP . 'view/_templates/footer.php';
    }
    
    public function addDatabase()
    {
        // if we have POST data to create a new song entry
        if (isset($_POST["submit_add_database"])) {
            // do addSong() in model/model.php
            $this->model->addDatabase($_POST["name"], $_POST["collation"]);
        }
 
        // where to go after song has been added
        header('location: ' . URL . 'home/index');
    }
}

model:

...
        public function addDatabase($name, $collation)
    {
        $sql = "CREATE DATABASE :name;";
        $query = $this->db->prepare($sql);
        $parameters = array(':name' => $name, ':collation' => $collation);

        // useful for debugging: you can see the SQL behind above construction by using:
        // echo '[ PDO DEBUG ]: ' . Helper::debugPDO($sql, $parameters);  exit();

        $query->execute($parameters);
    }

view

...
                <form action="<?php echo URL; ?>home/adddatabase" method="POST">
                <div class="row">
                    <div class="input-field col s6">
                        <input id="first_name" name="name" type="text" class="validate">
                        <label for="first_name">Database Name</label>
                    </div>
                    <div class="input-field col s6">
                        <select class="browser-default" name="collation">
                            <option value="0">Choose your option</option>
                            <option value="1">Option 1</option>
                            <option value="2">Option 2</option>
                            <option value="3">Option 3</option>
                        </select>
                    </div>
                </div>
                
                <div class="right database-submit">
                    <button class="btn waves-effect waves-light red lighten-3" type="submit" name="submit_add_database" value="Submit">
                        Create Now<i class="mdi-content-send right"></i>
                    </button>
                </div>
                </form>
...

[Williams Duarte 431]

Segue um exemplo e adapte ao code!

<?php
$host="localhost"; 

$root="root"; 
$root_password="123456"; 

$user='newuser';
$pass='newpass';
$db="newdb"; 
$collation = 'utf8_general_ci';

try {
	$dbh = new PDO("mysql:host=$host", $root, $root_password);

	$dbh->exec("CREATE DATABASE `$db` COLLATE `$collation`;
			CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
			GRANT ALL ON `$db`.* TO '$user'@'localhost';
			FLUSH PRIVILEGES;") 
	or die(print_r($dbh->errorInfo(), true));

} catch (PDOException $e) {
	die("DB ERROR: ". $e->getMessage());
}

[li0n.coder 3]

Não consegui finalizar. Model:

 

<?phpclass Model{    function __construct($db)    {        try {            $this->db = $db;        } catch (PDOException $e) {            exit('Database connection could not be established.');        }    }           public function addDatabase($name, $collation){           try {               $db->exec("CREATE DATABASE `$name`               CREATE USER 'DB_USER'@'DB_HOST' IDENTIFIED BY 'DB_PASS';               GRANT ALL ON `$name`.* TO 'DB_PASS'@'DB_HOST';               FLUSH PRIVILEGES;")            or die(print_r($db->errorInfo(), true));} catch (PDOException $e) {               die("DB ERROR: ". $e->getMessage());           }                             }        }

controller

<?php/** * Class Home * * Please note: * Don't use the same name for class and method, as this might trigger an (unintended) __construct of the class. * This is really weird behaviour, but documented here: http://php.net/manual/en/language.oop5.decon.php * */class Home extends Controller{    /**     * PAGE: index     * This method handles what happens when you move to http://yourproject/home/index (which is the default page btw)     */    public function index()    {        // load views        require APP . 'view/_templates/header.php';        require APP . 'view/home/index.php';        require APP . 'view/_templates/footer.php';        echo DB_USER;    }        public function addDatabase()    {        // if we have POST data to create a new song entry        if (isset($_POST["submit_add_database"])) {            // do addSong() in model/model.php            $this->model->addDatabase($_POST["name"], $_POST["collation"]);        }        // where to go after song has been added        header('location: ' . URL . 'home/index');    }}

Caso queira ver todo code: https://github.com/misakie/Yuno-Gasai

 

up

[Williams Duarte 431]

Quais erros as exceptions estão lançando?

Outra coisa, essas constantes em aspas simples sem tratamentos?

 

Coloque as constantes entre aspas duplas e pontos

'". DB_USER . "'

ou use a função sprintf ou seta estas constantes em variáveis $this

[li0n.coder 3]

Fiz algumas alterações por mim mesmo e funcionou!. Code para quem quiser:

<?php

class Model
{

    function __construct($db)
    {
        try {
            $this->db = $db;
        } catch (PDOException $e) {
            exit('Database connection could not be established.');
        }
    }
    
    /**
     * Add a database to mysql
     * @param string $name Name
     * @param string $collation Collation
     */
    public function addDatabase($name, $collation)
    {
        try {
            $sql = "CREATE DATABASE `$name` COLLATE `$collation`;
            CREATE USER '". DB_USER . "'@'". DB_HOST . "' IDENTIFIED BY '". DB_PASS . "';
            GRANT ALL ON `$name`.* TO '". DB_PASS . "'@'". DB_HOST . "';
            FLUSH PRIVILEGES;";
            $query = $this->db->prepare($sql);
            $query->execute();
        } catch (PDOException $e) {
            die("DB ERROR: ". $e->getMessage());
        }
    }
}