1054 Unknown column 'o.orders_id' in 'on clause'

[dbrewster]

cool, thanks, the case of the missing parentheses! I got it, the page load is fixed.

But it's still generating an error on "Search by Product Name or ID:XX or Model." All the other search fields work.

I enter the word "ONLINE" in that field, it generates this error:

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 and ot.class = 'ot_total' ) where (o.orders_status = s.orders_status_id and s.language_id = '1') and (op.products_model like '%ONLINE%' or op.products_name like 'ONLINE%')]

[DrByte]

left join zen_orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total' )

Change the o.orders_id (in the specific spot shown) to be op.orders_id instead.

[dbrewster]

changed in all three places, as advised.

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = 
ot.orders_id and ot.class = 'ot_total'  ) " . " 

became

left join " . TABLE_ORDERS_TOTAL . " ot on (op.orders_id =
 ot.orders_id and ot.class = 'ot_total'  ) " . " 

Error on page load:

1054 Unknown column 'op.orders_id' in 'on clause'
in:
[select count(*) as total from (zen_orders o, zen_orders_status s ) left join zen_orders_total ot on (op.orders_id = ot.orders_id and ot.class = 'ot_total' ) where (o.orders_status = s.orders_status_id and s.language_id = '1')]

You create the search_orders_products filter this way:

    $new_table = " left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) ";
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search_orders_products']));
    $search = " and (op.products_model like '%" . $keywords . "%' or op.products_name like '" . $keywords . "%')";
    if (substr(strtoupper($_GET['search_orders_products']), 0, 3) == 'ID:') {
      $keywords = TRIM(substr($_GET['search_orders_products'], 3));
      $search = " and op.products_id ='" . (int)$keywords . "'"; 

we haven't looked at that code ... ? Disclaimer: I am pretty ignorant about how these queries are structured, and have no idea what's wrong. I just know that code is executing, too.

well, I'm going home, to be continued tomorrow for me! Have a good evening, Dr. Byte!

--diana

[DrByte]

changed in all three places, as advised.

No, not all 3 places ... just the one.

[oavs]

The site is 1.3.7 already.

Update: the fix was in SEO URLs to set "Add category parent to beginning of URLs" to "false"

Hope that can help anyone else in the same fix!

Thanks for your help on PM, Merlin.

Okkkk.. I never installed SEo stuff (using pre build temp[late which may already have) but still getting the same error.

So where do I find the problem , in admin where ? can you please advise?

Thanks

[dbrewster]

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id
 and ot.class = 'ot_total'  ) " . " 

This line occurs 3 times in the search filter part of the code. I've tried changing each one singly to

left join " . TABLE_ORDERS_TOTAL . " ot on (op.orders_id = ot.orders_id
 and ot.class = 'ot_total'  ) " . " 

If I change only the last instance, I get this error message:

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 (op.orders_id = ot.orders_id and ot.class = 'ot_total' ) where (o.orders_status = s.orders_status_id and s.language_id = '1') and (op.products_model like '%ONLINE%' or op.products_name like 'ONLINE%')]

changing either of the first two instances produces this error message:

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 and ot.class = 'ot_total' ) where (o.orders_status = s.orders_status_id and s.language_id = '1') and (op.products_model like '%ONLINE%' or op.products_name like 'ONLINE%')]

[dbrewster]

your original edit suggestion doesn't actually occur in the code, so I approximated.

left join zen_orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total' )

[michaelhl]

Is the problem solved now? I use all corrections I read above and I still have the same problem.
The search:"Search by Product Name or ID:XX or Model " doesn't work.
But isn't the problem in Line 689-697:

if (isset($_GET['search_orders_products']) && zen_not_null($_GET['search_orders_products'])) {
    $new_fields = '';
    $search_distinct = ' distinct ';
    $new_table = " left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) ";
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search_orders_products']));
    $search = " and (op.products_model like '%" . $keywords . "%' or op.products_name like '" . $keywords . "%')";
    if (substr(strtoupper($_GET['search_orders_products']), 0, 3) == 'ID:') {
      $keywords = TRIM(substr($_GET['search_orders_products'], 3));
      $search = " and op.products_id ='" . (int)$keywords . "'"; 

I try to test all corrections in the thread, but it doesn't work.

[dbrewster]

I don't have this problem solved, either.

For me, since it's an additional feature that I have lived without until now, it's a minor bug. Hopefully it will be working in the next release.

---Diana

[michaelhl]

You're right, it is a usefull but unneeded feature. I don't want to have a mistake in my shop and so I will delete this searchbox in my orders.php.
Thanks for your quick reply.
Michael