Insert formfields to SQL - Error

Question

Hi so I have a form with 10 fields and I am trying to insert them on an SQL databse through posting them on a PHP page. Connection starts fine, but it returns the error below:

Error: INSERT INTO courses (name, teacher, description, class, DAYONE, DAYTWO, DAYTHREE, STD1, STD2, STD3) VALUES (, , , , , , , , , ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , , , , )' at line 1

include_once 'connect.php';   
// Create connection
$conn = new mysqli(HOST, USER, PASSWORD, DATABASE);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$name = $_POST['name'];
$teacher = $_POST['teacher'];
$description = $_POST['description'];
$class = $_POST['class'];
$dayone = $_POST['dayone'];
$daytwo = $_POST['daytwo'];
$daythree = $_POST['daythree'];
$std1 = $_POST['std1'];
$std2 = $_POST['std2'];
$std3 = $_POST['std3'];

$sql = "INSERT INTO courses (name, teacher, description, class, DAYONE, DAYTWO, DAYTHREE, STD1, STD2, STD3) VALUES ($name, $teacher, $description, $class, $dayone, $daytwo, $daythree, $std1, $std2, $std3)";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

I should also mention that the database table has one more field called ID type int(11) which is AUTO_INCREMENT and I expect it to be automatically filled everytime a new row is inserted. Am I wrong?

EDIT: Added HTML code since it has been asked

<form name="registration_form" method="post" class="clearfix" action="create.php">
    <div class="form-group">
        <label for="name">NAME</label>
        <input type="text" class="form-control" id="name" placeholder="Course Name">
    </div>
    <div class="form-group">
        <label for="teacher">Teacher</label>
        <input type="text" class="form-control" id="teacher" placeholder="Teacher's Name">
    </div>
    <div class="form-group">
        <label for="description">Description</label>
        <textarea class="form-control" id="description" placeholder="Description"></textarea>
    </div>
    <div class="form-group">
        <label for="class">Class</label>
        <input type="text" class="form-control" id="class" placeholder="Class Name">
    </div>
    <div class="form-group">
        <label for="dayone">Day one</label>
        <input type="text" class="form-control" id="dayone" placeholder="Day One">
    </div>
    <div class="form-group">
        <label for="daytwo">Day two</label>
        <input type="text" class="form-control" id="daytwo" placeholder="Day Two">
    </div>
    <div class="form-group">
        <label for="daythree">Day three</label>
        <input type="text" class="form-control" id="daythree" placeholder="Day Three">
    </div>
    <div class="form-group">
        <label for="std1">std1</label>
        <input type="text" class="form-control" id="std1" placeholder="std1">
    </div>
    <div class="form-group">
        <label for="std2">std2</label>
        <input type="text" class="form-control" id="std2" placeholder="std2">
    </div>
    <div class="form-group">
        <label for="std1">std3</label>
        <input type="text" class="form-control" id="std3" placeholder="std3">
    </div>
    <div class="checkbox">
        <label>
            <input type="checkbox">I Understand <a href="#">Terms & Conditions</a>
        </label>
    </div>
    <button type="submit" class="btn pull-right">Create Course</button>
</form>

Are you sure you the fields aren't empty? What's the `var_dump($_POST);`? Also, this is very bad, you should take a look at [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — FirstOne, Dec 30 '15 at 15:54
@FirstOne yes brother - "MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 sec)" thats what "SELECT * FROM `courses`" returned — George Chareas, Dec 30 '15 at 15:56
You are correct, the id field doesn't not need to be dealt with, as it will increment automatically. To further help we will need to see how your HTML form is setup to see where the issue is — bhooks, Dec 30 '15 at 15:56
@FirstOne I will create sessions and I will work on security such brute force attacks or injections later - thats the last thing I care for right now, my priority is to fix the error. — George Chareas, Dec 30 '15 at 15:57
You shouldn't use the `sql-server` tag for questions not about Microsoft SQL Server — Bill Tür stands with Ukraine, Dec 30 '15 at 15:59
The rework is yours :). Please, add the form as requested by @bhooks and the `var_dump($_POST)`. (Sidenote: this unsafe test woudn't work either, since you aren't using quotes for the variables, like so `VALUES ('$name'...)`) — FirstOne, Dec 30 '15 at 15:59
i suspect if your query is capable enough to add single-quotes around data values — techspider, Dec 30 '15 at 15:59
**You don't have `name` attribute in your inputs**... Possible duplicate of [$\_POST is empty after form submission](http://stackoverflow.com/questions/854205/post-is-empty-after-form-submission) — FirstOne, Dec 30 '15 at 16:02
@user2169261 you're right, didnt think that quotes may ruin the code, I changed it and I got a new more error: Error: INSERT INTO courses (name, teacher, description, class, DAYONE, DAYTWO, DAYTHREE, STD1, STD2, STD3) VALUES ($name, $teacher, $description, $class, $dayone, $daytwo, $daythree, $std1, $std2, $std3) Unknown column '$name' in 'field list' — George Chareas, Dec 30 '15 at 16:03
you should concatenate quotes; please post your new query to see the concatenation part — techspider, Dec 30 '15 at 16:04
Made your suggestions and it worked fine - thank you very much for the support. But, I still didnt understand why should I use ' " . before and after the value? — George Chareas, Dec 30 '15 at 16:12

score 0 · Accepted Answer · answered Dec 30 '15 at 16:17

This should help you identify if the issue is POST variables not being received.

Also a little bit more security.

// create an array of all possible input values
$input_array = array('name', 'teacher', 'description', 'class', 'dayone', 'daytwo', 'daythree', 'std1', 'std2', 'std3');

// create an input array to put any received data into for input to the database
$input_array = array();

include_once 'connect.php';   
    // Create connection
    $conn = new mysqli(HOST, USER, PASSWORD, DATABASE);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 


    // loop through the possible input values to check that a post variable has been received for each.. if received escape the data ready for input to the database
    foreach($input_array as $key => $value)
    {
    if(!isset($_POST[$value])) {
    die("no {$value} post variables received");
    }
    $input_array[$value] = mysqli_real_escape_string($conn, $_POST[$value]);
    }


    $sql = "INSERT INTO courses (name, teacher, description, class, DAYONE, DAYTWO, DAYTHREE, STD1, STD2, STD3) VALUES ('{$input_array['name']}', '{$input_array['teacher']}', '{$input_array['description']}', '{$input_array['class']}', '{$input_array['dayone']}', '{$input_array['daytwo']}', '{$input_array['daythree']}', '{$input_array['std1']}', '{$input_array['std2']}', '{$input_array['std3']}')";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

    $conn->close();

score -1 · Answer 2 · answered Dec 30 '15 at 16:04

-1

Try:

$sql = "INSERT INTO courses (name, teacher, description, class, DAYONE, DAYTWO, DAYTHREE, STD1, STD2, STD3) VALUES ('".$name."', '".$teacher."', '".$description."', '".$class."', '".$dayone."', '".$daytwo."', '".$daythree."', '".$std1."', '".$std2."', '".$std3."')";

Also, use:

$name = $conn->real_escape_string($_POST['name']);
//etc

Also add name to your form fields:

<input name="class" type="text" class="form-control" id="class" placeholder="Class Name">

answered Dec 30 '15 at 16:04

NOJ75

63
8

Thanks - success message has been returned. Really thank you. May you please explain to me since I am newbie why should I use ' " . before and after the value? – George Chareas Dec 30 '15 at 16:10
You are most welcome. I am not a pro either but I think it is because the variables need to be turned into strings. Like I said, I am no pro but I had this problem in the past but figured it out with trial and error :) – NOJ75 Dec 30 '15 at 16:12

Insert formfields to SQL - Error

2 Answers2