Unknown Column 1 in field list?

Question

Completely stumped. Getting Error as stated in question title, and no idea why. All my coloumns have text names an there is no reference as far as i know in my code to a column named "1". I have had someone modify this slightly to help me out so maybe there is an issue here i am unaware of.

(string)$insert;
if(is_array($_POST['Year'])){
    foreach($_POST['Year'] as $k=>$v){  
         $insert .= "($_POST['Name'][$k], $_POST['Short'][$k], $_POST['Med'][$k], $_POST['Long'][$k], $_POST['VLong'][$k], $_POST['Extreme'][$k], $_POST['LJump'][$k], $_POST['HJump'][$k], $_POST['Shotputt'][$k], $_POST['Discuss'][$k], $_POST['Javelin'][$k], $_POST['Date'][$k], $_POST['Year'][$k]),"; 
    }
     $insert = substr_replace($insert ,0,-1);
}else{
    $insert .= "($_POST['Name'], $_POST['Short'], $_POST['Med'], $_POST['Long'], $_POST['VLong'], $_POST['Extreme'], $_POST['LJump'], $_POST['HJump'], $_POST['Shotputt'], $_POST['Discuss'], $_POST['Javelin'], $_POST['Date'], $_POST['Year'])"; 
}
$sql="INSERT INTO results_main 
(`Name`, `Short`, `Med`, `Long`, `VLong`, `Extreme`, `LJump`, `HJump`, `Shotputt`, `Discuss`, `Javelin`, `Date`, `Year`)
VALUES 
".$insert;

$result = mysql_query($sql) or die(mysql_error());

// close connection 
mysql_close($conn);

i told you from your previous question what is the result of `echo $sql`? — John Woo, Apr 15 '13 at 14:18
I updated the question, please find below the echo $sql output INSERT INTO results_main (`Name`, `Short`, `Med`, `Long`, `VLong`, `Extreme`, `LJump`, `HJump`, `Shotputt`, `Discuss`, `Javelin`, `Date`, `Year`) VALUES (`1`, ``, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `2013-04-26`, `10`), (`2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2013-04-26`, `10`); Unknown column '1' in 'field list' — Chris Conor Davern, Apr 15 '13 at 14:19
Warning: SQL injection possible. Please use parameterized queries instead of this string concatenating. — Marcel Korpel, Apr 15 '13 at 14:20
I am aware of the possibility of SQL injection, howver this application will be on a private LAN with no external internet access, so its not really a biggy — Chris Conor Davern, Apr 15 '13 at 14:20
Then still you should use something else then `mysql_*` functions, as they are deprecated. — Marcel Korpel, Apr 15 '13 at 14:25
You haven't quoted your result ($insert), the date format for example needs to be in ' quotes -> '2013-04-26' — Royal Bg, Apr 15 '13 at 14:27

score 2 · Answer 1 · answered Apr 15 '13 at 14:26

The reason is because you are wrapping the values with backticks. Values should be wrap with single quotes if they are string literals. MySQL is treating your values as columns because of the backticks around them. Backticks are used for identifiers not string literals. So the rought example would look like this,

INSERT INTO tableName (col1, col2, col3)
VALUES ('hello', 'world', 'stack')

Unknown Column 1 in field list?

1 Answers1