tekram:
the problem i am having now is that my search function is not working. i get an error when i try to do any search. the error is below. i have not altered the db except with the commands needed for this module. could it possiby be the source?
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 'SELECT DISTINCT
in:[...]
I copy and past your SQL code into my PhpMyAdmin, added the prefix for my tables names ("zc_"), and tried it: it ran without error! (I get the result "total = 44")
My query:
SELECT count( DISTINCT p.products_id ) AS total
FROM zc_products p
LEFT JOIN zc_manufacturers m
USING ( manufacturers_id ) , zc_products_description pd
WHERE p.products_status =1
AND p.products_id = pd.products_id
AND (
p.products_id
IN (
SELECT DISTINCT p.products_id
FROM zc_products p
LEFT JOIN zc_manufacturers m
USING ( manufacturers_id ) , zc_products_description pd, zc_categories c, zc_products_to_categories p2c
LEFT JOIN zc_meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1
WHERE p.products_status =1
AND 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 '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR (
mtpd.metatags_keywords LIKE '%k%'
AND mtpd.metatags_keywords != ''
)
OR (
mtpd.metatags_description LIKE '%k%'
AND mtpd.metatags_description != ''
)
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM zc_products p
LEFT JOIN zc_manufacturers m
USING ( manufacturers_id ) , zc_products_description pd, zc_categories c, zc_products_to_categories p2c
LEFT JOIN zc_meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, zc_product_book_extra pbe, zc_books_to_authors bta, zc_book_authors ba, zc_book_authors_info bai
WHERE p.products_status =1
AND 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 pbe.products_id = p.products_id
AND bta.products_id = p.products_id
AND bta.book_authors_id = ba.book_authors_id
AND ba.book_authors_id = bai.book_authors_id
AND bai.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_authors_name LIKE '%k%'
OR book_authors_nickname LIKE '%k%'
OR subtitle LIKE '%k%'
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM zc_products p
LEFT JOIN zc_manufacturers m
USING ( manufacturers_id ) , zc_products_description pd, zc_categories c, zc_products_to_categories p2c
LEFT JOIN zc_meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, zc_books_to_genres btg, zc_book_genre bg, zc_book_genre_description bgd
WHERE p.products_status =1
AND 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 btg.products_id = p.products_id
AND btg.book_genre_id = bg.book_genre_id
AND bg.book_genre_id = bgd.book_genre_id
AND bgd.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_genre_name LIKE '%k%'
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM zc_products p
LEFT JOIN zc_manufacturers m
USING ( manufacturers_id ) , zc_products_description pd, zc_categories c, zc_products_to_categories p2c
LEFT JOIN zc_meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, zc_books_to_types btt, zc_book_type bt, zc_book_type_description btd
WHERE p.products_status =1
AND 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 btt.products_id = p.products_id
AND btt.book_type_id = bt.book_type_id
AND bt.book_type_id = btd.book_type_id
AND btd.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_type_name LIKE '%k%'
)
)
)
)
For your information, your query was this:
SELECT count( DISTINCT p.products_id ) AS total
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd
WHERE p.products_status =1
AND p.products_id = pd.products_id
AND (
p.products_id
IN (
SELECT DISTINCT p.products_id
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd, categories c, products_to_categories p2c
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1
WHERE p.products_status =1
AND 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 '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR (
mtpd.metatags_keywords LIKE '%k%'
AND mtpd.metatags_keywords != ''
)
OR (
mtpd.metatags_description LIKE '%k%'
AND mtpd.metatags_description != ''
)
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd, categories c, products_to_categories p2c
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, product_book_extra pbe, books_to_authors bta, book_authors ba, book_authors_info bai
WHERE p.products_status =1
AND 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 pbe.products_id = p.products_id
AND bta.products_id = p.products_id
AND bta.book_authors_id = ba.book_authors_id
AND ba.book_authors_id = bai.book_authors_id
AND bai.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_authors_name LIKE '%k%'
OR book_authors_nickname LIKE '%k%'
OR subtitle LIKE '%k%'
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd, categories c, products_to_categories p2c
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, books_to_genres btg, book_genre bg, book_genre_description bgd
WHERE p.products_status =1
AND 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 btg.products_id = p.products_id
AND btg.book_genre_id = bg.book_genre_id
AND bg.book_genre_id = bgd.book_genre_id
AND bgd.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_genre_name LIKE '%k%'
)
)
)
OR p.products_id
IN (
SELECT DISTINCT p.products_id
FROM products p
LEFT JOIN manufacturers m
USING ( manufacturers_id ) , products_description pd, categories c, products_to_categories p2c
LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id = p2c.products_id
AND mtpd.language_id =1, books_to_types btt, book_type bt, book_type_description btd
WHERE p.products_status =1
AND 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 btt.products_id = p.products_id
AND btt.book_type_id = bt.book_type_id
AND bt.book_type_id = btd.book_type_id
AND btd.language_id =1
AND (
(
pd.products_name LIKE '%k%'
OR p.products_model LIKE '%k%'
OR m.manufacturers_name LIKE '%k%'
OR book_type_name LIKE '%k%'
)
)
)
)
If you diff both codes, you will see that there is no other difference that the tables names (because I added the prefixes "zc_").
My MySQL server is version 4.1.11 (4.1.11-Debian_4sarge2-log). Maybe there is something to look on this side?
Sorry, I'm not an SQL guru :-(
If anybody else could help, please!
Regards,
moku
