How to search by product in admin?

[Bartman]

As the title suggests, I want to search on a product in the admin screen and see a list of the customers that have bought it. Any ideas?

 

Thanks,

 

Bart

[Guest]

basically you need an sql query to retrieve it. Here it is:

 

select o.customers_name, op.products_name from orders o left join orders_products op on (op.orders_id=o.orders_id) where products_name='Herbal Aloe Hair Pack'

 

You could use this contribution and add it to the repository. Then it can generate reports for you right from the osc admin

http://www.oscommerce.com/community/contributions,4238

[Guest]

just replace the product name in your case I used the one from the test I did here

[Bartman]

Fantastic, thank you! One last favour to ask, what syntax would I need to use to search on just a part of the product name? For example, is it possible to search on 'RX7' to return the results for products called 'Mazda RX7' and 'RX7 SP'? The reason I ask is that the product name may have changed slightly over the years and I'd like to maximise the results.

 

I'll take a look at the contrib, cheers.

 

Thanks again,

 

Bart

[Guest]

use the like

 

select o.customers_name, op.products_name from orders o left join orders_products op on (op.orders_id=o.orders_id) where products_name like '%Mazda RX7%'

[Bartman]

Thanks Mark, you have saved me hours and hours of work. Much appreciated!

[skeeweeaka]

Mark can you tell me what that statement you just posted means with the "o"? I am having problems with the Western Union Module and the 1054 and 1064 errors contains "o" statements. I can't seem to get anyone to help me with finding out what I am supposed to do to get it to work properly... I noticed Elari is on the boards but can't get ahold of him for a response. I would like to use Western Union...please help....

[Guest]

it differentiates the sql tables; when multiple tables have the same column name to specify which column from which table you access.

[skeeweeaka]

Could you possibly help me figure the error out.

 

1054 - Unknown column 'osh.new_value' in 'where clause'

 

select os.orders_status_name, osh.date_added from orders_status os, orders_status_history osh where osh.orders_id = '222' and osh.new_value = os.orders_status_id and os.language_id = '1' order by osh.date_added desc

 

[TEP STOP]

 

 

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 'select o.orders_id, o.orders_status, o.date_purchased, o.delive

 

select count(select o.orders_id, o.orders_status, o.date_purchased, o.delivery_name, ot.text as order_total, s.orders_status_name from orders o left join orders_total ot on (o.orders_id = ot.orders_id) left join orders_status s on (o.orders_status = s.orders_status_id and s.language_id = \'1\') where o.customers_id = \'2\' and ot.class = \'ot_total\' order by orders_id DESC) as total

 

[TEP STOP]