Display Value of BD in SELECT in PHP

0

Good morning, in the code that I show next I want to show the value of a field saved in a BD Mysql in a SELECT list in php

Code:

<form name="fe" action="" method="post">
        <table border="2">
            <tbody><tr><td>Nombre de Usuario</td>
            <td><input name="txtbus" type="text"></td>
            <td><input name="btn1" value="Buscar" type="submit" onsubmit="this.reset()"></td></tr>
    </form>
<?php
$btn = "";
if(isset($_POST["btn1"])){
    $btn=$_POST["btn1"];
    $bus=$_POST["txtbus"];
if($btn=="Buscar"){
require_once('config.php');
            $mysqli = mysqli_init();
            $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);      $mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']);
$queryM = "SELECT nombre_usuario,nombre,idnivel,idservicio,idestado, servicio,estado,nivel
          FROM usuario,servicio,estado,nivel WHERE nombre_usuario = '$bus' and 
          idservicio = servicio.id and
          idestado   = estado.id and
          idnivel    = nivel.id ORDER BY usuario.id";
          $result    = $mysqli->query($queryM);
?>  
<form class='contacto'>
            <div><etiqueta>Nombre de Usuario:</etiqueta><input type='text' value='<?php echo $row['nombre_usuario']?>'></div>
            <div><etiqueta>Empleado:</etiqueta><input type='text' value='<?php echo $row['nombre']?>'></div>
            <div>
                <etiqueta>Nivel</etiqueta>
                <select id="nivel" name="nivel">
                        <?php 
                            $result2 = $mysqli->query('SELECT * FROM nivel');
                            while($row2 = $result2->fetch_array())
                                {
                                echo'<OPTION VALUE="'.$row2['id'].'">'.$row2['nivel'].'</OPTION>';
                                }
                                $mysqli->close();
                        ?> 
                    </select>   
            </div>
            <div><etiqueta>Tipo de Servicio:</etiqueta><input type='text' value='<?php echo $row['servicio']?>'></div>
            <div><etiqueta>Estatus:</etiqueta><input type='text' value='<?php echo $row['estado']?>'></div>
            <!--<div><label>Estatus:</label><textarea rows='6'></textarea></div>-->
            <a id="cancelupdate" class="button delete" onsubmit="this.reset()">Cancelar</a>
            <div><input type='submit' value='Envia Mensaje' onsubmit="this.reset()"></div>
        </form>
<?php
    }
?>

All the data is displayed, but I want the SELECT Option to show me the value that is actually stored in the database.

Tables: CREATE TABLE usuario (    id int (5) NOT NULL,    nombre_usuario varchar (30) NOT NULL,    password varchar (72) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,    nombre varchar (30) NOT NULL,    idnivel varchar (30) NOT NULL,    idservicio int (5) NOT NULL,    idestado int (5) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = latin1;

CREATE TABLE nivel (    id int (4) NOT NULL,    nivel varchar (20) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE servicio (    id int (5) NOT NULL,    servicio varchar (20) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE estado (    id int (5) NOT NULL,    estado varchar (10) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8;

    
asked by HGarcia 01.11.2017 в 15:25
source

2 answers

0

Inside the while it analyzes the value that the user has; assuming it is idnivel:

while($row2 = $result2->fetch_array())
{
    $sel = ($row['idnivel'] == $row2['id']) ? ' selected' : '';
    echo "<option value=\"{$row2['id']}\"$sel>{$row2['nivel']}</option>";
}

If you are not working with HTML5 then instead of 'selected' you should put 'selected="selected"'.

Remember that all HTML tags (regardless of the version) and their attributes must be written in lowercase.

    
answered by 01.11.2017 / 16:11
source
0

This is my proposal, taking into account what has been said in my comments.

I omitted the second SELECT, because I understood that it was not necessary for the purposes of your program. You may also consider selecting fewer fields in your SQL query, if you are not going to need them.

<form name="fe" action="" method="post">
        <table border="2">
            <tbody><tr><td>Nombre de Usuario</td>
            <td><input name="txtbus" type="text"></td>
            <td><input name="btn1" value="Buscar" type="submit" onsubmit="this.reset()"></td></tr>
    </form>
<?php
$btn = "";
if(isset($_POST["btn1"])){
    $btn=$_POST["btn1"];
    $bus=$_POST["txtbus"];

    if($btn=="Buscar"){

        require_once('config.php');
        $mysqli = mysqli_init();
        $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
        $mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']);

        if ($mysqli){
            $queryM = 
                    "SELECT u.nombre_usuario, u.nombre, u.idnivel, s.idservicio, 
                            e.idestado, s.servicio, e.estado, n.nivel
                      FROM usuario u 
                      INNER JOIN servicio s ON u.idservicio = s.id 
                      INNER JOIN estado e ON u.idestado   = e.id
                      INNER JOIN nivel n ON u.idnivel    = n.id
                      WHERE u.nombre_usuario = ?
                      ORDER BY u.id";
            $stmt=$mysqli->prepare($queryM);
            if ($stmt){

              $stmt->bind_param("s",$bus);
              $stmt->execute();
              $stmt->bind_result($nombre_usuario, $nombre, $idnivel, $idservicio, $idestado, $servicio, $estado, $nivel);   
?>  
            <form class='contacto'>
            <div><etiqueta>Nombre de Usuario:</etiqueta><input type='text' value='<?php echo $row['nombre_usuario']?>'></div>
            <div><etiqueta>Empleado:</etiqueta><input type='text' value='<?php echo $row['nombre']?>'></div>
            <div>
                <etiqueta>Nivel</etiqueta>
                <select id="nivel" name="nivel">
<?php 
              while ($stmt->fetch()) {
                    echo "<option value=\"$idnivel\">$nivel</option>";
              } 
              $stmt->close();
?> 
            </select>   
            </div>
            <div><etiqueta>Tipo de Servicio:</etiqueta><input type='text' value='<?php echo $row['servicio']?>'></div>
            <div><etiqueta>Estatus:</etiqueta><input type='text' value='<?php echo $row['estado']?>'></div>
            <!--<div><label>Estatus:</label><textarea rows='6'></textarea></div>-->
            <a id="cancelupdate" class="button delete" onsubmit="this.reset()">Cancelar</a>
            <div><input type='submit' value='Envia Mensaje' onsubmit="this.reset()"></div>
        </form>
<?php
      }else{

          echo "Error en la consulta SQL";

      }
              $mysqli->close();

}else{

        echo "No se pudo conectar a la base de datos";  
}

    }else{

        echo "Opción buscar no fue seleccionada"; //Esta comprobación se puede omitir o cambiar por otra cosa

    }

}else{

    echo "btn1 no presionado";

}
    
answered by 01.11.2017 в 17:30