MySQL / PHP - you need to be able to create query results with certain columns that have more weight than others

Question

I was instructed to create a search function, which when searching, some fields will have more weight than others.

Here is a simplified example.

cars (table)
year, make, model, color, type (columns)

Say someone is looking for the following:

Year: 1968
Make: Ford
Model: Mustang
Color: Red
Type: Sports car

If cars do not have a single correct field in the table, they should not be displayed, but if there are some correct fields in the record, but not all of them should appear. But some fields must be weighted above others.

For example, perhaps they are weighted as follows:
Column - Weight
Year - 30
Make - 100
Model - 85
Color - 10
Type - 50

So, if the record matches the search in the "make" field and the "model" field, this record will be higher than the record corresponding in the "year", "color" and "type" field, due to the weight that we put in each column.

So, let's say that the query corresponds to at least one field for two records in the database, they need to be sorted by the most relevant by weight:

1971, Ford, Fairlane, Blue, Sports Car (weight = 185)
1968, Dodge, Charger, Red, Sports Car (weight = 90).

I am trying to figure out how to do this. If someone did something similar, please give me an idea on how to make it work.

MySQL , , , PHP. .

Answer 1

, , , , .

SELECT SUM(
  IF(year = "1968", 30, 0) +
  IF(make = "Ford", 100, 0) +
  IF(model = "Mustang", 85, 0) +
  IF(color = "Red", 10, 0) +
  IF(type = "Sports Car", 50, 0)
) AS `weight`, cars.* FROM cars 
  WHERE year = "1968" 
  OR make = "Ford"
  OR model = "Mustang"
  OR color = "Red"
  OR type = "Sports Car"
GROUP BY cars.id
ORDER BY `weight` DESC;

, ( SUM(), , - . , .

, , - .

:

+============================================================+
| weight | year | make      | model    | color  | type       |
|============================================================|
| 130    | 1968 | Ford      | Fairlane | Blue   | Roadster   |
| 100    | 2014 | Ford      | Taurus   | Silver | Sedan      |
| 60     | 2015 | Chevrolet | Corvette | Red    | Sports Car |
+============================================================+

, , , Ford (+100), 1968 (+30) (10 + 50), ( )

, (.. 0), WHERE ... OR ..., . !

, LEFT JOIN :

SELECT SUM(
  IF(cars.year = "1968", 30, 0) +
  IF(cars.make = "Ford", 100, 0) +
  IF(cars.model = "Mustang", 85, 0) +
  IF(cars.color = "Red", 10, 0) +
  IF(types.name = "Sports Car", 50, 0)
) AS `weight`, cars.*, types.* FROM cars 
LEFT JOIN cars_types ON cars_types.car_id = cars.id
LEFT JOIN types ON cars_types.type_id = types.id
  WHERE year = "1968" 
  OR cars.make = "Ford"
  OR cars.model = "Mustang"
  OR cars.color = "Red"
  OR types.name = "Sports Car"
GROUP BY cars.id
ORDER BY `weight` DESC;

LEFT JOIN:

, () () (85 + 10), (Sports Car) (50). , , Dodge Caliber - , , , . , !

Answer 2

, - PHP . :

SELECT Year,Make,Model,Color,Type 
FROM table 
WHERE year='$postedyear' OR make='$postedmake' 
   OR model='$postedmodel' OR color='$postedcolor'

php- :

foreach($results as $result){
    $score = 0;
    if($result['year']==$postedyear{$score=$score+30;}
    //continue with the other with the same logic.
}

foreach $score . $result, .

Answer 3

@lelio-faieta​​p >

php , , , , . . (, array_intersect_assoc()) , . .

Answer 4

, mysql-, . PHP , ​​ .

, : " SphinxSearch"

, , mysql .. . , , mysql.