1109 ERROR - Search function.

[more6lood]

Hi all,

 

Im wondering if some guru would be kind enough to help me out with this issue.

 

When i click the search function & enter the words then click go, i get this error:

 

1109 - Unknown table 'c' in where 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_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 '%tetst%' or p.products_model like '%tetst%' or m.manufacturers_name like '%tetst%') )

 

[TEP STOP]

 

Im using mysql 4, with a custom template, so this is not the mysql 5 bug i read about,

Iv tried seartching the forums & google with no joy.

 

everything else on the site works.

 

Please go easy on me, im no way near being experienced with mysql/php or html.

 

Thanks very very much in advance :)

M.

[morehawes]

Hi, looks like the problem is being because in the query that is running it has been aliased like this : "categories C" (upper case C) but in the rest of the query it is refered to as "c" (lower case c). So this needs to be changed.

 

Is this error occuring in advanced_search_result.php?

 

If so find this line and ensure it is lower case (TABLE_CATEGORIES . " c, ") and no upper (TABLE_CATEGORIES . " C, "):

 

  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

If that doesn't work then let me know and we'll delve deeper.

[more6lood]

Hi Joe,

 

Thx very much your reply!

 

Yes, the search is a direct link to advanced_search, and the return error is on advanced_search_result.php.

 

But I couldn't find the code you mentioned in advanced_search_result.php, which has me even more worried now.

 

I'm lost...I tried for 7 hours last night, i tried about 5 different "solutions" with no success.

 

Is there anything else i could do to help you help me plz? :)

 

Thanks so much.

M.

[morehawes]

Hi again, have you made any modifications / install any contributions which would affect the search code?

 

You could post the entire advanced_search_result.php code between the

...

tags and i'll have a quick look.

[more6lood]

YOU ARE THE MAN!!!! :)

 

My bad of course, i found the line you referred to!

I was looking at a modified version indeed, on my local server, from 1 of the 5 solutions,

I went to sleep late and frustrated and forgot to replace the file again from my backup!

 

Just tested on my web server now and its all working.

Shame i just didn't just post my problem up here, may have saved me 7 hours of sheer terror last night! :)

 

Man, i cant thank you enough!!!...really!

 

All the best mate!

have a great weekend!

 

M.

[morehawes]

Glad you've got it working! :D

 

Yeah, sometimes all it takes is a little nudge to fix those problems you've been up all night trying to fix. I know that feeling well!

 

All the best.