asked on

Insert dropdown value into mysql db

I am trying to select a value from dropdown list and insert into mysql database, i am new to PHP and i know my code is not pretty but i want to learn, please steer me in the right direction. Thank You.

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title></title>
</head>

<body>

<form method="post" action="">
 
 <label >Please Select Username</label>
 
 <select name="checkin">
<?php
 require('db.php'); 

$sql = mysqli_query($con, "SELECT * FROM users LEFT JOIN check_in_out on users.id = check_in_out.check_id WHERE check_in_out.check_id IS NULL");
while ($row = $sql->fetch_assoc()){
echo "<option value=\"checkin\">" . $row['id'] . "</option>";
}

 ?>

</select>

 <button type="submit" name="submit" >Submit</button>
 


<?php
 require('db.php'); 
if(isset($_POST["submit"]))
{
 
$check_id ='check_id';
$check_in_date = date("Y-m-d H:i:s");
$sql =  "INSERT INTO check_in_out (check_id,check_in_date) VALUES ('$check_id','$check_in_date')"; 
$result = mysqli_query($con,$sql);

    echo "You are checked in";
}

 ?>
  </form> 
</body>
</html>

Open in new window

Swatantra Bhargava

Try below

Change :

echo "<option value=". $row['id'] .">" . $row['id'] . "</option>";

if(isset($_POST["submit"]))
{

$check_id =$_POST["checkin"];
$check_in_date = date("Y-m-d H:i:s");
$sql = "INSERT INTO check_in_out (check_id,check_in_date) VALUES ('$check_id','$check_in_date')";
$result = mysqli_query($con,$sql);

echo "You are checked in";
}

ozzy t

ASKER

Thank You Swatantra!

Chris Stanyon

OK. Couple of things to note.

You only need to include the db connection once. It makes sense to do this at the top of your page (and you should require it, not include it). It also makes sense to turn on error reporting when you're debugging so that you can see any errors that your code generates.

As you're starting out with doing this, I would strongly suggest you start to learn how to deal with your SQL DB in an Object Oriented way. It's just a much cleaner wat of doing things.

In your code, you are generating a <select> with several options, but the values to every option is the same - "checkin", so no matter what options use selects, that's what will be submitted. You then try and insert values into your database that have nothing to do with the form.

If you want to insert values into your DB from a user-filled in form, then you will also need to learn about preapred statements - this adds security to your site as it prevents SQL Injection.

When running select queries, don't get into the habit of SELECT *. Be explicit with the fields you want to include - SELECT id, firstname, username.

To start with, I would just worry about getting the options populated and then dump the data from the form so you can see what's going on.

Have a look at these 2 snippets of code - the first is the DB connection, and the second is a basic HTML form populated from the DB:

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

$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'yourDb';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try  {
    $db = new mysqli($hostname, $username, $password, $database);
    $db->set_charset("utf8");
} catch (mysqli_sql_exception $e) {
    die( $e->getMessage() );
}

Open in new window

<?php require_once 'db.php'; ?>
<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Chris Stanyon // EE - 29109325</title>
    </head>
    <body>

        <?php $users = $db->query("SELECT id FROM users LEFT JOIN check_in_out on users.id = check_in_out.check_id WHERE check_in_out.check_id IS NULL"); ?>

        <form method="post">
            <label>Please Select Username</label>
            <select name="checkin">
                <?php while ($user = $users->fetch_object()): ?>
                    <option value="<?= $user->id ?>"><?= $user->id ?></option>
                <?php endwhile; ?>
            </select>
            <button type="submit" name="submit" >Submit</button>
        </form>

        <?php
        if (!empty($_POST)):
            var_dump($_POST);
        endif;
        ?>

    </body>
</html>

Open in new window

Have a read through the code to make sure you understand it. Once you've got that part in play, I'll show you how to use a prepared statement to insert the form data into a DB table.

ozzy t

ASKER

Thank You Chris, i really appreciate you taking the time to explain these concepts to me, they are very insightful and you have opened up my eyes.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.