Searching order comments

[perfumbg]

Hello, I somehow managed to do something but it is not enough. I am puting the tracking number for the order on the comments, and I want to be able to search the orders for that tracking number.

What I managet to do so far:

I have made change on this lines in /admin/orders.php

// create search filter
  $search = '';
  if (isset($_GET['search']) && zen_not_null($_GET['search'])) {
    $search_distinct = ' ';
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search']));
    $search = " and (o.customers_city like '%" . $keywords . "%' or o.customers_postcode like '%" . $keywords . "%' or o.date_purchased like '%" . $keywords . "%' or o.billing_name like '%" . $keywords . "%' or o.billing_company like '%" . $keywords . "%' or o.billing_street_address like '%" . $keywords . "%' or o.delivery_city like '%" . $keywords . "%' or o.delivery_postcode like '%" . $keywords . "%' or o.delivery_name like '%" . $keywords . "%' or o.delivery_company like '%" . $keywords . "%' or o.delivery_street_address like '%" . $keywords . "%' or o.billing_city like '%" . $keywords . "%' or o.billing_postcode like '%" . $keywords . "%' or o.customers_email_address like '%" . $keywords . "%' or o.customers_name like '%" . $keywords . "%' or o.customers_company like '%" . $keywords . "%' or o.customers_street_address  like '%" . $keywords . "%' or o.customers_telephone like '%" . $keywords . "%' or o.ip_address  like '%" . $keywords . "%')";
    $new_table = ""; 

I have changed it to:

// create search filter
  $search = '';
  if (isset($_GET['search']) && zen_not_null($_GET['search'])) {
    $search_distinct = ' ';
    $keywords = zen_db_input(zen_db_prepare_input($_GET['search']));
    $search = " and (o.customers_city like '%" . $keywords . "%' or o.customers_postcode like '%" . $keywords . "%' or o.date_purchased like '%" . $keywords . "%' or o.billing_name like '%" . $keywords . "%' or o.billing_company like '%" . $keywords . "%' or o.billing_street_address like '%" . $keywords . "%' or o.delivery_city like '%" . $keywords . "%' or o.delivery_postcode like '%" . $keywords . "%' or o.delivery_name like '%" . $keywords . "%' or o.delivery_company like '%" . $keywords . "%' or o.delivery_street_address like '%" . $keywords . "%' or o.billing_city like '%" . $keywords . "%' or o.billing_postcode like '%" . $keywords . "%' or o.customers_email_address like '%" . $keywords . "%' or o.customers_name like '%" . $keywords . "%' or o.customers_company like '%" . $keywords . "%' or o.customers_street_address  like '%" . $keywords . "%' or o.customers_telephone like '%" . $keywords . "%' or o.ip_address  like '%" . $keywords . "%' or osh.comments like '" . $keywords . "%')";
    $new_table = "left join ".TABLE_ORDERS_STATUS_HISTORY." osh on (osh.orders_id = o.orders_id)"; 

As well i added osh.comments to all $orders_query_raw = "select osh.comments that has something to do with the TABLE_ORDERS

And now it is possible to perform searh in the order coments but that search has to be exact.

For example if I search for order that have coment xxxxxxxxx122 and I tipe in the serch field xxxxxxxxx12 or anything that differs from xxxxxxxxx122 the search result is empty, how can I make it possible to search for part of the tracking, because I am too lazy to type every time the full number sequence, and i think it is not needed.

Thanks for the help :)

[perfumbg]

Update, after few tests i found that order statut list is affected and not displayed and i have this error

Europe/Sofia] PHP Fatal error: 1054:Unknown column 'osh.comments' in 'field list' :: select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total,osh.comments, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address, osh.comments
from (orders_status s, orders o )
left join 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 = '2') order by o.orders_id DESC ==> (as called by) public_html/admin/orders.php on line 932 <== in public_html/includes/classes/db/mysql/query_factory.php on line 155

Now I lant to fix this and to have what i made till now.
Any help is wellcomed.

[DrByte]

In your first post you said you added this:

or osh.comments like '" . $keywords . "%'

You'll need to make one small change if you want the comment-search to be searchable with ??????12. The extra % puts the wildcard in front of your criteria:

or osh.comments like '%" . $keywords . "%'

[perfumbg]

Thank you, I will check this and share the results.

P.P.

I tested it and it is working pretty well, thank you so much dr. Byte

[perfumbg]

If anybody needs this you can alter the product model search fields in orders.php, I had to find this line:

    $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 . "%')"; 

and I replaced it with this:

    $new_table = " left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id)
    left join ".TABLE_ORDERS_STATUS_HISTORY." osh on (osh.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 . "%' or osh.comments like '%" . $keywords . "%')"; 

Now I can search in orders comments from the "Search by Product Name or ID:XX or Model" field in orders
With the kind help of drByte.

[farmerrem]

I am looking to do the same thing but would also like the ability to search (filter) pick-up locations (I am using the storepickup module.) I am using Zen Cart v.1.5.4 - eatdavidspasture.com

Any help is greatly appreciated.

-Remington Kesten