Inserting data from dropdown into MySQL table

DeckDekk · May 22, 2020

Hello there!

I've been banging my head on this for a while and I just can't seem to get it to work properly.

I have a dropdown menu which selects information from table1 using a select statement (this table is called 'lid'). It selects the firstname, lastname and member id from this table and shows it in the dropdown menu. I'm glad I got that part working but the hard thing is inserting the data that the user selects into another table. So when you select the id member from this dropdown menu it only inserts a blank row into table2 (which is called 'teamlid'). Can you guys help me? How can I insert the id member into my table2? What am I doing wrong here?

Thanks a million! This is my first post so if I'm doing anything wrong, let me know and I'll fix it asap!

My code:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Boast & Drive</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        .wrapper{
            width: 650px;
            margin: 0 auto;
        }
        .page-header h2{
            margin-top: 0;
        }
        table tr td:last-child a{
            margin-right: 15px;
        }
    </style>
</head>
<body>
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header clearfix">
                        <h2 class="pull-left">Teamleden</h2>
                        <div class="btn-toolbar">
                        <a href="read.php" class="btn btn-primary btn-lg pull-right">Terug</a>
                        </div>
                    </div>
                    
                    <?php
                    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
                    error_reporting(E_ALL);

                    //Verbinding maken met de database
                    require_once "login.php";

                    $sql = "SELECT tl.teamnaam,
                                   tl.tl_ID,
                                   tl.lidnummer,
                                   l.voornaam,
                                   l.achternaam
                            
                            FROM   teamlid tl
                            
                            JOIN    lid l   ON tl.lidnummer = l.lidnummer
                            
                            ORDER BY tl.teamnaam;";  
                    
                    if($result = mysqli_query($conn, $sql)) {
                        if(mysqli_num_rows($result) > 0) {
                            echo "<table class='table table-bordered table-striped'>";
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>Teamnaam</th>";
                                        echo "<th>Tl_ID</th>";
                                        echo "<th>Lidnummer</th>";
                                        echo "<th>Voornaam</th>";
                                        echo "<th>Achternaam</th>";
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = mysqli_fetch_array($result)){
                                    echo "<tr>";
                                        echo "<td>" . $row['teamnaam'] . "</td>";
                                        echo "<td>" . $row['tl_ID'] . "</td>";
                                        echo "<td>" . $row['lidnummer'] . "</td>";
                                        echo "<td>" . $row['voornaam'] . "</td>";
                                        echo "<td>" . $row['achternaam'] . "</td>";
                                        echo "<td>";
                                            echo "<a href='update.php?id=". $row['lidnummer'] ."' title='Gegevens wijzigen' data-															toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>";
                                            echo "<a href='delete.php?id=". $row['lidnummer'] ."' title='Lid verwijderen' data-																toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>";
                                        echo "</td>";                                        
                                    echo "</tr>";
                                }
                                echo "</tbody>";
                            echo "</table>";

                            mysqli_free_result($result);
                        } else{
                            echo "<p class='lead'><em>Er zijn geen gegevens om weer te geven.</em></p>";
                        }
                    } else{
                        echo "De volgende fout is gevonden:  " . mysqli_error($conn);
                    }
                    
                    ?>

                    
                    <form name="dropdown" method="post">
                     <div class="page-header clearfix">
                        <h2 class="pull-left">Teamlid toevoegen</h2>
                    </div>           
                    <p>Selecteer hieronder met behulp van het dropdown menu een lid welke je aan bovenstaand team wilt toevoegen</p>
                        
                    <div class="container-fluid">
                        <div class="row">

                        <?php
                        // Variabelen aanmaken en tonen met lege waardes
                        $teamnaam = $lidnummer = '';

                        // Code voor dropdown. Selecteert voornaam, achternaam en lidnummer van tabel lid)
                        $sql = "SELECT voornaam, achternaam, lidnummer FROM lid ORDER BY achternaam";
                        $result = mysqli_query($conn, $sql);

                        echo "<select id='teamLid' name='teamLid'>";
                            echo "<option>--Selecteer Lid--</option>";
                        while ($row = mysqli_fetch_array($result)) {
                            echo "<option value='" . $row['lid'] . "'>" . $row['voornaam'] . "  " . $row['achternaam'] . " " . $row['lidnummer'] . "</option>";
                        }
                        echo "</select>";
                        
                        if 
                        (isset($_POST["id"]) && !empty($_POST["id"])) {
                            $id = $_POST["teamLid"];

                            $stmt = $conn->prepare("INSERT INTO teamlid (teamnaam, lidnummer) VALUES (?,?)");
                            $stmt->bind_param('si', $param_teamnaam, $param_lidnummer);
                            $param_teamnaam         = $teamnaam;
                            $param_lidnummer        = $lidnummer;
                            $stmt->execute();

                        }
                        // Verbinding sluiten
                        mysqli_close($conn);
                        ?>
                            <div>
                                <input type="hidden" name="id" value="<?php echo $id; ?>" />
                                <input type="submit" name="submit" class="btn btn-primary" value="Toevoegen">
                            </div>
                        </div>
                    </div>
                </form>
            </div>  
        </div>
</body>
</html>

requinix · May 22, 2020

$teamnaam = $lidnummer = '';

That's the only time you ever assign values to those variables.

DeckDekk · May 25, 2020

You're right about that. But to get the input from the dropdown, what value should I assign to those variables?
(the reason I assigned empty strings to those variables is because I copied them from my other application page where I used an input form to get the data. I thought if I use that as a basis and work on from there it would help, but alas).

requinix · May 25, 2020

4 minutes ago, DeckDekk said:

You're right about that. But to get the input from the dropdown, what value should I assign to those variables?

Here is a good place to start.

DeckDekk · May 28, 2020

Thanks! Still trying to get my head around it but I still haven't found a solution.

DeckDekk · June 9, 2020

So I'm still banging my head over this one. I can't find the value needed to add into the table. And the only thing that gets added into the table are empty strings and integer values.

I looked at what requinix said about the $teamname and $memberid values. But whatever value I try to assign to it, it doesn't insert into the database (unless I do 'teamname', then it inserts the string 'teamname' into the table). I also looked at the documentation but I can't apply that knowledge to my "insert dropdown data into different table" problem

I changed everything to english for you guys, maybe that way you can point me into the right direction.

Much appreciated!

This is what I've got:

  <form name="dropdown" method="post">
                        <div class="page-header clearfix">
                            <h2 class="pull-left">Add teammember</h2>
                        </div>           
                        <p>Use the dropdown to select which member you want to add to the team</p>
                        
                        <div class="container-fluid">
                            <div class="row">

                            <?php
                            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
                            error_reporting(E_ALL);
                            
                            $teamname = '';
                            $memberid = '';

                            $sql = "SELECT firstname, lastname, memberid FROM member ORDER BY surname";
                            $result = mysqli_query($conn, $sql);
                                
                                echo "<select id='teammember' name='teammember'>";
                                echo "<option>--select member--</option>";
                            while ($row = mysqli_fetch_array($result)) {
                                echo "<option value='" . $row['member'] . "'>" . $row['firstname'] . "  " . $row['lastname'] . " " . 											$row['memberid'] . "</option>";											
                            }
                            echo "</select>";
                            
                            if 
                            (isset($_POST["id"]) && !empty($_POST["id"])) {
                                $id = $_POST["id"];

                                $stmt = $conn->prepare("INSERT INTO teammember (teamnname, memberid) VALUES (?,?)");
                                $stmt->bind_param('si', $param_teamname, $param_memberid);
                                $param_teamname         = $teamname;
                                $param_memberid         = $memberid;
                                $stmt->execute();

                            }

                            mysqli_close($conn);
                            ?>
                                <div>
                                    <input type="hidden" name="id" value="<?php echo $id; ?>" />
                                    <input type="submit" name="submit" class="btn btn-primary" value="Add">
                                </div>
                            </div>
                    </div>
                </form>

Edited June 9, 2020 by DeckDekk

Barand · June 9, 2020

image.png.43a3f0199d34504128fd50bcb7bbc6cb.png

You are setting the option values to $row['member'] but you don't select anything called "member"
Why concatenate nothing at the end of the line?
Why are you processing the posted data in the middle of your form output? Move it before the form.
The $id in the form is doing nothing.

Other problems you have already been told about and done nothing to fix them.

Edited June 9, 2020 by Barand

DeckDekk · June 10, 2020

Thanks! I'll get on it asap.

Sign In

Inserting data from dropdown into MySQL table

Recommended Posts

DeckDekk

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

DeckDekk

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

DeckDekk

Link to comment

Share on other sites

DeckDekk

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

DeckDekk

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information