unknown column in 'field list'...

[FrankA]

Hi!

I'm new to this site and I have this posted on the mysql help section too, so I'm probably breaking a couple rules posting it here too. Sorry in advance! I'm not completely new to php/mysql programming, but I think this could 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 add 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 add. 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. 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'

 

//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

 

 

[bundyxc]

$sql = "INSERT INTO `" . $this->x_table_name . "` (`" . $field_string . "`) VALUES ('" . $value_string . "')";

 

Give that a shot.

[FrankA]

Thanks man, I'll give that a shot. Also here's the code with the php tags around the code, thanks for telling me about that.

 

//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());

 

and

 

//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'=>''
                        )
                 )
   )
);

[FrankA]

Ah yeah, I've had this error before when I try putting the tildes and single quotes. 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 'model`,`price`,`company_id`,`date_added`,`features`,`category`,`mount_type`,`cha' at line 1

 

It conflicts with the tildes and stuff that are already in another part of the code to add it in. These are the foreach loops that handle it:

 

//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]);
			}
		}
	}