CODEWITHSUNDEEP
phpmysqlsql-insert
Lee Ginger-Ninja McCarthy
Lee Ginger-Ninja McCarthy

Reputation: 184

MYSQL - INSERT error, unknown column in field list

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

Upvotes: 6

Views: 44875

Answers (4)

Azan Momin
Azan Momin

Reputation: 23

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

Upvotes: 1

gridrivero
gridrivero

Reputation: 1

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

Upvotes: 0

user1646111
user1646111

Reputation:

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.

Upvotes: 14

John Brown
John Brown

Reputation: 189

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.

Upvotes: 0

Related Questions