unknown column in 'field list'

[FrankA]

Hi, I've never posted on here, so I'll probably accidentally break a few posting rules. Sorry in advance! I'm not completely new to php/mysql programming, but I think this may be an easy fix. I'm working with existing code that I didn't write, but it handles an online catalogue that my job put up. They wanted to ad a new category, which I determined should be as easy as adding to the associative array in the file I found handling the product categories. I added to it using exactly the same parts as the rest of the array and plugging in the new information. It generated the form like it was supposed to, but when I enter in information to test it it prints the error "Unknown column 'chainguides' in 'field list'" chainguides being the name of the new category I was trying to ad. I've (sadly) spent hours (luckily not all at once) trying to find the fix other people have had, but came up with nothing that worked. For the sake of not having too look at many, many lines of coding, I'll post in the code that handles the mysql query and a small part of the array.

 

//Prepare the arrays we've just created to be included in the SQL INSERT
		$field_string = join(",",$fields);
		$value_string = join(",",$values);
                        //I've tried echoing these variables and they output the form data

		//Create the SQL INSERT and execute the query
		$sql = "INSERT INTO $this->x_table_name ($field_string)
				VALUES	($value_string)";
		mysql_query($sql) or die(mysql_error());

 

I saw that it should be typed as "INSERT INTO `$this->x_table_name` (`$field_string`) VALUES ('$value_string')"; but that returned a syntax error. I tried just putting the single quotes around $value_string without the tildes on the column name and table name, but that also returned a syntax error. This code works exactly how it should for every other category except for the one I tried to add and works when I comment out the extra part of the associative array that I put in.

 

//this one works fine
'pants' => array( 'import_id'=>'30',
		'name'=>'Shorts / Pants',
		'tbl_name'=>'pants',
		'tbl_id'=>'pants_id',
		'road'=>false,
		'mtn'=>true,
		'fields'=>array(
				  0 => array('name'=>'Features',
							'db_name'=>'features',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
				  1 => array('name'=>'Model',
							'db_name'=>'model',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>'Please enter a model number.'
							),
				  2 => array('name'=>'U.S. MSRP',
							'db_name'=>'price',
							'input_size'=>'7',
							'type'=>'text',
							'required'=>'Please enter a price (numbers and decimal point only).'
							),
				  3 => array('name'=>'Image',
							'db_name'=>'img_filename',
							'input_size'=>'0',
							'type'=>'file',
							'required'=>''
							),
				  4 => array('name'=>'More Info URL',
							'db_name'=>'url',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
				  5 => array('name'=>'Category',
							'db_name'=>'category',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							)
				  )
),
       //this is the one I made based on the others that worked
'chainguides' => array( 'import_id'=>'31',
		'name'=>'Chain Guides',
		'tbl_name'=>'chainguides',
		'tbl_id'=>'chainguides_id',
		'road'=>false,
		'mtn'=>true,
		'fields'=>array(
				  0 => array('name'=>'Year/Model',
							'db_name'=>'model',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>'Please enter a model number.'
							),
				  1 => array('name'=>'U.S. MSRP',
							'db_name'=>'price',
							'input_size'=>'7',
							'type'=>'text',
							'required'=>'Please enter a price (numbers and decimal point only).'
							),
			      2 => array('name'=>'Features',
							'db_name'=>'features',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
			      3 => array('name'=>'Category',
							'db_name'=>'category',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
				  4 => array('name'=>'Mount Type',
							'db_name'=>'mount_type',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
				  5 => array('name'=>'Number of Chain Rings',
							'db_name'=>'chain_rings',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
				  6 => array('name'=>'Backplate Material',
							'db_name'=>'backplate_material',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							),
                      7 => array('name'=>'Color',
                                'db_name'=>'color',
                                'input_size'=>'200',
                                'type'=>'text',
                                'required'=>''
                                ),
                      8 => array('name'=>'Weight',
                                'db_name'=>'weight',
                                'input_size'=>'200',
                                'type'=>'text',
                                'required'=>''
                                ),
				  9 => array('name'=>'Image',
							'db_name'=>'img_filename',
							'input_size'=>'0',
							'type'=>'file',
							'required'=>''
							),
			      10 => array('name'=>'URL',
							'db_name'=>'url',
							'input_size'=>'200',
							'type'=>'text',
							'required'=>''
							)
				  )
)
);

 

I didn't put in the full array because it is very long and I put in the category directly before the one I added, called pants, to show how it's structured and I put in the extra fields like the other ones that were longer. As I said before, the form generates perfectly and the error is when I hit submit. The strangest part about the whole thing is when I check the table using phpmyadmin all of the data actually gets inserted correctly into the chainguides table, but won't show up when I look at the test profile I made for the site and can't be searched for on the main catalogue page. Also, there isn't a field in the table called chainguides because I didn't intend there to be a field in the table called that, so it's strange that it's trying to put that in as a field when it's only the table's name, unless I'm misunderstanding the error.

 

Thank you very much for taking a look!

-Frank

[kickstart]

Hi

 

Where do "$fields" and "$values" arrays come from?

 

Can you echo out $sql and see what it is set to.

 

All the best

 

Keith

[FrankA]

Hey Keith,

The $fields and $values arrays are in from here:

 

//Initialize variables for processing of the $required and $optional arrays
	$fields = array();
	$values = array();

	//Loop through the required fields, confirming that they are present in the $data array and not blank or otherwise invalid
	foreach ($required as $var)
	{
		if (make_clean($data[$var]) !== "NULL")
		{
			$fields[] = "`$var`";

			//Don't escape the mysql Now() command
			if ($data[$var] == 'Now()')
			{
				$values[] = $data[$var];
			}
			else
			{
				$values[] = make_clean($data[$var]);
			}
		}
		else
		{
			$error = "The required field '$var' was not found. Please enter valid information in this field.";
		}
	}

	//Add the optional fields
	foreach ($optional as $var)
	{
		if (make_clean($data[$var]) !== "NULL")
		{
			$fields[] = "`$var`";

			//Don't escape the mysql Now() command
			if ($data[$var] == 'Now()')
			{
				$values[] = $data[$var];
			}
			else
			{
				$values[] = make_clean($data[$var]);
			}
		}
	}

Also I found this in the comments /* @param array $data

  * An associative array containing all the information needed to create a record

  * The keys of this array must match the names of the db fields they correspond to. */ I don't know if you needed that though.

 

When I echo $sql it says:

 

INSERT INTO chainguides (`model`,`price`,`company_id`,`date_added`,`features`,`category`,`mount_type`,`chain_rings`,`backplate_material`,`color`,`weight`) VALUES ('778','77','264',Now(),'none','none','none','200','sand','green','500')Unknown column 'chainguides' in 'field list'

 

All of the values are what I typed in the form when I tested.

Thanks for taking a look!

-Frank

[kickstart]

Hi

 

It appears that the code expects there to be a seperate table for each category (pretty poor database design), so you are trying to insert to a table called chainguides, which I suspect doesn't exist.

 

All the best

 

Keith

[FrankA]

Hey,

  Yeah, each category is its own table. I don't know very much about database design yet, so I'm glad I haven't applied these ideas to my own projects! I'm posting three screen caps: The first one is the database itself, the second one is a screen cap of a table I didn't make, but works, and the third is the chainguides table I've added. Perhaps I didn't structure it correctly? I really appreciate the help!

 

-Frank

 

[attachment deleted by admin]

[kickstart]

Hi

 

Mmmm, nothing obvious. It sounds like the code is pretty complex. The previous fragment loops though 2 arrays ($required and $optional), but not sure where these are set up (I would guess they are derived from the complex array).

 

To be honest I think I would need to see most of the source to find the problem.

 

However, first stage, if you take the SQL insert statement that has been output and try executing it in phpMyAdmin does it give the same error?

 

All the best

 

Keith

[FrankA]

Hey,

    Using the code in the sql tab added just fine. Here's the whole block of code that handles the new entry:

 

/**
  * Enters a new record
  *
  * @param array $data
  * 	An associative array containing all the information needed to create a record
  * 	The keys of this array must match the names of the db fields they coorespond to.
  *
  * @param array $required
  * 	An array containing the required fields.
  *
  * @param array $optional
  * 	An array containing the optional fields.
  *
  * @param optional array $file_data
  * 	If this record includes a file, this contains the $_FILES superglobal
  *
  * @return int $id
  * 	The id of the newly created record
  *
  * @return string
  * 	Returns an error message on bad data or if a required field from is missing in the $data array
  *
  */
function Add($data, $required, $optional, $file_data = array())
{
	//Bail on bad data
	if (!is_array($data) || !is_array($required) || !is_array($optional))
	{
		return "Invalid input.";
	}

	//Set the error variable
	$error = 0;

	//Initialize variables for processing of the $required and $optional arrays
	$fields = array();
	$values = array();

	//Loop through the required fields, confirming that they are present in the $data array and not blank or otherwise invalid
	foreach ($required as $var)
	{
		if (make_clean($data[$var]) !== "NULL")
		{
			$fields[] = "`$var`";

			//Don't escape the mysql Now() command
			if ($data[$var] == 'Now()')
			{
				$values[] = $data[$var];
			}
			else
			{
				$values[] = make_clean($data[$var]);
			}
		}
		else
		{
			$error = "The required field '$var' was not found. Please enter valid information in this field.";
		}
	}

	//Add the optional fields
	foreach ($optional as $var)
	{
		if (make_clean($data[$var]) !== "NULL")
		{
			$fields[] = "`$var`";

			//Don't escape the mysql Now() command
			if ($data[$var] == 'Now()')
			{
				$values[] = $data[$var];
			}
			else
			{
				$values[] = make_clean($data[$var]);
			}
		}
	}

	//Check to see if we have all the required variables
	if ($error === 0)
	{
		//Prepare the arrays we've just created to be included in the SQL INSERT
		$field_string = join(",",$fields);
		$value_string = join(",",$values);

		//echo $sql;
		//Create the SQL INSERT and execute the query
		$sql = "INSERT INTO $this->x_table_name ($field_string)
				VALUES	($value_string)";
		mysql_query($sql) or die(mysql_error());

		//Return the id for this newly created record.
		$new_fileid =  mysql_insert_id();

		foreach ($file_data as $current_file)
		{
			//print_r($current_file);
			if ($current_file['error'] === 0)
			{
				$file_result = upload($current_file, $this->x_table_name, $this->x_table_id, 'img_filename', UPLOADS_DIR."company_".$_SESSION['company_id']."/",$new_fileid);
				if ($file_result!== true)
				{
					$sql = "DELETE FROM $this->x_table_name WHERE `$this->x_table_id` = '$new_fileid'";
					mysql_query($sql) or die (mysql_error());
					return "Error on file ".substr($current_file['name'],3,1).":<br />".$file_result;
				}
			}
			else if ($current_file['error'] === 1)
			{
				return "Filesize exceeds server limit. (5 MB)";
			}
			else if ($current_file['error'] !== 4 && $current_file['error'] > 0)
			{
				return "There is a problem with this file. It cannot be uploaded.";
			}
		}

		return $new_fileid;
	}
	else
	{
		return $error;
	}
}

 

Also, this is the code from the form for adding the item.

 

$db = new DB();
$error = 0;
if (!is_null($_POST['cat']))
{
if (!is_numeric($_POST['price']))
{
	$error = "Please enter a valid price.";
	$data = $_POST;
}
else
{
	$check_road = 0;
	if ($_GET['guide'] == "road")
	{
		$check_road = 1;
	}
	else
	{
		$check_road = 0;
	}

	//echo $_SESSION['max_products'];
	if (getProductCount($_SESSION['company_id'])+1 > $_SESSION['max_products'])
	{
		$error = "You have exceeded the allowed number of products for your company.<br />Please contact H3 to upgrade your account.<br />If you have just upgraded your account, please logout and login again to complete the upgrade.";
		$data = $_POST;
	}
	else if (checkForModel($_POST['model'],$RB_products[$_POST['cat']]['tbl_name'],$RB_products[$_POST['cat']]['tbl_id'],$_SESSION['company_id'],0,$check_road))
	{
		$error = "A product with this model number is already in the system.<br />Please choose a different model number.";
		$data = $_POST;
	}
	else
	{
		//print_r($_POST);
		$req = array();
		$opt = array();
		foreach ($RB_products[$_POST['cat']]['fields'] as $input_field)
		{
			if ($input_field['type'] == 'file')
			{
				continue;
			}
			if ($input_field['required'] != '')
			{
				$req[] = $input_field['db_name'];
			}
			else
			{
				$opt[] = $input_field['db_name'];
			}
		}
		if ($RB_products[$_POST['cat']]['road'] === true && $RB_products[$_POST['cat']]['mtn'] === true)
		{
			$req[] = 'isroad';
			if ($_GET['guide'] == "road")
			{
				$_POST['isroad'] = "1";
			}
			else
			{
				$_POST['isroad'] = "0";
			}
		}
		$req[] = 'company_id';
		$req[] = 'date_added';
		$_POST['company_id'] = $_SESSION['company_id'];
		$_POST['date_added'] = 'Now()';

		//print_r($req);
		//print_r($opt);

		$db->Reset();
		$db->SetTable($RB_products[$_POST['cat']]['tbl_name'],$RB_products[$_POST['cat']]['tbl_id']);
		$error = $db->Add($_POST,$req,$opt,$_FILES);

		if (!is_numeric($error))
		{
			$data = $_POST;
		}
		else
		{
			incrementProdCount($RB_products[$_POST['cat']]['tbl_name'],$_SESSION['company_id']);

			$comp = new DB();
			$comp->SetTable('companies', 'company_id');
			$comp->Load($_SESSION['company_id']);
			$email_body = "The Buyer's Guide company, ".$comp->Get('name').", has added a new product, bringing their product count to ".getProductCount($_SESSION['company_id'],false).".";
			//USER::SendEmail("New Product Added by ".$comp->Get('name'), $email_body,"christine@h3publications.com","admin@bicyclebuyersguide.com","Buyer's Guide Product Tracking");
			USER::SendEmail("New Product Added by ".$comp->Get('name'), $email_body,"neilv@h3publications.com","admin@bicyclebuyersguide.com","Buyer's Guide Product Tracking");
		}
	}
}
}

 

And I found the upload function too...

 

function upload($file_data, $db_table, $db_id, $db_field, $upload_dir, $update_id)
{
if (!class_exists('file_upload'))
{
	include(SERVER_ROOT."includes/upload_class.php");
}

$max_size = 1024*15; // the max. size for uploading

$my_upload = new file_upload;

$my_upload->upload_dir = $upload_dir;
$my_upload->extensions = array(".jpg", ".jpeg");
$my_upload->max_length_filename = 50;
$my_upload->rename_file = true;

$my_upload->the_temp_file = $file_data['tmp_name'];
$my_upload->the_file =  $file_data['name'];
$my_upload->http_error =  $file_data['error'];
$my_upload->replace = false;
$my_upload->do_filename_check = true; // use this boolean to check for a valid filename
if ($my_upload->upload())
{
	$final_name = $my_upload->file_copy;
	$full_path = $my_upload->upload_dir.$my_upload->file_copy;
	resizeWidth($full_path,$my_upload->upload_dir."thumb_".$final_name,100);

	//$sql = "SELECT `$db_field` FROM `$db_table` WHERE `$db_id` = '$update_id'";
	//$result = mysql_query($sql) or die (mysql_error());
	//$row = mysql_fetch_assoc($result);
	//deleteImg($row[$db_field], $_SESSION['company_id']);

	$sql = "UPDATE `$db_table` SET `$db_field` = '$final_name' WHERE `$db_id` = '$update_id'";
	//echo $sql;
	mysql_query($sql) or die (mysql_error());

	return true;
}
else
{
	return $my_upload->show_error_string();
}
}

 

A lot of code :X Thanks again for looking!

 

-Frank

[kickstart]

Hi

 

Can't see anything there (would need the call to add, including the set ups of the arrays passed).

 

However, that the SQL works fine in phpmyadmin suggests that the insert SQL is fine. I am wondering if there is some SQL elsewhere that is putting out the error.

 

I would suggest finding all the or die statements and add something to each so that they are unique (ie  or die ('1 '.mysql_error()); , and have a different number for each one).

 

All the best

 

Keith