Jump to content

Complicated MYQL Query


malkocoglu

Recommended Posts

Hello All,

 

I need to figure out a way to get a value from data base. The difficulty is that the query will be based on a form request.

 

I have a table called VEHICLES. And I need to pull all values using the data from submitted form field of passengers, large_bags, small_bags by visitor.

 

This table contains different vehicles like a Saloon car which can take up to 4 passengers, 2 large bags and 1 small bag

 

So Example.

 

If a visitor Selects 2 Passengers, 2 Large Bags and 0 Small Bags.

 

It should only pull the vehicle which can take 2 Passengers, 2 Large Bags.

 

current code I have is like this which only selects a specific value from the table. It does not

SELECT * FROM vehicles WHERE passengers ='$passengers' AND large_case='$large_bags' AND small_case='$small_bags' 

This will only return the value according to what ever the visitor select and because of that 32 Saloon vehicle needs to be created which is not practical.

 

I need to make a code that  calculates the option using the values from the form and pulls only the vehicle which is suitable for the entered value

 

Example:

We have following vehicles;

Salooon => up to 4 Passengers, 2 Large Bags, 1 Small Bag

Estate => up to 4 Passengers, 4 Large Bags or 3 Large bags and 2 Small Bag or 2 Large Bags and 4 Small bags or some other combunations

MPV => Up to 6 Passengers, 3 Large Bags and 2 Small Bags or other combinations

Minibus => Up to 8 Passengers and 10 Large Bags and 5 Small bags or other combinations of large and small bags

 

Form Entry: 3 Passengers + 3 Large Bags + 2 Small Bags

 

This should only Return ESTATE.

 

 

I hope I could explain enough.

 

 

Link to comment
Share on other sites

From your query I am guessing your data looks like this

mysql> SELECT * FROM vehicles;
+-------------+---------+------------+------------+------------+
| vehicles_id | type    | passengers | large_case | small_case |
+-------------+---------+------------+------------+------------+
|           1 | Saloon  |          4 |          2 |          1 |
|           2 | Estate  |          4 |          4 |          0 |
|           3 | Estate  |          4 |          3 |          2 |
|           4 | Estate  |          4 |          2 |          4 |
|           5 | MPV     |          6 |          3 |          2 |
|           6 | Minibus |          8 |         10 |          5 |
+-------------+---------+------------+------------+------------+

To save storing all the various combinations of luggage why not store the the total "Luggage Units" that it can hold. From your data you have

Large case = 2 units

Small case = 1 unit

 

So the data would then be

+-------------+---------+------------+------+
| vehicles_id | type    | passengers | LU   |
+-------------+---------+------------+------+
|           1 | Saloon  |          4 |    5 |
|           2 | Estate  |          4 |    8 |
|           5 | MPV     |          6 |    8 |
|           6 | Minibus |          8 |   25 |
+-------------+---------+------------+------+

the query for "Form Entry: 3 Passengers + 3 Large Bags + 2 Small Bags" becomes

SELECT type FROM vehicles
WHERE passengers >= 3
AND LU >= 8
ORDER BY passengers
LIMIT 1

giving

+--------+
| type   |
+--------+
| Estate |
+--------+
  • Like 1
Link to comment
Share on other sites

I would change the tables slightly and have a "rating" type column for storage.

 

i.e. bodyType | maxPassengers | maxStorage

so your saloon would have 4 max passengers and a maxStorage of 5, your estate would have 4 max passengers and a max storage of 9 and so on.

 

Then have a small bag worth 1 storage and a large bag worth 2.

 

Then you can easily add up the form input and apply to the query:

...
$storage = (int)$small_storage + 2 * (int)$large_storage;
$qry = "SELECT DISTINCT bodyType, maxPassengers, maxStorage FROM vehicles WHERE maxPassengers >= $passengers AND maxStorage >= $storage ORDER BY maxPassengers, maxStorage DESC";
...

EDIT: @Barand said it better than I did while I was typing.  

*tips hat*

Edited by Muddy_Funster
Link to comment
Share on other sites

Thank you guys for prompt answer. The difficulty I will have with that is that I say saloon car can take 2 large 1 small which is 3 suitcases. If I allow them to choose 3 suitcases in total then they could choose 3 large suitcase which will not fit in a saloon car.

 

 

Hope it makes sense...

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.