Error querying database in php. Syntax error in variables?

Question

Been over this for the past hour and can't figure out what might be causing the error. Previously I populated the database via a command line in mysql. It worked fine. But I want to add a GUI element for the ordinary user who doesn't understand linux/ line commands etc.

So I decided to add a html form which would post to php. I've done it before on a different form so can't figure out what might be causing the error.

this is the html form:

<form action="insertjob.php" method="post" class="basic-grey">
<label>Job Title:</label><input type="text" name="job_title" id="job_title"/><br>
<label>Job Description:</label><input type="text" name="job_description" id="job_description"/><br>
<label>Job Location:</label><input type="text" name="job_location" id="job_location"/><br>
<label>Job Category:</label><input type="text" name="job_category" id="job_category"/><br>
     <input type="submit" name=submit value="Submit"/>
</form>

then when the user presses submit...it leads to...

insertjob.php

<?php

$user = "root";
$password = "*****";
$host = "********";
$dbase = "jobslist";
$table = "jobs_list";

$job_title= $_POST['jobtitle'];
$job_description= $_POST['jobdescription'];
$job_location= $_POST['joblocation'];
 $job_category= $_POST['jobcategory'];


$dbc= mysqli_connect($host,$user,$password,$dbase)
or die("Unable to select database");

$query= "INSERT INTO $table  ". "VALUES ('$job_title', '$job_description', '$job_location', '$job_category')";

mysqli_query ($dbc, $query)
or die ("Error querying database");

//header('Location: thankyou.html');

mysqli_close($dbc);

?>

It's not a database problem as I've checked. I don't think it's a connection problem either as I've checked the developer tools and there's no errors. I suspect it might be the variables?

If I remove it...my code doesn't run. In fact my other php file has it and the code runs perfectly. — Superunknown
– Superunknown, Commented Mar 12, 2015 at 15:51
You haven't told us what the error is. You need to cut & paste whatever error messages into your question. — Andy Lester
– Andy Lester, Commented Mar 12, 2015 at 16:10

Community · Accepted Answer · 2017-05-23 11:43:24Z

4

Your form elements' name attributes contain underscores between words:

<label>Job Title:</label><input type="text" name="job_title" id="job_title"/><br>
                                                     ^
<label>Job Description:</label><input type="text" name="job_description" id="job_description"/><br>
                                                           ^
<label>Job Location:</label><input type="text" name="job_location" id="job_location"/><br>
                                                        ^
<label>Job Category:</label><input type="text" name="job_category" id="job_category"/><br>
                                                        ^

But your POST variables don't:

$job_title= $_POST['jobtitle'];
                       ^
$job_description= $_POST['jobdescription'];
                             ^
$job_location= $_POST['joblocation'];
                          ^
$job_category= $_POST['jobcategory'];
                          ^

So change those to:

$job_title= $_POST['job_title'];
$job_description= $_POST['job_description'];
$job_location= $_POST['job_location'];
$job_category= $_POST['job_category'];

Using error reporting http://php.net/manual/en/function.error-reporting.php would have signaled "Undefined index..." notices.

$query= "INSERT INTO $table  VALUES ('$job_title', '$job_description', '$job_location', '$job_category')";

mysqli_query ($dbc, $query) or die(mysqli_error($dbc));

Plus, missing quotes around the name attribute for the submit button.

<input type="submit" name=submit value="Submit"/>
                          ^^^^^^

You should also use both stripslashes() and mysqli_real_escape_string(), should your input data contain characters that MySQL may not agree with, such as apostrophes; not to mention to protect from injection (more on this below).

Nota:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

Footnotes:

It is usually best to actually use the column names that are being inserted into.

http://dev.mysql.com/doc/en/insert.html

From their example:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

In your case:

$query= "INSERT INTO $table (job_title, job_description, job_location, job_category) 
         VALUES ('$job_title', '$job_description', '$job_location', '$job_category')";

Another thing is to use mysqli_error() to your advantage, in order to get the real error, should there be a problem somewhere.

$dbc= mysqli_connect($host,$user,$password,$dbase)
 or die("Error " . mysqli_error($dbc));

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

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);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Edit: Check if all fields are set/not empty:

You can also replace isset() by !empty():

if(isset($_POST['job_title']) 
&& isset($_POST['job_description']) 
&& isset($_POST['job_location']) 
&& isset($_POST['job_category'])
)

{

$job_title= $_POST['job_title'];
$job_description= $_POST['job_description'];
$job_location= $_POST['job_location'];
$job_category= $_POST['job_category'];

}

As per our conversation, the solution was to use:

$query= "INSERT INTO $table (id, job_title, job_description, job_location, job_category) 
         VALUES ('','$job_title', '$job_description', '$job_location', '$job_category')";

In regards to the error you were getting, being:

Column count doesn't match value count at row 1

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Mar 12, 2015 at 15:52

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Superunknown Over a year ago

Thank you! You took your time to explain everything to me. Much appreciated! In my database, I have an 'ID' column as well...but that's auto-increment. How do I insert data into table without touching the ID as I want it to be automatic?

Funk Forty Niner Over a year ago

@Superunknown You're welcome. If ID is an AI, you don't need to add it to your query; MySQL will do the rest.

Funk Forty Niner Over a year ago

@Superunknown It could very well be. If there are empty rows for ID, that will explain it, and MySQL has lost count and doesn't know what to do from there. Delete the ones that are empty and make sure there are no empty rows and zeros in that row/column.

Funk Forty Niner Over a year ago

@Superunknown So, where are we at? Success, or still having trouble?

Superunknown Over a year ago

Perfect explanation. Suggestions were spot on. Thanks again!

|

Collectives™ on Stack Overflow

Error querying database in php. Syntax error in variables?

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related