Taking mySQL database input from HTML form with PHP

Question

I'm trying to take in data from a webpage with a HTML form and PHP to my mySQL Database. It connects just fine on both pages but I get an error when I try to submit from the form. It will take in data if I just write it into the PHP myself and click submit, but it won't take it from the form so there must be something wrong there but I can't figure out what. I've never used PHP with mySQL before so I'm not too sure how it all works. Any help with an explanation of how it's working would be appreciated.

Below is my test.html.php page where my form is and the testinsert.php page where I try to insert the data.

(Also, courseID is a foreign key in the 'test' table, so i need to make the courseID selectable from the options, i struggled with this and I don't know if this is where the issue lies. In the current code it is in a drop down menu, it shows the courseID's but there is a blank option in between each option e.g. the list of options will be - '4', 'blank', '5'... etc)

<!DOCTYPE html>

<?php
include 'connect.php';
?>

<html lang="en">
<head>
    <link rel="stylesheet" type="text/css" href="style.css" />
    <meta name="viewport" content="width=1024, initial-scale=1.0, maximum-scale=1.0,user-       scalable=no"/>
</head>
    <title>Test Sign Up</title>
<body>

<header>
    <h1>Test Sign Up</h1>

</header>
        <div class="contactform">
            <form action="testinsert.php" method ="post">

                <label for="name">Name:</label>
                <input type="text" id="name" name="name" placeholder="Enter 
                your name here" required>

                <label for="testsentence">Test Sentence:</label>
                <input type="text" id="testsentence" name="testsentence" placeholder="Enter your sentence here" required>


                <label for="course">Course:</label>
                <select id="course" name="course">
                <?php

                $query = "SELECT CourseID FROM Course";

                $result = mysqli_query($conn, $query);


                while($row = mysqli_fetch_array($result)){

                    echo "<option>" . $row['CourseID'] . "<option>";

                }

                mysqli_close($conn);

                ?>
                </select>

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

            </form>                                    
        </div>
    <p></p>
    <a href="courses.html.php">View Courses</a>
    <p></p>
    <a href="index.html">Return to home page</a>
</body>    
</html>

Testinsert.php -

<?php
include 'connect.php';

$name = 'name';
$testsentence = 'testsentence';
$courseid = 'course';

$sql="INSERT INTO Test (Name, TestSentence, Course)
VALUES ('$name','$testsentence', '$courseid')";

if (mysqli_query($conn, $sql)) {
    echo "<p></p>New record added successfully";
    echo '<p></p><a href="index.html">Return to home page</a>';
} else {
    echo "<p></p>Error adding record";
    echo '<p></p><a href="index.html">Return to home page</a>';
}

mysql_close($conn);
?>

@PatrickQ its the 'error adding record' error that i wrote in testinsert.php — Chloe13, Jan 15 '19 at 21:48
The code that you're showing has hardcoded values for `$name`, `$testsentence`, and `$courseid`. If you are not getting errors with the hardcoded values, please update the code in your question to reflect the version that _is_ causing problems. Also, you should be using [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and preferably the object-oriented format of mysqli instead of the procedural format. — Patrick Q, Jan 15 '19 at 21:53
Are you extracting the values from the form into your PHP script, i.e. `extract($_POST)` or something similar? You cannot use the values directly from the form, as your `$name`, `$testsentence`, and `$courseid` will be undefined. — Anis R., Jan 15 '19 at 22:11
@AnisR. ohh! i forgot to put ($_POST), whoops, this fixed it! thanks — Chloe13, Jan 15 '19 at 22:23
You're welcome! I'll put that in an answer, if anyone faces the same thing later on. — Anis R., Jan 15 '19 at 22:25
@EdenReich the asker already got their answer, if you read previous comments. — Anis R., Jan 15 '19 at 22:30

mickmackusa · Accepted Answer · 2019-01-16T00:01:12.803

You are getting blank options AFTER each option with an expected value because you have failed to write a closing option tag. / needs to be written into the second option tag like this:

while ($row = mysqli_fetch_array($result)) {
    echo "<option>{$row['CourseID']}</option>";
}

The option tags still render even if you don't properly close them. In this case, the error presents itself by generating twice the desired tags.

I recommend that you use MYSQLI_ASSOC as the second parameter of your mysqli_fetch_array call or more conveniently: mysqli_fetch_assoc

In fact, because $result is iterable, you can write:

foreach ($result as $row) {
    echo "<option>{$row['CourseID']}</option>";
}

About using extract($_POST)...

I have never once found a good reason to use extract in one of my scripts. Not once. Furthermore, the php manual has a specific Warning stating:

Warning Do not use extract() on untrusted data, like user input (e.g. $_GET, $_FILES).

There are more warning down the page, but you effectly baked insecurity into your code by calling extract on user supplied data. DON'T EVER DO THIS, THERE IS NO GOOD REASON TO DO IT.

Here is a decent page that speaks about accessing submitted data: PHP Pass variable to next page

Specifically, this is how you access the expected superglobal data:

$name = $_POST['name'];
$testsentence = $_POST['testsentence'];
$courseid = $_POST['course'];

You must never write unfiltered, unsanitized user supplied data directly into your mysql query, it leads to query instability at best and insecurity at worst.

You must use a prepared statement with placeholders and bound variables on your INSERT query. There are thousands of examples of how to do this process on Stackoverflow, please research until it makes sense -- don't tell yourself that you'll do it layer.

thank you for the explanation, i didnt know about this. I'll look into this more — Chloe13, Jan 16 '19 at 00:07

Anis R. · Answer 2 · 2019-01-15T23:29:03.813

-2

Make sure you added extract($_POST) (or something similar) in your PHP code!

You need to extract the parameters from your POST request before using them, otherwise your $name, $testsentence, and $courseid will be undefined.

edited Jan 15 '19 at 23:29

answered Jan 15 '19 at 22:28

Anis R.

6,656
2
15
37

Taking mySQL database input from HTML form with PHP

2 Answers2