Select with PDO and SQL barring HTML

2

I'm trying to make a Select with PDO and MYSQL, but when I put the php code doing this query in the middle of my html it does not allow the execution of the rest of the page.

follow the code snippet:

<select class="emselect">
    <option value="select">selecione</option>
    <?php
    $buscarid=$pdo->prepare("SELECT ID,usuario FROM usuarios ORDER BY ID ASC");
    $buscarid->execute();
    while($linha=$buscarid->fetch(PDO::FETCH_ASSOC)){
        echo "<option value=".$linha["ID"].$linha["usuario"].">".$linha["ID"]." ".$linha["usuario"]."</option>";
    }
    ?>
    </option>
</select>

After trying to do some testing I realized that the problem was in php, but it is not connected to any kind of "open" closure, something that has given me a problem before, so I believe that the connection in the middle of the html code is blocking , then what would be the best way to do it? I need while to continue in the same place to list select items.

    
asked by anonymous 19.10.2017 / 15:16

1 answer

3

Apparently you missed the connection before using the prepare function, you can do it this way:

$conn = new PDO('mysql:host=localhost;dbname=seu_banco_de_dados', 'seu_usuario', 'sua_senha');

Now from the $conn variable that is where your connection is, you run prepare and assign its $buscarid variable:

<select class="emselect">
    <option value="select">selecione</option>
    <?php
    $buscarid = $conn->prepare("SELECT ID, usuario FROM usuarios ORDER BY ID ASC");
    $buscarid->execute();
    while($linha=$buscarid->fetch(PDO::FETCH_ASSOC)){
        echo "<option value=".$linha["ID"].$linha["usuario"].">".$linha["ID"]." ".$linha["usuario"]."</option>";
    }
    ?>
</select>

However, this will require you to repeat the code to make the connection every time you need to connect to the database. If maintenance is necessary in your system, you may have a headache. To avoid this code repetition, you can create a class where the connection will be created, so create a new file PHP named conexao.class.php and put the following content:

<?
    class Conexao extends PDO { // Criamos a classe Conexao que estende todas os métodos e propriedades da classe PDO

        private static $instancia; // Criamos uma variável $instancia que é onde a instancia do PDO ficará quando criada

        //Este método abaixo é o construtor, ou seja, ele será executado quando for criada uma nova instancia da nossa classe Conexao.
        public function Conexao($dsn, $username, $password){
            parent::__construct($dsn, $username, $password);//Aqui estamos chamando o método construtor da classe pai que no nosso caso é PDO
        }

        //Este método, será responsável por criar a conexão ou retornar a conexão existente
        public static function getInstance(){
            if (!isset(self::$instancia)){ //Se a variável $instancia não estiver setada, a conexão será criada
                try{
                    //Neste ponto criamos uma instancia da classe Conexao e armazenamos ela dentro da variável $instancia
                    self::$instancia = new Conexao("mysql:host=localhost; dbname=seu_banco_de_dados",
                        "seu_usuario", "sua_senha");
                } catch (Exception $e) { //Em caso de erro ao tentar se conectar será retornada uma string com o erro
                    return "Ocorreu um erro ao conectar!<br />".$e->getMessage();
                }
            }

            return self::$instancia; //Caso der tudo certo será retornada a Conexao criada
        }
    }
?>

Your code to recover users would look like this:

<?
    include_once 'conexao.class.php';
    $conn = Conexao::getInstance();
    if (!is_string($conn)) { //Se a variável $conn não for uma string a conexão foi efetuada com sucesso
        ?>
            <select class="emselect">
                <option value="select">selecione</option>
                <?
                    $buscarid = $conn->prepare("SELECT ID, usuario FROM usuarios ORDER BY ID ASC");
                    $buscarid->execute();
                    while($linha=$buscarid->fetch(PDO::FETCH_ASSOC)){
                        echo "<option value=".$linha["ID"].$linha["usuario"].">".$linha["ID"]." ".$linha["usuario"]."</option>";
                    }
                ?>
            </select>
        <?
    } else {//Caso a variável $conn seja uma string, ela conterá o erro que ocorreu na conexão
        echo $conn;
    }
?>
    
19.10.2017 / 15:24