1054 Unknown column 'o.orders_id' in 'on clause' -- in admin order search

I have this error coming up when I search in customer orders by item name or item model

1054 Unknown column 'o.orders_id' in 'on clause'
in:
[select count(distinct o.orders_id) as total from (zen_orders o, zen_orders_status s left join zen_orders_products op on (op.orders_id = o.orders_id) ) left join zen_orders_total ot on (o.orders_id = ot.orders_id) where (o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total') and (op.products_model like '%098590%' or op.products_name like '098590%')]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

answer to your question can be found at the bottom of this page. .also you can look at this thread

http://www.zen-cart.com/forum/showthread.php?t=111000&highlight=1054+Unknown+column+%27o.orders_id%27+%27on+clause%27

Thanks tony no more error but search doesnt find anything at all.

When did this "start" for you?

Dr it started when I installed click and ship Im guessing because thats the mod that changes order.php.. But cant find orders by product name if I dont remember the customers name or order number, Drbyte is there a way to fix this search in admin to work?

The symptoms you describe are strongly similar to putting an old version of orders.php over a newer version.

You'll need to put the newer one back up, and then merge just the unique additions/changes for the addon that replaced it.

Oh :( my php is not so good....

you don't need much php knowledge for merging files.
if your OS is window , then download and install free tool called winmerge . this tool allow you to compare files with one anther and merge differences.

using this tool , compare the fresh copy of order.php with order.php that comes with addon . you can see the differences highlighted by this utility. simply copy the difference from addon to zencart order.php file ( done from winmerge window ) .

Actually even if I put back the original orders.php file, the search function always comes back as zero result

rxalex:

Actually even if I put back the original orders.php file, the search function always comes back as zero result
The search on a clean install without mods properly locates orders when putting customer names in the Search box, and product names or product models in the products search box, and orders when putting the order number in the Order Number search box.

Have you tried searching using upper/lower case? Maybe you've got a case-sensitivity problem?

Your right it is case sensitive, good job (master trouble shooter). Anyway to fix this?

1. Find out what collation your database is presently using, by following the dialog here: http://www.zen-cart.com/forum/showthread.php?p=874535

It sounds like your database is set to use a case-sensitive collation, instead of a case-insensitive collation (denoted by a _ci suffix).

2. Use the suggested changes to as an example to adjust your database's collation to a suitable mode based on your current collation.
   DO NOT CHANGE THE BASE COLLATION (ie: if it's latin1_something, change it to a latin1_something_ci ... DO NOT change it from latin1_something to utf8_something_ci, as that will create serious problems if you don't follow a whole bunch of other important steps.

My sql looks like as attached... Can you assist me in what exactly needs to be patched . Appreciate your help, zencart was installed via simplescript - I have two sites hopefully your fix will work for both v1.3.8

Oh my ... why do you have a mix of both latin1_swedish_ci and utf8_bin ?

In the case of your immediate search issue, use what I posted in that thread. It will set your utf8_bin fields to utf8_general_ci which will resolve the immediate problem.

As to all your other tables being a mishmash of collations, I recommend you work with someone to clean things up to be consistent. That's an entirely different topic, and will require working with someone with the specific skills needed. Perhaps for hire.

Thanks Doc... what exactly needs to be done so I can have them fix it?