Here is my problem:
for example:
User inputted: 2000 as desired budget
User selected Wedding as desired service type
What are all the combinations of services that can be afforded given the budget?
My initial query is:
Select price,service_inclusion,
soid, servicetype
FROM service_offer where servicetype='Wedding' and SUM(price) = 2000
Here are the data in my database.
ID Service_type Price
1 Wedding 500
2 Wedding 700
3 Wedding 1000
4 Wedding 300
5 Wedding 1000
This is what will be the result either this:
4 Wedding 300
2 Wedding 700
3 Wedding 1000
Or
3 Wedding 1000
5 Wedding 1000
Or I can use it random. Can I make it on a query or just I can do it in php code?
If I understand correctly. You want a query to return all possible combinations of items with service type 'Wedding' that will add up to your budget.
This is a difficult problem, basically a variant of the bin packing problem
A simple approach might be to get all the values of the items that match 'Wedding' and start subtracting them from your remaining budget until you get to 0 (or close to 0).
You could start with small amounts to get more 'services' into your budget or start with large amounts to have only a few services.
It's probably possible to do this in SQL, but not recommended.
@jayboune - I agree with Loopo. It's not an easy problem to solve as you want to consider every possible combination (assuming that you "Wedding" is always included). The processing requirements (memory, time etc) would increase each time a new service was added to the database and be made available.
Probably an easier option (and a better customer experience) would be to present the customer with the available services (including prices) and keep a running total on the page they are using as they choose services they want. Only presenting services which cost less than the remaining budget will make the customer think more about what they really want.
Using this method means that the time delay in choosing things will far outweigh any processing delays. And, from a customer point of view, there won't be a long delay before the can actually start doing things