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
– John Conde, Commented Mar 29, 2013 at 19:46
And the code that sets the $balancenew, $difference, $category, $notes variables. — methai
– methai, Commented Mar 29, 2013 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
– Mike Brant, Commented Mar 29, 2013 at 19:47
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. A guide like PHP The Right Way can help explain best practices. Always be absolutely sure your user parameters are properly escaped or you will have severe SQL injection bugs. — tadman
– tadman, Commented Nov 6, 2014 at 21:14

user1646111 · Accepted Answer · 2013-03-29 19:48:19Z

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, 2013 at 19:48

user1646111

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

7 Comments

methai Over a year ago

Agreed, unescaped fields can and do cause strange errors like you mentioned.

user1646111 Over a year ago

data should be escaped with single quota in order to tell MySQL that it's not a reference to a column.

Kermit Over a year ago

mysql_real_escape_string will not prevent against SQL injection.

user1646111 Over a year ago

@PolishPrince: its better than nothing

Lee Ginger-Ninja McCarthy Over a year ago

i dont need to worry about sql injection attacks as this isnt a script that is for use of the general public.

|

slavoo · Accepted Answer · 2014-04-16 13:38:00Z

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, 2014 at 13:38

slavoo

6,11464 gold badges40 silver badges41 bronze badges

answered Apr 16, 2014 at 13:20

Azan Momin

231 silver badge3 bronze badges

1 Comment

tadman Over a year ago

Yes and no. The big problem here is this is the wrong way to compose a query.

John Brown · Accepted Answer · 2013-03-29 19:47:12Z

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, 2013 at 19:47

John Brown

1891 silver badge7 bronze badges

Comments

gridrivero · Accepted Answer · 2014-04-16 13:23:25Z

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, 2014 at 13:23

gridrivero

15 bronze badges

Collectives™ on Stack Overflow

MYSQL - INSERT error, unknown column in field list

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related