Any idea on how to fix this sql issue?

[themilkman]

....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 'from products p left join manufacturers m using(manufacturers_id

 

from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%test%' or p.products_model like '%test%' or m.manufacturers_name like '%test%') ) order by pd.products_name limit 0, 20

 

[TEP STOP]

 

 

Basically I tried to search for an item and got the above error message, any idea how to fix it?

 

Just to add, when searching for a non-existent product then no error message is shown - it just simply says that no psuch products exists. However, when searching for an item that does exist then the error message is thrown.

 

Also I have noticed that for the contrib that I had installed that was working to show a sold out button now no longer works - all itema appear as sold out even though they are instock with positive quantities.

 

 

Many Thanls

[themilkman]

Can anyone point me on how I should debug this the? SHould I install PHPmyadmin??

[themilkman]

I have reverted to a backup veriosn of my website this solved the issue with the sold out status however the error on search still appears.

 

Any help here would be really appreciated.

 

Thanks

[Jan Zonjee]

....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 'from products p left join manufacturers m using(manufacturers_id)

There is nothing wrong here if you ask me. It would throw an error if there is no longer a manufacturers_id in the table products though. Did you check that?

[themilkman]

I have not done anything to the db table, for the product when I added it I did not set any manufacturuer for it.

[Jan Zonjee]

I have not done anything to the db table, for the product when I added it I did not set any manufacturuer for it.

A. That is not an answer to my question: Is there a manufacturers_id in the table products?

B. You said you added a contribution, that might have done something to the database table.

[themilkman]

Hi Jan - I have done a roll back instead and will add back the functionality that I think has caused a problem later.

 

However to answer your Q:

 

A. That is not an answer to my question: Is there a manufacturers_id in the table products?

 

I do not have phpMyAdmin installed to do the query to check this or is there another way to check this?

[Jan Zonjee]

I do not have phpMyAdmin installed to do the query to check this or is there another way to check this?

Depends on how big your backup is (if it is very big, a text editor can be slow), but if you make a new one you will find the create table statement in there for that table (tables are added in the backup in alphabetical order). Something like this (this is from oscommerce.sql in the install, but it looks very similar, just lower case I think):

DROP TABLE IF EXISTS products;
CREATE TABLE products (
 products_id int NOT NULL auto_increment,
 products_quantity int(4) NOT NULL,
 products_model varchar(12),
 products_image varchar(64),
 products_price decimal(15,4) NOT NULL,
 products_date_added datetime NOT NULL,
 products_last_modified datetime,
 products_date_available datetime,
 products_weight decimal(5,2) NOT NULL,
 products_status tinyint(1) NOT NULL,
 products_tax_class_id int NOT NULL,
 manufacturers_id int NULL,
 products_ordered int NOT NULL default '0',
 PRIMARY KEY (products_id),
 KEY idx_products_date_added (products_date_added)
);

So open it with a text editor and you can see what the structure of that table was when you made the backup.

[themilkman]

This is my mistake I have not been making back ups like this just of all the php files to and from my host account using ftp.

 

I will make sure that I make backups the correct way through osCommerce.

 

This would mean that there is nothing wrong with the DB since using previous versions of my website files they do not cause this problem.

[themilkman]

create table products (

products_id int(11) not null auto_increment,

products_quantity int(4) default '0' not null ,

products_model varchar(12) ,

products_image varchar(64) ,

products_price decimal(15,4) default '0.0000' not null ,

products_date_added datetime default '0000-00-00 00:00:00' not null ,

products_last_modified datetime ,

products_date_available datetime ,

products_weight decimal(5,2) default '0.00' not null ,

products_status tinyint(1) default '0' not null ,

products_tax_class_id int(11) default '0' not null ,

manufacturers_id int(11) ,

products_ordered int(11) default '0' not null ,

PRIMARY KEY (products_id),

KEY idx_products_model (products_model),

KEY idx_products_date_added (products_date_added)

);

 

insert into products (products_id, products_quantity, products_model, products_image, products_price, products_date_added, products_last_modified, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id, products_ordered)

 

I made a back up and got the above.

[Jan Zonjee]

I made a back up and got the above.

That looks fine. What about the table manufacturer?

[themilkman]

create table manufacturers (

manufacturers_id int(11) not null auto_increment,

manufacturers_name varchar(32) not null ,

manufacturers_image varchar(64) ,

date_added datetime ,

last_modified datetime ,

PRIMARY KEY (manufacturers_id),

KEY IDX_MANUFACTURERS_NAME (manufacturers_name)

);

 

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('1', 'Matrox', 'manufacturer_matrox.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('2', 'Microsoft', 'manufacturer_microsoft.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('3', 'Warner', 'manufacturer_warner.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('4', 'Fox', 'manufacturer_fox.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('5', 'Logitech', 'manufacturer_logitech.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('6', 'Canon', 'manufacturer_canon.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('7', 'Sierra', 'manufacturer_sierra.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('8', 'GT Interactive', 'manufacturer_gt_interactive.gif', '2008-01-24 17:07:01', NULL);

insert into manufacturers (manufacturers_id, manufacturers_name, manufacturers_image, date_added, last_modified) values ('9', 'Hewlett Packard', 'manufacturer_hewlett_packard.gif', '2008-01-24 17:07:01', NULL);

drop table if exists manufacturers_info;

create table manufacturers_info (

manufacturers_id int(11) default '0' not null ,

languages_id int(11) default '0' not null ,

manufacturers_url varchar(255) not null ,

url_clicked int(5) default '0' not null ,

date_last_click datetime ,

PRIMARY KEY (manufacturers_id, languages_id)

);

 

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('1', '1', 'http://www.matrox.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('2', '1', 'http://www.microsoft.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('3', '1', 'http://www.warner.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('4', '1', 'http://www.fox.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('5', '1', 'http://www.logitech.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('6', '1', 'http://www.canon.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('7', '1', 'http://www.sierra.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('8', '1', 'http://www.infogrames.com', '0', NULL);

insert into manufacturers_info (manufacturers_id, languages_id, manufacturers_url, url_clicked, date_last_click) values ('9', '1', 'http://www.hewlettpackard.com', '0', NULL);

drop table if exists newsletters;

create table newsletters (

newsletters_id int(11) not null auto_increment,

title varchar(255) not null ,

content text not null ,

module varchar(255) not null ,

date_added datetime default '0000-00-00 00:00:00' not null ,

date_sent datetime ,

status int(1) ,

locked int(1) default '0' ,

PRIMARY KEY (newsletters_id)

);

 

drop table if exists orders;

create table orders (

orders_id int(11) not null auto_increment,

customers_id int(11) default '0' not null ,

customers_name varchar(64) not null ,

customers_company varchar(32) ,

customers_street_address varchar(64) not null ,

customers_suburb varchar(32) ,

customers_city varchar(32) not null ,

customers_postcode varchar(10) not null ,

customers_state varchar(32) ,

customers_country varchar(32) not null ,

customers_telephone varchar(32) not null ,

customers_email_address varchar(96) not null ,

customers_address_format_id int(5) default '0' not null ,

delivery_name varchar(64) not null ,

delivery_company varchar(32) ,

delivery_street_address varchar(64) not null ,

delivery_suburb varchar(32) ,

delivery_city varchar(32) not null ,

delivery_postcode varchar(10) not null ,

delivery_state varchar(32) ,

delivery_country varchar(32) not null ,

delivery_address_format_id int(5) default '0' not null ,

billing_name varchar(64) not null ,

billing_company varchar(32) ,

billing_street_address varchar(64) not null ,

billing_suburb varchar(32) ,

bi

[Jan Zonjee]

create table manufacturers (

manufacturers_id int(11) not null auto_increment,

manufacturers_name varchar(32) not null ,

manufacturers_image varchar(64) ,

date_added datetime ,

last_modified datetime ,

PRIMARY KEY (manufacturers_id),

KEY IDX_MANUFACTURERS_NAME (manufacturers_name)

);

OK, that is not it either. Only thing I can think of that might be wrong then if that MySQL is not showing the error spot, but the whole query starts with "from products p..." where it should start with something like "select field1, field2, field3 from table, table" etcetera.

My "trick" to quickly check something like that is finding the query and then making a deliberate mistake in it, like changing a field name so that the query gets echo'ed. Put your deliberate error earlier of course then the spot that is shown here.

[themilkman]

Thanks - will try to debug that way.