Search problems

[typeinmotion]

Hi

 

Can any help me figure this out. Everytime i do a search i get the same error message:

 

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 'and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.' at line 1

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '0' and ((pd.products_name like '%test%' or p.products_model like '%test%' or m.manufacturers_name like '%test%') )

 

[TEP STOP]

 

I have had a good look around all the exisiting threads but have had no luck

 

Many thanks

[Guest]

I don't know where this query is from but your problem is here.

 

... products_to_categories p2c and p.products_id = p2c.products_id and ...

 

it should be

... products_to_categories p2c where p.products_id = p2c.products_id and ...

[typeinmotion]

Many thanks that removed the last error and replaced with

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '0' and ((pd.products_name like '%casio%' or p.products_model like '%casio%' or m.manufacturers_name like '%casio%') )

 

Any ideas

[Guest]

I got the same error number/messages ever since my web hosting company upgrades MySQL from 3.x to 5.x.

 

Apparently ALL queries which use ANSI's LEFT JOIN don't work anymore.

 

I don't know in detail about MySQL. I there any global database setting to support this (backward compatibility)? Does MySQL 5 now have different SQL syntax?

 

Can't imagine I have to change ALL the queries (from ANSI's LEFT JOIN to inner join)! :'(

 

Any better advise?

[claired100]

Hi there,

Did anyone figure this out? Can someone please help me?

Cannot see any products in my store, am getting this message

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '552'

 

Thanks,

Claire

[Guest]

Apparently ALL queries which use ANSI's LEFT JOIN don't work anymore.

 

I don't know in detail about MySQL. I there any global database setting to support this (backward compatibility)? Does MySQL 5 now have different SQL syntax?

 

Can't imagine I have to change ALL the queries (from ANSI's LEFT JOIN to inner join)! :'(

 

Any better advise?

no, left join works fine with mysql 5.x. See the devmysql site for all changes between versions. And you could get the latest osc upgrade that has the queries modified to work with 5.x