Organizing Data

programming
1

Hey,

I am using PHP and MySQL to allow a visitor to my web site enter in their specific intentions when recording using a camcorder, entering in their budget, and then it will search for camcorders that fit the budget and will meet the requirements for their intentions.

For example, I have a MySQL table of options that include budget, use (personal/corporate), and then some specific examples of recording such as family videos, nature/landscapes.

SO, I have a table of camcorders and a table of options. The options table refers to the camcorder table, I have a row called cidowner which is the ID of the camcorder.

I want the script to return camcorders that are below the budget and are for a specific use. These are more strict. However, for recording intentions, they can check up one or all of the options. So, I want camcorders that meet any of the criteria they search for. For example, only one of the criteria have to match for the camcorder to show.

Would using an array be the best way to do this? Could someone just point me into the right direction?

If you need a better explanation, please let me know and I will try my best!

Thanks

2

If you need help constructing the mysql query then post the structure of your tables and we’ll help you put it together.

3

Table: camcorders
Fields: cid, name, description, url, using

Table: options
Fields: oid, cidowner, options, value

The options refer back to the camcorder using the cidowner id. This id is the id of the camcorder. Now, I have added in options for the following (example)-

Oid: 1
Option: budget
Value: 300
cidowner: 23

Oid: 2
Option: famvideos
Value: 1
cidowner: 23

So, this would mean that camcorder # 23 has a budget of $300 and is a good camcorder for family videos.

My mistake may be making all of these different option types in separate rows, because the customer will be checking off these options.

The form that will be searching the database asks the user to select a budget, use (corporate or personal), and then a list of check boxes for their intent with the camcorder (ie- family videos, commercials, nature shooting, etc).

How can I have it search for a camcorder that is within the budget, use, and then matches only one of their intentions. So, if they check off for family videos and commercials it will show camcorders that have either family videos OR commercials, it doesn’t have to be EXACTLY both.

Hope this helps more! Thanks

4

Hey again.

Try playing around with something like this:


SELECT DISTINCT camcorders.cid
FROM options
LEFT JOIN camcorders
  ON options.cidowner=camcorders.cid
WHERE camcorders.using='MY_USE'
AND options.Value<=MY_BUDGET
AND (
	options.Option='OPTION_1' OR
	options.Option='OPTION_2' OR
	options.Option='OPTION_3');

You have to use code to generate the “options.Option=‘OPTION_X’ OR” part. Basically you’ll want one of those lines for each checkbox the user has checked. If I understand your setup and intentions correctly, the above query should get you pretty close.