SQL syntax error: unknown column

Question

I'm trying to run this query:

$result = db_query("INSERT INTO `timesheets` (clientid, candid, weekending, department, orderno, basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) VALUES (`$client`, `$cand`, `$week_ending`, `$department`, `$order_no`, `$basic_pay`, `$basic_charge`, `$ot_pay`, `$ot_charge`, `$ot2_pay`, `$ot2_charge`, `$status`, `$hue`, `$huc`)");
    if($result){
        print 'Success! ID of last inserted record is';
    } 
    else {
        die('Error : ' . db_error());
    }

These are the values from the form

    $client = db_quote($_POST['client']);
    $cand = db_quote($_POST['cand']);
    $order_no = db_quote($_POST['order_no']);
    $department = db_quote($_POST['department']);
    $week_ending = db_quote($_POST['week_ending']);
    $basic_pay = db_quote($_POST['basic_pay']);
    $hue = db_quote($_POST['hue']);
    $basic_charge = db_quote($_POST['basic_charge']);
    $huc = db_quote($_POST['huc']);
    $ot_pay = db_quote($_POST['ot1_pay']);
    $ot_charge = db_quote($_POST['ot1_charge']);
    $ot2_pay = db_quote($_POST['ot2_pay']);
    $ot2_charge = db_quote($_POST['ot2_charge']);   
    $status = 'cand';

The value of $client in this instance is "725". The error I am getting is Error : Unknown column ''725'' in 'field list'.

How can this be - the column name in the timesheets table is clientid. I'm trying to put the value 725 into the clientid column. Is there a syntax error somewhere? The error message doesn't seem to make sense?

Does this answer your question? [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Dharman, Mar 04 '22 at 21:42

Abhishek Ginani · Answer 1 · 2015-07-22T10:16:43.043

1

The correct query would be like below one:

"INSERT INTO 'timesheets' (clientid, candid, weekending, department, orderno, basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) 
VALUES ('$client', '$cand', '$week_ending', '$department', '$order_no', '$basic_pay', '$basic_charge', '$ot_pay', '$ot_charge', '$ot2_pay', '$ot2_charge', '$status', '$hue', '$huc')

edited Jul 22 '15 at 10:16

answered Jul 22 '15 at 10:07

Abhishek Ginani

4,511
4
23
35

That would give a syntax error as the variable `$status` is a string, and would need to be treated as such in the values section of the statement – gabe3886 Jul 22 '15 at 10:13
@gabe3886 I'm now getting that error! Error : Unknown column 'cand' in 'field list'. Using '$status' fixed it – Ian Butler Jul 22 '15 at 10:17
Have you declared 'cand' ? – Abhishek Ginani Jul 22 '15 at 10:20
I've declared `$status = "cand";`. So in VALUES I need to wrap strings in quotes like so `'$status'`. Why does it work for the rest without the quotes? Isn't `$department` a string also? – Ian Butler Jul 22 '15 at 10:24
1

Yeah, You need to wrap the strings with quotes if column type is of varchar in table. – Abhishek Ginani Jul 22 '15 at 10:26
I'm using this which works `VALUES ($client, $cand, $week_ending, $department, $order_no, $basic_pay, $basic_charge, $ot_pay, $ot_charge, $ot2_pay, $ot2_charge, '$status', $hue, $huc)` – Ian Butler Jul 22 '15 at 10:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/83957/discussion-between-code-monk-and-ian-butler). – Abhishek Ginani Jul 22 '15 at 10:29
1

@IanButler The department is most likely a string, but the call to `db_quote` is potentially correctly wrapping the strings ahead of their usage – gabe3886 Jul 22 '15 at 10:34
@gabe3886 Well spotted! Yes "cand" isn't being passed into `db_quote()`. Thanks for your help – Ian Butler Jul 22 '15 at 10:40

score 1 · Accepted Answer · answered Jul 22 '15 at 10:10

When setting the values in MySQL you can't use the backtick character to denote a field, you'll need to use either ' or " to enclose your variables.

Use of a back-tick is for naming a field. It's a quirk of MySQL.

Your query line should be:

$result = db_query("INSERT INTO `timesheets` (clientid, candid, weekending, department, orderno, basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) VALUES ('$client', '$cand', '$week_ending', '$department', '$order_no', '$basic_pay', '$basic_charge', '$ot_pay', '$ot_charge', '$ot2_pay', '$ot2_charge', '$status', '$hue', '$huc')");

I'm using this which works VALUES ($client, $cand, $week_ending, $department, $order_no, $basic_pay, $basic_charge, $ot_pay, $ot_charge, $ot2_pay, $ot2_charge, '$status', $hue, $huc) The column types are all varchar of varying lengths. Why then can I leave $department without quotes but doing so with $status gives an error? Isn't $department a string also? — Ian Butler, Jul 22 '15 at 10:36

Mukesh Kalgude · Answer 3 · 2015-07-22T10:24:22.600

0

Try this way

$result = db_query("INSERT INTO `timesheets` (clientid, candid, weekending, department, orderno, basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) VALUES ('".$client."', '".$cand."', '".$week_ending."', '".$department."', '".$order_no."', '".$basic_pay."', '".$basic_charge."', '".$ot_pay."', '".$ot_charge."', '".$ot2_pay."', '".$ot2_charge."', '".$status."', '".$hue."', '".$huc."')");

edited Jul 22 '15 at 10:24

answered Jul 22 '15 at 10:12

Mukesh Kalgude

4,814
2
17
32

Because the values aren't quoted in this SQL, any strings will cause an error – gabe3886 Jul 22 '15 at 10:20

score 0 · Answer 4 · answered Jul 22 '15 at 10:18

Please pay attention to the quotation marks during the creation of the query.

The INSERT statement should something like that:

INSERT INTO table_name (col_name_1, col_name_2, col_name_3) VALUES('$param_1', '$param_2', '$param_3')

Note that the table_name is written without quotation marks, while each value is delimited by a single quotes.

You can find the official documentation here and a full example here.

By the way, the query for your code should be something like that:

 $sqlQuery = "INSERT INTO timesheets (clientid, candid, weekending, department, orderno,basicpay, basiccharge, otpay, otcharge, ot2pay, ot2charge, status, hue, huc) VALUES ('$client', '$cand', '$week_ending', '$department', '$order_no', '$basic_pay', '$basic_charge', '$ot_pay', '$ot_charge', '$ot2_pay', '$ot2_charge', '$status', '$hue', '$huc')";

In the question, the table_name part isn't in quotations, it's in back ticks with is completely valid and in some places encouraged — gabe3886, Jul 22 '15 at 10:21

SQL syntax error: unknown column

4 Answers4