Complicated MYQL Query

[malkocoglu]

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.

 

 

[Barand]

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 |
+--------+

[Muddy_Funster]

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 June 15, 2015 by Muddy_Funster

[Barand]

Good to see we can agree on something

[Muddy_Funster]

Had to happen sooner or later lol. 

 

nah, joking aside: I tend to not even look in on threads I see you have posted on because I know there's going to be nothing I'll be able to add.

[malkocoglu]

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...

[Barand]

Then don't tell them 3 suitcases, tell them what we said (5 half-suitcases if you like) or give them a cubic capacity maximum. Instead of working in the simpler units as I suggested, work in absolute capacity (cu.ft or litres)

[malkocoglu]

I get it now. Brilliant idea. Thank you guys.....