I have a real estate website where people can search property based on Location, Property Type and Builder.

I have a Table like below.

CREATE TABLE Project(ProjectId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
                     Location  VARCHAR(255), 
                     PropertyType VARCHAR(255), 
                     Builder VARCHAR(255), 
                     ProjectName  VARCHAR(255),
                     Status TINYINT)
                     
                     
INSERT INTO Project(Location, PropertyType, Builder, ProjectName)
             VALUES('Location A', 'Flats',     'Builder A', 'Project A', 1),
                   ('Location A', 'Villas',    'Builder B', 'Project B', 1),
                   ('Location B', 'Flats',     'Builder A', 'Project C', 1),
                   ('Location C', 'Villas',    'Builder C', 'Project D', 1),
                   ('Location B', 'Plots',     'Builder B', 'Project E', 1),
                   ('Location A', 'Row House', 'Builder C', 'Project F', 1),
                   ('Location A', 'Plots',     'Builder A', 'Project G', 1),
                   ('Location C', 'Plots',     'Builder C', 'Project H', 1),
                   ('Location C', 'Flats',     'Builder B', 'Project I', 1),
                   ('Location C', 'Villas',    'Builder B', 'Project J', 1),
                   ('Location A', 'Villas',    'Builder A', 'Project K', 1),
                   ('Location C', 'Flats',     'Builder B', 'Project L', 1);

The search procedure which I use is in such a way that It brings back search result as per the parameters they selected i.e Property Type, Location, Builder Name

Now there may be projects for some search parameters as below

Flats by Builder A at Location C

I want to change procedure in such a way the parameters in filters in where clause should change to bring result all the time by considering other possibilities like for the above

Flats by Builder A at Location C

There is no Flats by Builder A at Location C so it should display Flats at Location C by Other Builders

Location C   Flats     Builder B    Project I

The Filter should Consider PropertyType, Location and Builder Name

Villas at Location B

There is No Villas at Location B So

Location A    Villas   Builder B   Project B
Location C    Villas   Builder B   Project J
Location A    Villas   Builder A   Project K

The search should happen by

PropertyType -> Location -> Builder

If there is No project for a particular Builder in that Location then

PropertyType -> Location

If there is No project for a particular Location then

PropertyType

The parameters in the filters should be eliminated in such a way it brings similar result to search param.

       PropertyType -> Location -> Builder       No Records
       PropertyType -> Location                  No Records
       PropertyType                              Records Found

Solution

DROP PROCEDURE  IF EXISTS getProjectResult;
CREATE PROCEDURE getProjectResult(IN PropertyType VARCHAR(255), IN Location VARCHAR(255), IN BuilderName VARCHAR(255))
BEGIN
  DROP TABLE IF EXISTS tblTempProjIds;
  CREATE TEMPORARY TABLE tblTempProjIds(ProjId INT, Points INT);
  
  SET @PropertyTypePoints = 0;
  SET @LocationPoints     = 0;
  SET @BuilderName        = 0;
  
  IF PropertyType  '' THEN
    SET @PropertyTypePoints = 5;
  END IF;
  
  IF Location  '' THEN
    SET @LocationPoints = 3;
  END IF;
  
  IF BuilderName  '' THEN
    SET @BuilderName = 1;
  END IF;
  
  SET @BestScore = @PropertyTypePoints + @LocationPoints + @BuilderName;
  
  SET @strSQL ='INSERT INTO tblTempProjIds(ProjId, Points) SELECT ProjectId,
                       CASE WHEN PropertyType= ? then 5 else 0 end + 
                       CASE WHEN Location = ? then 3 else 0 end +  
                       CASE WHEN Builder = ? then 1 else 0 end as score
                  FROM project
                 ORDER BY score DESC';
                   
  SET @PropertyType = PropertyType;
  SET @Location     = Location;
  SET @BuilderName  = BuilderName;
  
  PREPARE stmt FROM @strSQL;
  EXECUTE stmt USING @PropertyType, @Location, @BuilderName;  
  DEALLOCATE PREPARE stmt; 
  
  #SELECT * FROM tblTempProjIds;
  
  SET @strSQLCount ='SELECT @Num := MAX(Points) 
                       FROM tblTempProjIds';
                      
                 
  PREPARE stmt FROM @strSQLCount;
  EXECUTE stmt;  
  DEALLOCATE PREPARE stmt;  
  
  
  CASE @Num 
     WHEN 9 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '" AND Builder="', BuilderName, '"');     
     WHEN 8 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '"');     
     WHEN 6 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Builder ="', BuilderName, '"'); 
     WHEN 5 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '"'); 
     WHEN 4 THEN SET @Where = CONCAT(' AND Location="', Location, '" AND Builder ="', BuilderName, '"');
     WHEN 3 THEN SET @Where = CONCAT(' AND Location="', Location, '"');
     WHEN 1 THEN SET @Where = CONCAT(' AND Builder="', BuilderName, '"'); 
     WHEN 0 THEN SET @Where = '';
   END CASE;
   
   
    SET @FinalSQL =    CONCAT('SELECT * 
                                 FROM project
                                WHERE status = 1 ', @Where);
                            
    PREPARE stmt FROM @FinalSQL;
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;             
END;