I have a real estate website where people can search property based on Location, Property Type and Builder.<\/p>\n
I have a Table like below.<\/p>\n
\r\nCREATE TABLE Project(ProjectId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, \r\n Location VARCHAR(255), \r\n PropertyType VARCHAR(255), \r\n Builder VARCHAR(255), \r\n ProjectName VARCHAR(255),\r\n Status TINYINT)\r\n \r\n \r\nINSERT INTO Project(Location, PropertyType, Builder, ProjectName)\r\n VALUES('Location A', 'Flats', 'Builder A', 'Project A', 1),\r\n ('Location A', 'Villas', 'Builder B', 'Project B', 1),\r\n ('Location B', 'Flats', 'Builder A', 'Project C', 1),\r\n ('Location C', 'Villas', 'Builder C', 'Project D', 1),\r\n ('Location B', 'Plots', 'Builder B', 'Project E', 1),\r\n ('Location A', 'Row House', 'Builder C', 'Project F', 1),\r\n ('Location A', 'Plots', 'Builder A', 'Project G', 1),\r\n ('Location C', 'Plots', 'Builder C', 'Project H', 1),\r\n ('Location C', 'Flats', 'Builder B', 'Project I', 1),\r\n ('Location C', 'Villas', 'Builder B', 'Project J', 1),\r\n ('Location A', 'Villas', 'Builder A', 'Project K', 1),\r\n ('Location C', 'Flats', 'Builder B', 'Project L', 1);\r\n<\/pre>\nThe 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<\/p>\n
Now there may be projects for some search parameters as below<\/p>\n
Flats by Builder A at Location C <\/p>\n
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 <\/p>\n
Flats by Builder A at Location C <\/p>\n
There is no Flats by Builder A at Location C so it should display Flats at Location C by Other Builders<\/p>\n
\r\nLocation C Flats Builder B Project I\r\n<\/pre>\nThe Filter should Consider PropertyType, Location and Builder Name<\/p>\n
Villas at Location B <\/p>\n
There is No Villas at Location B So <\/p>\n
\r\nLocation A Villas Builder B Project B\r\nLocation C Villas Builder B Project J\r\nLocation A Villas Builder A Project K\r\n<\/pre>\nThe search should happen by <\/p>\n
\r\nPropertyType -> Location -> Builder\r\n<\/pre>\nIf there is No project for a particular Builder in that Location then <\/p>\n
\r\nPropertyType -> Location\r\n<\/pre>\nIf there is No project for a particular Location then <\/p>\n
\r\nPropertyType\r\n<\/pre>\nThe parameters in the filters should be eliminated in such a way it brings similar result to search param.<\/p>\n
\r\n\r\n PropertyType -> Location -> Builder No Records\r\n PropertyType -> Location No Records\r\n PropertyType Records Found\r\n\r\n<\/pre>\nSolution<\/b><\/p>\n\r\nDROP PROCEDURE IF EXISTS getProjectResult;\r\nCREATE PROCEDURE getProjectResult(IN PropertyType VARCHAR(255), IN Location VARCHAR(255), IN BuilderName VARCHAR(255))\r\nBEGIN\r\n DROP TABLE IF EXISTS tblTempProjIds;\r\n CREATE TEMPORARY TABLE tblTempProjIds(ProjId INT, Points INT);\r\n \r\n SET @PropertyTypePoints = 0;\r\n SET @LocationPoints = 0;\r\n SET @BuilderName = 0;\r\n \r\n IF PropertyType '' THEN\r\n SET @PropertyTypePoints = 5;\r\n END IF;\r\n \r\n IF Location '' THEN\r\n SET @LocationPoints = 3;\r\n END IF;\r\n \r\n IF BuilderName '' THEN\r\n SET @BuilderName = 1;\r\n END IF;\r\n \r\n SET @BestScore = @PropertyTypePoints + @LocationPoints + @BuilderName;\r\n \r\n SET @strSQL ='INSERT INTO tblTempProjIds(ProjId, Points) SELECT ProjectId,\r\n CASE WHEN PropertyType= ? then 5 else 0 end + \r\n CASE WHEN Location = ? then 3 else 0 end + \r\n CASE WHEN Builder = ? then 1 else 0 end as score\r\n FROM project\r\n ORDER BY score DESC';\r\n \r\n SET @PropertyType = PropertyType;\r\n SET @Location = Location;\r\n SET @BuilderName = BuilderName;\r\n \r\n PREPARE stmt FROM @strSQL;\r\n EXECUTE stmt USING @PropertyType, @Location, @BuilderName; \r\n DEALLOCATE PREPARE stmt; \r\n \r\n #SELECT * FROM tblTempProjIds;\r\n \r\n SET @strSQLCount ='SELECT @Num := MAX(Points) \r\n FROM tblTempProjIds';\r\n \r\n \r\n PREPARE stmt FROM @strSQLCount;\r\n EXECUTE stmt; \r\n DEALLOCATE PREPARE stmt; \r\n \r\n \r\n CASE @Num \r\n WHEN 9 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '" AND Builder="', BuilderName, '"'); \r\n WHEN 8 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '"'); \r\n WHEN 6 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Builder ="', BuilderName, '"'); \r\n WHEN 5 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '"'); \r\n WHEN 4 THEN SET @Where = CONCAT(' AND Location="', Location, '" AND Builder ="', BuilderName, '"');\r\n WHEN 3 THEN SET @Where = CONCAT(' AND Location="', Location, '"');\r\n WHEN 1 THEN SET @Where = CONCAT(' AND Builder="', BuilderName, '"'); \r\n WHEN 0 THEN SET @Where = '';\r\n END CASE;\r\n \r\n \r\n SET @FinalSQL = CONCAT('SELECT * \r\n FROM project\r\n WHERE status = 1 ', @Where);\r\n \r\n PREPARE stmt FROM @FinalSQL;\r\n EXECUTE stmt; \r\n DEALLOCATE PREPARE stmt; \r\nEND;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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… Continue reading