UPDATE mysql record of the selected value from dropdown list

Question

I'm just learning Mysql/PHP and I'm trying to update a mysql record from a selected value from the dropdown list. I have read through several tutorials, and tried to apply them, but I cannot get this working...

What I want : I got a dropdown list that get values from the mysql database( UNIQUE ID, Number, Model, Serialnumber, Capacity). I want to UPDATE the selected value from the dropdown menu. Every value from the dropdown menu got a UNIQUE ID.

Problem: When I hit submit, it does nothing. When I change the SQL query from WHERE id='$id'" TO WHERE id=23 it will update the record with id 23. So it has something to do with that.

I know my code is a mess, but will clean later. My code : ipad-uitlenen.php

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
session_start();
include("../includes/connect.php");
if(isset($_SESSION['logged_in'])){
  $sql="SELECT id, nr, model, serienummer, capaciteit FROM ipads WHERE uitgeleend='Nee' ORDER BY nr";
  $result1 = mysqli_query($db, $sql);
 
 if(isset($_POST['btnAdd'])) {
  $id=$_REQUEST['id'];
  $persoon=$_POST['persoon'];
  $datumuitgeleend=$_POST['datumuitgeleend'];
  $datumretour=$_POST['datumretour'];
  $opmerking=$_POST['opmerking'];
  $sql="UPDATE ipads SET uitgeleend='Ja', persoon='$persoon', datumuitgeleend='$datumuitgeleend', datumretour='$datumretour', opmerking='$opmerking' WHERE id='$id'";
  $result=$db->query($sql);
  header("location:overzicht-ipads.php");

 }
 include("../includes/get_header_wn.php");
?>

            <h1 class="page-title">iPad uitlenen</h1>
                    <ul class="breadcrumb">
            <li><a href="index.php">Home</a> </li>
            <li class="active">Nieuw</li>
        </ul>
        </div>

<form id="gegevensForm" class="col-xs-4" form method="POST" action="ipad-uitlenen.php">
 <div class="form-group">
 <select name="id">
    <?php while ($row1 = mysqli_fetch_array($result1)):;?>
    <option value="<?php echo $id?>"><?php echo $row1[1];?> / <?php echo $row1[3];?> / <?php echo $row1[2];?> / <?php echo $row1[4];?></option>
    <?php endwhile;?>
</select>
 </div>

 <div class="form-group">
        <label>Persoon</label>
        <input type="text" class="form-control" name="persoon" value="" />
    </div>
 
 <div class="form-group">
        <label>Datum uitgeleend</label>
        <input type="text" id="datepicker" class="form-control" name="datumuitgeleend" value="" />
    </div>
 
 <div class="form-group">
        <label>Datum retour</label>
        <input type="text" id="datepicker1" class="form-control" name="datumretour" value="" />
    </div>
 
 <div class="form-group">
  <label for="comment">Opmerking</label>
  <textarea class="form-control" rows="5" id="comment" name="opmerking"></textarea>
 </div>
 
    <button class="btn btn-primary pull-right" name="btnAdd" input type="submit"><i class="fa fa-save"></i> Opslaan</button>
    <a href="overzicht-ipads.php"><input type="button" name="btnCancel" value="Annuleer" class="btn btn-primary pull-left"></a>
    
    <?php
  include('../includes/get_footer.php');
 ?>
    
</form>

<?php
}
?>

Connect.php

<?php
 try {
  $db = mysqli_connect("localhost", "root", "", "i3a");
 }
 catch(PDOException $e){
  echo $e->getMessage();
  die();
 }
 //PDO(database:localhost:3307;dbnaam, root, wachtwoord)
?>

Answer 1

Try with this,

Provide name to select tag not to option.

 <select name="id">
    <?php while ($row1 = mysqli_fetch_array($result1)):;?>
    <option value="<?php echo $id?>"><?php echo $row1[1];?> / <?php echo $row1[3];?> / <?php echo $row1[2];?> / <?php echo $row1[4];?></option>
    <?php endwhile;?>
</select>

Answer 2

Besides the other answer given in regards to naming the <select>, instead of <option>, something to which I commented about myself.

(Now) Seeing what you're using to connect with, and added in a later edit:

$db=new PDO("mysql:host=localhost;dbname=i3a", "root", "");

and you're using mysqli_* to query with.

Those different MySQL APIs do NOT intermix. You MUST use the same one from connecting to querying.

So... either use all PDO or all mysqli_, nothing else.

So, instead of changing your entire code to PDO (that's up to you), you need to connect with using mysqli_.

• http://php.net/manual/en/function.mysqli-connect.php

I.e.:

$db = mysqli_connect("yourHost", "User", "Password", "DB");

The $db variable is what you need to use throughout your entire query.

I.e.:

• $result1 = mysqli_query($db, $sql);
• $result=$db->query($sql);

Having used error checking and error reporting, would have thrown you something about that.

Sidenote: What is happening here, is that you are most likely losing your connection in having to reconnect in the other query.

So, keep the connection open all the time without having to switch from one API to the other.

You already connected with:

$hostname = "localhost";
$username = "root";
$password = "";
$databaseName = "i3a";
$connect = mysqli_connect($hostname, $username, $password, $databaseName);

So why switch to PDO using another connection?

Keep the same one (mysqli_) and use $connection then if you're going to use the above to keep the connection alive.

• $result1 = mysqli_query($connection, $sql);
• $result=$connection->query($sql);

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Then the rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

and check for errors on the queries.

• http://php.net/manual/en/mysqli.error.php

You're not doing that.

Answer 3

Got it solved by adding this in value :

<option value="<?php echo $row1[0]; ?>">