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, Mar 12 '15 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, Mar 12 '15 at 16:10

score 4 · Accepted Answer · edited May 23 '17 at 11:43

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 '17 at 11:43

Community

1
1

answered Mar 12 '15 at 15:52

Funk Forty Niner

74,450
15
68
141

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? – Superunknown Mar 12 '15 at 16:14
1

@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 Mar 12 '15 at 16:14
Thanks. Your suggestions have helped but now I'm getting blank fields in where the new entries should be. Also getting this error: "Column count doesn't match value count at row 1" I think this is because of my ID is at column 1 but I don't want to touch column 1. – Superunknown Mar 12 '15 at 16:18
@Superunknown What do you mean you don't want to touch column 1? Your AI'd column will automatically increment; there's nothing you can do to stop that. Either remove the constraint, or make it as UNIQUE with a default value. – Funk Forty Niner Mar 12 '15 at 16:21
My column 1 is my ID column. I want it to auto-increment which it is. But I'm getting blank fields in where the new entries should be. Also getting this error: "Column count doesn't match value count at row 1" – Superunknown Mar 12 '15 at 16:23
I need to know what your column names are called, that go in conjunction with the POSTs you wish to enter. My example `$query= "INSERT INTO $table (column_1, column_2, column_3, column_4)` was just that; an example. You need to replace `column_?` with the names of your columns, which seems to be the reason for the error you're getting. @Superunknown – Funk Forty Niner Mar 12 '15 at 16:26
My columns are id, job_title, job_description, job_location, job_category – Superunknown Mar 12 '15 at 16:27
@Superunknown Ok, so do `$query= "INSERT INTO $table (job_title, job_description, job_location, job_category)` and make sure you've put something inside all the form elements. Plus, you are using error checking as outlined in my answer, right? I have made a few edits, so if your data contains apostrophes etc. that will cause a problem. Reload my answer to see it all. – Funk Forty Niner Mar 12 '15 at 16:29
Yup I'm using error checking like you said: I keep getting "Column count doesn't match value count at row 1" I think my ID column is throwing it off...even though I haven't declared it on the php form – Superunknown Mar 12 '15 at 16:33
@Superunknown Your table may be corrupted. Either rebuild it, or start fresh. Is your ID as an `int` or other? Plus, if it's an `int` and you have a `0` (zero), that would also account for it, because MySQL is unable to increment it. Hard to say for sure, but there's something that's causing it, and those are the only things that come to mind. – Funk Forty Niner Mar 12 '15 at 16:39
Ah, I had to create the ID column when I first created the table, it wasn't automatic...then I had to change it to auto_increment. Could this be the reason? – Superunknown Mar 12 '15 at 16:41
1

@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 Mar 12 '15 at 16:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72861/discussion-between-superunknown-and-fred-ii). – Superunknown Mar 12 '15 at 16:58
1

@Superunknown So, where are we at? Success, or still having trouble? – Funk Forty Niner Mar 12 '15 at 16:58
1

Perfect explanation. Suggestions were spot on. Thanks again! – Superunknown Mar 12 '15 at 17:29
@Superunknown You're very much welcome, I was glad to have helped you and to find the right solution to the problem, *cheers!* – Funk Forty Niner Mar 12 '15 at 17:30

Error querying database in php. Syntax error in variables?

1 Answers1