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

I'm getting a similar error when I try to use the new search fields in Admin > Orders.

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_id ='146']
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.

But I can't find where to edit SEO URLs in admin! It sounds like a contribution, but I'm not using any contribution like that.

Ideas?

---Diana

Yours is not "p.products_id" ... yours is "o.orders_id" --- Does this help?

http://www.zen-cart.com/forum/showthread.php?t=79408

thanks for reply, Dr. Byte!

I looked at Shrimp-Gumbo's suggestion as you linked, and it looks to me like that code is already in 1.3.8a.

The code that's failing for me in admin/orders.php looks like this, line 753:

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
               where o.orders_status = s.orders_status_id and s.language_id
= '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" .
(int)$status . "' and ot.class = 'ot_total'  " .

I'm not sure how to edit this, could you suggest please?

I'm using MySQL 5.0.27-standard, PHP 4.4.7. Our server was migrated to new hardware and upgrade MySQL, which has caused some minor issues in other PHP/MySQL apps like WordPress special character encoding.

I have an option to upgrade to PHP 5, should I do that? :tremble: "tremble" because I'm running a few other apps... but they are all maintained to current versions: SMF forums, Moodle, WordPress.

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 '%ONLINE%' or op.products_name like 'ONLINE%')]

thanks!!! you're a dear.

---Diana

dbrewster:

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where o.orders_status = s.orders_status_id and s.language_id
= '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" .
(int)$status . "' and ot.class = 'ot_total' " .

try this:

left join " . TABLE_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
= '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" .
(int)$status . "'" .

Thanks!!!

I tried out the code in my sandbox, and am still getting the same 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) where (o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total') and (op.products_model like '%ONLINE%' or op.products_name like 'ONLINE%')]

The other search fields work fine, it's the new "Search by Product Name or ID:XX or Model" field that's misbehaving.

thoughts?

Please look again at what I posted. You moved it to the wrong place, or didn't do it in the right query.
There are several similar queries in that section of code.

OK, I found two similar queries.

This one part I can't edit correctly, I'm getting a SQL error when I try to load orders.php with this code.

Original code: line 746

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where (o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and ot.class = 'ot_total')  " .
```My edit:
```php
left join " . TABLE_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 = '" . (int)$_SESSION['languages_id'] . " ' " .

Did you accidentally remove a closing parenthesis from the spot where you moved the code "from"?

What is the new SQL error?

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 '' at line 3
in:
[select count(*) as total from (zen_orders o, zen_orders_status s ) 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']

here are the three edits I'm trying with original line numbers referenced:

admin/orders.php
line 722

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and ot.class = 'ot_total' order by orders_id DESC";

**
changed to:**

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total'  ) " . "
where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' order by orders_id DESC";

line 734

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total'  " .

changed to:

left join " . TABLE_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
 = '" . (int)$_SESSION['languages_id'] . "' and s.orders_status_id = '" . (int)$status . "'" .

line 746

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where (o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and ot.class = 'ot_total')  " .

changed to:

left join " . TABLE_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 = '" . (int)$_SESSION['languages_id'] . "'" .

those are the only changes I've tried, in various combinations. The last change generates the SQL error on page load, but with only the first two edits the error on search remains.

thank you for your time and troubleshooting brains! :hug:

---db

dbrewster:

line 746

left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) " . "
where (o.orders_status = s.orders_status_id and s.language_id = '" . (int)$_SESSION['languages_id'] . "' and ot.class = 'ot_total') " .

I repeat: You dropped a parenthesis ... as shown.

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%')]

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.

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

dbrewster:

changed in all three places, as advised.No, not all 3 places ... just the one.

knitwerk:

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

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%')]

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' )

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.

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

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