mysql error (newbie)

[doofy]

This will be a stupid\easy one, but I'm just too new to decypher what this mysql error is referring to. Any assistance at all would be appreciated.

 

Thank you in advance.

 

"Insert Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ID', 'username', 'goal1', 'goaldate') VALUES ('', '', 'goalfield', '')' at line 1"

 

...

if ($_SERVER['REQUEST_METHOD'] == 'POST')
{

$result = mysql_query(" SELECT * from login WHERE username = '$username' ") or die(mysql_error());
$row = mysql_fetch_array($result);
$ID = $row['ID'];
$username = $row['username'];
$NumGoals = $row['NumGoals'];
$NumGoals++;
$newgoal = mysql_real_escape_string(htmlspecialchars($_POST['newgoal']));
$newgoalid = "goal";
$newgoalid .= $NumGoals;
$newgoaldate = mysql_real_escape_string(htmlspecialchars($_POST['goaldate']));
// Verify if last column name already exists in table from databasename
$fields = mysql_list_fields('databasename', 'goals');
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {$field_array[] = mysql_field_name($fields, $i);}
if (!in_array('$newgoalid', '$newgoaldate', $field_array))
{
// IF non-existant column ALTER table to add goal columns
if (empty($field_array))
{
$result = mysql_db_query("ALTER TABLE 'goals'
	 ADD '$newgoalid' TEXT NOT NULL,
 ADD '$newgoaldate' DATE") or die("Alter Error: ".mysql_error());
}
}
// INSERT data INTO new column's fields to add record (newgoal#, newgoaldate)

$SQL = " INSERT INTO goals ('ID', 'username', '".$newgoalid."', 'goaldate') VALUES ('".$ID."', '".$username."', '".$newgoal."', '".$goaldate."')";
$result = mysql_query($SQL) or die("Insert Error: ".mysql_error());
// Go back succesfully to same page
header ("Location: test.php");
}

...

Edited February 1, 2013 by doofy

[doofy]

Well I figured out what was wrong for one part as to why it wasn't populating the ID or username and now I get a new error:

 

"Insert Error: Unknown column 'goal1' in 'field list'"

 

<?
error_reporting(ALL);
session_start();
if (!(isset($_SESSION['login']) && $_SESSION['login'] != '')) {
header ("Location: index.php");
}
$username = $_SESSION['username'];
require("connect.php");
if ($_SERVER['REQUEST_METHOD'] == 'POST')
{

$result = mysql_query(" SELECT * from login WHERE username = '$username' ") or die(mysql_error());
$row = mysql_fetch_array($result);
$ID = $row['ID'];
$NumGoals = $row['NumGoals'];
$NumGoals++;
$newgoal = mysql_real_escape_string(htmlspecialchars($_POST['newgoal']));
$newgoalid = "goal";
$newgoalid .= $NumGoals;
$newgoaldate = mysql_real_escape_string(htmlspecialchars($_POST['goaldate']));
// Verify if last column name already exists in table
$fields = mysql_list_fields('doofyd5_checkbucket', 'goals');
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {$field_array[] = mysql_field_name($fields, $i);}
if (!in_array('$newgoalid', '$newgoaldate', $field_array))
{
// IF non-existant column ALTER table to add goal columns
if (empty($field_array))
 {
 $result = mysql_db_query("ALTER TABLE 'goals'
	   ADD `".$newgoalid."` TEXT NOT NULL,
    ADD '".$newgoaldate."' DATE") or die("Alter Error: ".mysql_error());
 }
}
// INSERT data INTO new column's fields to add record (newgoal#, newgoaldate)

$SQL = " INSERT INTO `goals` (`ID`, `username`, `".$newgoalid."`, `goaldate`) VALUES (`".$ID."`, `".$username."`, `".$newgoal."`, `".$newgoaldate."`) ";
$result = mysql_query($SQL) or die("Insert Error: ".mysql_error());
// Go back succesfully to profile.php
header ("Location: test.php");
}
?>

Edited February 1, 2013 by doofy

[fenway]

So nice of you to post the solution.