MYSQL - INSERT error, unknown column in field list

Question

i keep getting the following error from this simple mysql statement and i cant see why. im sure its something obvious.

require_once("connect.php");

$query = mysql_query("SELECT * FROM accounts ORDER BY id DESC LIMIT 1");
$row = mysql_fetch_assoc($query);

$balanceold = $row['balance'];
$difference = $_POST['predec'].".".$_POST['dec'];

$category = $_POST['category'];
$notes = $_POST['notes'];

if(isset($_POST['in'])){
$balancenew = $balanceold + $difference;
$query = mysql_query("INSERT INTO accounts(currentbalance, balancein, category, notes) VALUES (".$balancenew.", ".$difference.", ".$category.", ".$notes.")");  
if($query){
header("Location: budget.php"); 
}
else{
die(mysql_error());
}
}

gives error: Unknown column 'payday' in 'field list'

here is my form code:

<form action=process.php method=post>

&pound;
<input type=text name=predec size=7>
. 
<input type=text name=dec size=4 value=00>
<br />
<select name=category>
<option value=payday>Payday</option>
</select>
<input type=text name=notes size=20>
<input type=submit name=in value=Deposit>
<input type=submit name=out value=Withdraw>
</form>

database table"accounts" contains the following fields:

id, int primary A_I

balancein, decimal 10,2

balanceout, decimal 10,2

current balance, decimal 10,2

category, varchar 50

notes, varchar 255

date, timestamp

...in that order

Your HTML is irrelevant. We'll need to see the structure of your database table. — John Conde, Mar 29 '13 at 19:46
And the code that sets the $balancenew, $difference, $category, $notes variables. — methai, Mar 29 '13 at 19:47
Are you sure this is the query that is giving that error? You have not specified a field named `payday` in the list of fields you are trying to insert values in, so I don't see how you would have this message generated. — Mike Brant, Mar 29 '13 at 19:47
just want to add that I did some thing dumb and was pulling my hair out for something as simple as.... using `"` instead of `'` to wrap around my values... — Populus, Apr 17 '14 at 14:11
**WARNING**: If you're just learning PHP, please, do not learn the obsolete `mysql_query` interface. It's awful and is being removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. Always be absolutely **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will have severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Nov 06 '14 at 21:14

score 14 · Accepted Answer · answered Mar 29 '13 at 19:48

14

try this (enclose each variable inside query with single quota):

mysql_query("INSERT INTO accounts(currentbalance, balancein, category, notes) 
          VALUES ('$balancenew', '$difference', '$category', '$notes')");

Its better to use mysqli or PDO to prevent from SQL injection attack, you could use mysql_real_escape_string() for now:

$balancenew = mysql_real_escape_string($balancenew);

and for other variables.

answered Mar 29 '13 at 19:48

Agreed, unescaped fields can and do cause strange errors like you mentioned. – methai Mar 29 '13 at 19:50
data should be escaped with single quota in order to tell MySQL that it's not a reference to a column. – Mar 29 '13 at 19:55
1

`mysql_real_escape_string` ***will not*** prevent against SQL injection. – Kermit Mar 29 '13 at 19:55
@PolishPrince: its better than nothing – Mar 29 '13 at 19:56
i dont need to worry about sql injection attacks as this isnt a script that is for use of the general public. – Lee Ginger-Ninja McCarthy Mar 29 '13 at 20:00
@LeeGinger-NinjaMcCarthy Rather be safe than sorry. – Kermit Mar 29 '13 at 20:05
@LeeGinger-NinjaMcCarthy Famous last words: "This is just for testing." – tadman Nov 06 '14 at 21:13

score 1 · Answer 2 · edited Apr 16 '14 at 13:38

1

Thats because you have syntax error in your INSERT query. String and Date values are to passed into single quotes and not double quotes in sql. the . or the String concatenation character is also not required. So based on the data you provided it might be

$query = mysql_query("INSERT INTO accounts(currentbalance, balancein, category, notes) 
                      VALUES ($balancenew, $difference, '$category', '$notes')");

edited Apr 16 '14 at 13:38

slavoo

5,798
64
37
39

answered Apr 16 '14 at 13:20

Azan Momin

23
1
3

Yes and no. The big problem here is this is the wrong way to compose a query. – tadman Nov 06 '14 at 21:13

score 0 · Answer 3 · answered Mar 29 '13 at 19:47

0

Basically what sql is telling you that you are referencing a column in your insert that is not defined in the database. Provide your table structure or ensure that the column name is exactly as you defined in the db. HTH.

answered Mar 29 '13 at 19:47

John Brown

189
1
7

score 0 · Answer 4 · answered Apr 16 '14 at 13:23

0

You have missed single inverted commas enclosing $notes and $category I guess. Enclose them in ' and your problem should be solved.

answered Apr 16 '14 at 13:23

gridrivero

1
5

MYSQL - INSERT error, unknown column in field list

4 Answers4