从SQL填充select字段,使用select更新数据库

从SQL填充select字段,使用select更新数据库

问题描述:

I am practicing with developing a little bit but I stumbled upon a small issue which I just cannot get my head around. The goal is simple: users fill out a form and are then redirected to either the results page or the form. From the results page, users can navigate to a page where they can edit any records that match the time period and the name they enter.

Example: User 1 adds a car named 'Banshee', is redirected to the results page, sees the 'Banshee' car but then decides he wants the color to be different. The user navigates to the update page and is able to either type in 'Banshee' or select 'Banshee' from a dropdown list. The first option works but the latter is the solution I'm looking for.

Currently I have the following form (this is just the part that matters):

<form method="post" name="input" action="edittest.php" >

    <?php
    include("/var/www/html/includes/sqlconnecttest.php");

    $db=mysqli_select_db($connect,$database)
    or die("Could not connect to the database");

    $query = "SELECT * FROM cars WHERE datetime > DATE_SUB(CURDATE(), INTERVAL 5 DAY);";
    $sql = mysqli_query($connect,$query);
?>

    <p>
    <label>Name</label>
    <select name="name" form="input" required>
    <?php
    echo "<option value=\"\" disabled=\"disabled\" selected=\"selected\" style=\"display\:none\"></option>";
    while ($row = mysqli_fetch_array($sql)){
    echo "<option value=\"name\">" . $row['name'] . "</option>";
    }
    ?>
    </select>
    </p>

The connection is done through the following PHP code:

<?php
    include("/var/www/html/includes/sqlconnecttest.php");

    $db = new mysqli("$host","$user","$pass","$database");

    $name=$_POST['name'];

    $stmt = $db->prepare("UPDATE cars
                          SET color=?, airco=?, tires=?, rims=?, price=?, engine=?, remarks=?
                          WHERE name='$name' AND datetime > DATE_SUB(CURDATE(), INTERVAL 5 DAY);");
    $stmt->bind_param('ssssiss', $ccolor, $cairco, $ctires, $crims, $cprice, $cengine, $cremarks);

    $color=$_POST['color'];
    $airco=$_POST['airco'];
    $tires=$_POST['tires'];
    $rims=$_POST['rims'];
    $price=$_POST['price'];
    $engine=$_POST['engine'];
    $remarks=$_POST['remarks'];

    $ccolor=htmlspecialchars($color,ENT_QUOTES);
    $cairco=htmlspecialchars($airco,ENT_QUOTES);
    $ctires=htmlspecialchars($tires,ENT_QUOTES);
    $crims=htmlspecialchars($rims,ENT_QUOTES);
    $cprice=htmlspecialchars($price,ENT_QUOTES);
    $cengine=htmlspecialchars($engine,ENT_QUOTES);
    $cremarks=htmlspecialchars($remarks,ENT_QUOTES);

    if ($stmt->execute()) {

    header("Location: redtest.php");
 }
 ?>

The issue is as follows: when I use the above posted HTML form with the while-loop populating the options, the update page doesn't do anything. No errors, no updates it just performs the action (open edittest.php).

However, when I replace the above posted dropdown menu with this simple textfield, it works fine. Whatever I type in the textfield, it uses it in the update query.

    <p>
    <label>Name</label>
    <input type="text" maxlength="50" name="name" required />
    </p>

I've got the feeling that the PHP code breaks the connection between the select-field and the rest of the form.

I see two issues with your select. First you have what appears to be a default value that you're then hiding with display:none. You're also setting the same value for every option. I believe this is what you're trying to do.

<select name="name" form="input" required>
  <option value=""></option>
<?php
  while ($row = mysqli_fetch_array($sql)){
     echo "<option value=\"". $row['name'] ."\">" . $row['name'] . "</option>";
  }
?>
</select>