CODEWITHSUNDEEP
phpmysql
Vurkac
Vurkac

Reputation: 133

Mysql Query with multiple query

First of all, I know the question title may have been asked before but my query is slightly different. I have searched but I couldn't find what I am looking for.IF anyone knows that this question asked before, please provide a link to that post.

So My question is this;

This is a minicab booking website. I have VEHICLES tables. In this table I have 4 vehicles. Saloon, Estate, MPV, Minibus. These vehicles have values like passengers and luggage capacity etc. as follows;

  • Saloon => passengers=4 AND luggage_capacity=5
  • Estate => passengers=4 AND luggage_capacity=8
  • MPV => passengers=6 AND luggage_capacity=10
  • Minibus=> passengers=10 AND luggage_capacity=30

Now, When visitor enters information of how many passengers and how many luggages, Sql query should return the correct vehicle for the information given.

Example: Visitor Selects 3 passengers and 5 luggages. This should return SALOON vehicle. If passengers is 6 and no luggage MPV should return as result and so on.

I have tried the following sql query but wrong vehicle is displayed.

SELECT name FROM vehicles WHERE passengers >= $passengers 
AND luggage_capacity >= $luggage_capacity

I hope I could explain what I meant. Any help is appreciated.

Upvotes: 0

Views: 80

Answers (4)

Salaander
Salaander

Reputation: 126

You do give default values to the $passangers and $luggage_capacity variables I hope and they are integers.

Also the result of this query might give you multiple results and in the wrong order. You should order the table the way you want and only select the smallest fitting car, something like this:

SELECT  `name` FROM `vehicles` WHERE `passengers` >= $passengers AND `luggage_capacity` >= $luggage_capacity ORDER BY `passengers`, `luggage_capacity` LIMIT 1;

This will order the vehicles tables in ascending order first by passengers and secondly by luggage_capacity, select the first to fit and return only that, not all. If no result is found empty result set is returned. I tested the query and works like you asked.

Upvotes: 0

Professor Abronsius
Professor Abronsius

Reputation: 33823

# Run from the console

set @p=3;
set @l=5;

select * 
    from `vehicles` 
    where `passengers` >= @p and `luggage_capacity` >= @l
    order by `passengers`
    limit 1;


+----+---------+------------+------------------+
| id | name    | passengers | luggage_capacity |
+----+---------+------------+------------------+
|  1 | Saloon  |          4 |                5 |
+----+---------+------------+------------------+

Upvotes: 0

peixotorms
peixotorms

Reputation: 1283

you need to order and limit if you want only one vehicle:

SELECT name FROM vehicles WHERE passengers >= '$passengers' AND luggage_capacity >= '$luggage_capacity' ORDER BY passengers ASC, luggage_capacity ASC LIMIT 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271051

My guess is that the various capacity values in the database are being stored as strings rather than numbers. You could fix this in the query, using silent conversion:

SELECT name
FROM vehicles
WHERE passengers + 0 >= $passengers AND
      luggage_capacity + 0 >= $luggage_capacity;

If this is the problem, it would be better to fix the table:

alter table vehicles modify column passengers int;
alter table vehicles modify column luggage_capacity int;

Upvotes: 0

Related Questions