On an eCommerce Site, I need to be able to filter orders by Customer First Name and/or Customer Last Name and/or Date Range and/or Products Ordered - using any or all of those criteria to narrow down a list.
The mySQL Query I have to filter by everything BUT Products Order is:
SELECT orders.idorder, customers.name, customers.lastName, customers.email, orders,orderDate, orders.total, orders.paymentDetails FROM orders, customers WHERE orders.idCustomer = customers.idCustomer AND customers.name LIKE '%ache%' AND customers.lastName LIKE '%ne%' AND orders.OD4SR>=str_to_date('01/01/2009', '%m/%d/%Y') AND orders.OD4SR<=str_to_date('12/31/2009', '%m/%d/%Y') ORDER BY idOrder DESC
(and I use programming conditional statements to only include the parts of the Query needed based on Filter Data Entered)
The above query works without fail (although there is probably an easier way to do it)
Here is the problem - I need to also Filter based on Products Ordered, in that if I enter say "Widget", only those orders that ordered a "Widget" will show.
Right now, I run a SECOND Query based on the results of the First Query:
SELECT products.idProduct FROM orders, dbSessionCart, cartRows, products WHERE orders.idOrder='[idOrder-From-First-Query]' AND orders.idOrder = dbSessionCart.idOrder AND dbSessionCart.idDbSessionCart = cartRows.idDbSessionCart AND cartRows.idProduct = products.idProduct AND products.description LIKE '%Widget%'
If Data Returned from Second Query, show Data from First Query, if Not - Skip It.
Problem is - when the first query is done, it will return 50 results per page from one query. But then the second query will need to run 50 times (one for each result from the first query) in order to filter the returned orders based on products ordered. And that causes things to slow down quite a bit.
Table Relationships to one another are:
Then for Products:
Is there any way, I can take the two queries above - and join them into ONE Query, so that I only return the rows I need?
This probably makes no sense, so please ask questions and I will answer as best I can.
Basically, all I am looking for is the query to filter based on EVERYTHING. From there, I can hack the query into conditional statements. I just have no idea how to join the two queries together to speed up the process and to get all data through one query, not dozens of repeated queries.