1064 - You have an error in your SQL syntax

[hawken]

When I go to edit a product, I get the following error:

 

1064 - 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 'Resource id #50, pd.extra_value1 from products p, products_description pd where ' at line 1

 

Resource id #50, pd.extra_value1 from products p, products_description pd where p.products_id = '1553' and p.products_id = pd.products_id and pd.language_id = '1'

 

[TEP STOP]

 

 

The last thing I did was install Header Tags SEO, and everything else works fine but this one thing. Anyone have any ideas? I read the official support thread and could not figure out anything.

[♥geoffreywalton]

There is no such field as Resource id #50 in the products or product_description table.

 

Can you post the sql that generates the error?

 

G

[hawken]

Thanks, here's the code:

 

-- --------------------------------------------------------
--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
 `products_id` int(11) NOT NULL auto_increment,
 `products_quantity` int(4) NOT NULL default '0',
 `products_model` varchar(12) default NULL,
 `products_image` varchar(64) default NULL,
 `products_price` decimal(15,4) NOT NULL default '0.0000',
 `products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
 `products_last_modified` datetime default NULL,
 `products_date_available` datetime default NULL,
 `products_weight` decimal(5,2) NOT NULL default '0.00',
 `products_status` tinyint(1) NOT NULL default '0',
 `products_tax_class_id` int(11) NOT NULL default '0',
 `manufacturers_id` int(11) default NULL,
 `products_ordered` int(11) NOT NULL default '0',
 PRIMARY KEY  (`products_id`),
 KEY `idx_products_date_added` (`products_date_added`),
 KEY `idx_products_model` (`products_model`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1553 ;
--
-- Dumping data for table `products`
--

 

-- --------------------------------------------------------
--
-- Table structure for table `products_description`
--
CREATE TABLE IF NOT EXISTS `products_description` (
 `products_id` int(11) NOT NULL auto_increment,
 `language_id` int(11) NOT NULL default '1',
 `products_name` varchar(64) NOT NULL default '',
 `products_description` text,
 `products_url` varchar(255) default NULL,
 `products_viewed` int(5) default '0',
 `products_head_title_tag` varchar(80) default NULL,
 `products_head_desc_tag` longtext,
 `products_head_keywords_tag` longtext,
 `extra_value1` varchar(160) default NULL,
 PRIMARY KEY  (`products_id`,`language_id`),
 KEY `products_name` (`products_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1553 ;
--
-- Dumping data for table `products_description`
--

[NodsDorf]

The resource id error may have something to do with an array that is incorrectly referenced.

 

That said, I don't think Geoffrey was asking for the SQL insert statements. Rather, the code inside the admin/categories.php that generates the error. Try searching that file for code that matches the error (minus the error part).

 

Just my guess that is you missed a closing tag on one the the modifications you recently did.

[hawken]

I figured out that it's two conflicting mods. Here's the instructions for both that affect the same line: (Is there a way to combine these steps so the final result works with both? Thanks!

 

FIND (around line 448):

 

if (isset($HTTP_GET_VARS['pID']) && empty($HTTP_POST_VARS)) {

$product_query = tep_db_query("select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

REPLACE WITH:

 

if (isset($HTTP_GET_VARS['pID']) && empty($HTTP_POST_VARS)) {

/*** Begin Header Tags SEO ***/

$product_query = tep_db_query("select pd.products_name, pd.products_description, pd.products_head_title_tag, pd.products_head_desc_tag, pd.products_head_keywords_tag, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

/*** End Header Tags SEO ***/

 

15f) Around line 380 find the line that reads:

 

$product_query = tep_db_query("select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

and REPLACE it with the following:

 

// begin Extra Product Fields

$query = "select pd.products_name, pd.products_description, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id, p.products_type";

foreach ($xfields as $f) {

$query .= ', pd.' . $f;

}

$query .= " from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";

$product_query = tep_db_query($query);

// end Extra Product Fields

[NodsDorf]

I'll get you the correct code to merge those 2 just a few moments if somebody doesn't do it before me.

[NodsDorf]

// begin Extra Product Fields & Header Tags Modification
$query = "select pd.products_name, pd.products_description, pd.products_head_title_tag, pd.products_head_desc_tag, pd.products_head_keywords_tag, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id, p.products_type";
foreach ($xfields as $f) {
$query .= ', pd.' . $f;
}
$query .= " from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = '" . (int)$HTTP_GET_VARS['pID'] . "' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'";
$product_query = tep_db_query($query);
// end Extra Product Fields

 

Let me know if that fixes it.

[hawken]

Sorry for my delay, I was without internet the last couple days. Thanks for your help, but unfortunately it doesn't work and when I just tried that I get a new error message:

 

1054 - Unknown column 'p.products_type' in 'field list'

 

select pd.products_name, pd.products_description, pd.products_head_title_tag, pd.products_head_desc_tag, pd.products_head_keywords_tag, pd.products_url, p.products_id, p.products_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.products_date_added, p.products_last_modified, date_format(p.products_date_available, '%Y-%m-%d') as products_date_available, p.products_status, p.products_tax_class_id, p.manufacturers_id, p.products_type, pd.extra_value1 from products p, products_description pd where p.products_id = '1491' and p.products_id = pd.products_id and pd.language_id = '1'

 

[TEP STOP]

[NodsDorf]

Hi Don, that error should tell you exactly what the issue is. Your table products doesn't have a products_type column. Check your "Extra Products Field" instructions for the insert SQL and see if it instructs you to create that field. Then check your oscommerce database and the products table to see if it exist.

 

If not you can do it pretty quick in your phpMyadmin. With a SQL command.

 

Alter table products add products_type varchar(50)

 

Hope that helps

[hawken]

Thank you, you were right! I must have missed a step in the mysql and your fix worked! Cheers.