Order Search

[rubygirl]

hey all - does anyone know where to start in order to create a search for the orders.php page?

 

I would like to be able to search for ANY information like ordered products, last names, email address, product attributes, anything entered into the orders.

 

any thoughts?

[rubygirl]

If anyone can at least point me in the right direction - that would be helpful - I understand that when in the admin area you can search by order number - I simply want to be able to search by any data in the order - including attributes, client name, date ordered, anything that has to do with an order ... anyone????

[bambam92]

I also would like to be able to add the ability to search for Products_Model in the orders.php file.

 

We have many merchants (Vendors) who each use a unique indentifier for ALL products they add to the inventory. When orders are made the orders.php file DOES include this information on the orders page, shipping label. and the Invoice.

 

example, Merchant John E Hogan may be assigned such as jeh1 as his unique id number, while debra j hogan may be assigned djh1 for her unique id number to use.

 

It would be very very handy for john e hogan to simply type in his unique number (jeh1) into the orders search box to bring up a List of all his orders (pending, processing, or shipped).

 

I have added various mods to our system. Searching for a particular vendors products works perfectly when doing the search from the Catalogs built in search function. I merely would like to see this ability implemented in the Orders.php files built in search to allow searching ability of the Product Model...

 

Tryed this myself :( Complete flop as usual :(

[Guest]

here is a start you could always enhance the search queries

 

in the admin\orders.php

 

1. Setup a new form find this code

			  <tr><?php echo tep_draw_form('orders', FILENAME_ORDERS, '', 'get'); ?>
			<td class="smallText" align="right"><?php echo HEADING_TITLE_SEARCH . ' ' . tep_draw_input_field('oID', '', 'size="12"') . tep_draw_hidden_field('action', 'edit'); ?></td>
		  </form></tr>

 

replace with this:

			  <tr>
			<td class="smallText" align="right">
<?php
echo tep_draw_form('search', FILENAME_ORDERS, '', 'get');
echo HEADING_TITLE_SEARCH . ' ' . tep_draw_input_field('search');
echo tep_draw_hidden_field(tep_session_name(),tep_session_id());
echo '</form>';
?>
			</td>
		  </tr>

 

2. Setup the query - further down find this code:

	if (isset($HTTP_GET_VARS['cID'])) {

 

replace it with this

	if(isset($HTTP_GET_VARS['search'])) {
  $search = tep_db_prepare_input($HTTP_GET_VARS['search']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_ORDERS_STATUS . " s on (o.orders_status=s.orders_status_id) where (op.products_name like '%" . tep_db_input($search) . "%' or op.products_model like '%" . tep_db_input($search) . "%' or o.customers_name like '%" . tep_db_input($search) . "%' or o.customers_email_address like '%" . tep_db_input($search) . "%') and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
} elseif(isset($HTTP_GET_VARS['cID'])) {

 

just enhance it and add more columns from the order products table in the where clause. And replace the language string for the search box.

[bambam92]

here is a start you could always enhance the search queries

 

in the admin\orders.php

 

1. Setup a new form find this code

			  <tr><?php echo tep_draw_form('orders', FILENAME_ORDERS, '', 'get'); ?>
			<td class="smallText" align="right"><?php echo HEADING_TITLE_SEARCH . ' ' . tep_draw_input_field('oID', '', 'size="12"') . tep_draw_hidden_field('action', 'edit'); ?></td>
		  </form></tr>

 

replace with this:

			  <tr>
			<td class="smallText" align="right">
<?php
echo tep_draw_form('search', FILENAME_ORDERS, '', 'get');
echo HEADING_TITLE_SEARCH . ' ' . tep_draw_input_field('search');
echo tep_draw_hidden_field(tep_session_name(),tep_session_id());
echo '</form>';
?>
			</td>
		  </tr>

 

2. Setup the query - further down find this code:

	if (isset($HTTP_GET_VARS['cID'])) {

 

replace it with this

	if(isset($HTTP_GET_VARS['search'])) {
  $search = tep_db_prepare_input($HTTP_GET_VARS['search']);
  $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_ORDERS_STATUS . " s on (o.orders_status=s.orders_status_id) where (op.products_name like '%" . tep_db_input($search) . "%' or op.products_model like '%" . tep_db_input($search) . "%' or o.customers_name like '%" . tep_db_input($search) . "%' or o.customers_email_address like '%" . tep_db_input($search) . "%') and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
} elseif(isset($HTTP_GET_VARS['cID'])) {

 

just enhance it and add more columns from the order products table in the where clause. And replace the language string for the search box.

 

 

MAN, That worked perfectly Enigma1 :)

 

Send me your PayPal email address and I will send you $100.00 US as you saved me a LOT of time which i really dont have anyway :(

[Guest]

I'm trying to create a php page with a search box and a drop down box to search through customers/orders.

 

Example:

 

Type in search box 'bloggs' and select 'Last Name' to search for anyone with that surname.

 

I also want this search and drop box o be able to handle the following:

 

First Name

Last Name

Company Name

Billing Post Code

Shipping Post Code

Item Purchased

 

Is there a quick way of doing this or maybe something already in circulation (can't find anything in he contributions/forum)

 

I hope that someone can help me with this - it would save me on average 20minutes a day trying to find certain customers records when they supply limited information (usually on the telephone).

 

Thanks, in advance :thumbsup:

[♥stubbsy]

Hi there,

 

I've had this installed and working for a while, I'm able to find orders by product, customer etc.

 

But what I've just realised is that I'm no longer able to put in an order number and find an order??

 

Any ideas

 

Cheers

 

Dave

[♥stubbsy]

oops - double post

[poorgirl_richgirl]

hi, the search works, but i cant seem to get my order numbers to come up in search

[kru]

If you still want your Order ID search box, instead of replacing the code in Step 1, merely add the new code directly above the following line:

 

<tr><?php echo tep_draw_form('orders', FILENAME_ORDERS, '', 'get'); ?>

 

 

This will add a new search box above the Order ID box.

 

Also, there are a lot of joins in his search query from Step 2. We have a lot of orders and a lot of products so we were getting an error that we had exceeded the maximum size for rows to be examined. We were not looking to search through products. We just needed the ability to search for an order by email address and another custom field we had added for PO Number. So I modified his select statement. Specifically, I removed:

 

TABLE_ORDERS_PRODUCTS . " op on (op.orders_id = o.orders_id) left join " .

 

and

op.products_name like '%" . tep_db_input($search) . "%' or op.products_model like '%" . tep_db_input($search) . "%' or

 

And I replaced the code for customers_name with our new po_number field since it is also in the orders table.

 

I hope this helps someone. This code was a godsend for us. Many thanks to enigma1.

[kru]

I turned this into a contrib. My first, so don't be too critical. It can be found here:

 

http://addons.oscommerce.com/info/8281

 

 

Enjoy!!

[♥bruyndoncx]

nice effort thumbs up!

[kru]

Thanks. I figure I've used enough contribs, it was time I submitted one myself. Plus, as I was searching for the ability to do this, I found quite a few posts asking for a way to do the same thing and enigma1 was the only one who provided a solution. And none of the other posts linked back to his. I just found it because I was persistent and creative in my search attempts...