Clever Geek Handbook

SQL Query to find matching values ​​based on user input

I am creating a website for real estate agents and tenants. Tenants can register and fill in their desired locations for properties, including street, city and zip code. As soon as they register, it automatically sends email agents that have properties that match these search criteria.

I currently have a query configured as follows to match either street, city, or postal code.

<%
Dim rspropertyresults
Dim rspropertyresults_numRows

Set rspropertyresults = Server.CreateObject("ADODB.Recordset")
rspropertyresults.ActiveConnection = MM_dbconn_STRING
rspropertyresults.Source = "SELECT * FROM VWTenantPropertiesResults "

'WHERE     (ContentStreet = 'Holderness Road') OR (ContentTown = 'Hull') OR (ContentPostCode = 'HU')

rspropertyresults.Source = rspropertyresults.Source& "WHERE (ContentStreet = '" & Replace(rspropertyresults__varReqStreet, "'", "''") & "'"

rspropertyresults.Source = rspropertyresults.Source& "OR ContentTown = '" & Replace(rspropertyresults__varReqTown, "'", "''") & "' "
rspropertyresults.Source = rspropertyresults.Source& "OR ContentTrimmedPostCode = '" & Replace(varPostcode, "'", "''") & "' ) "

rspropertyresults.Source = rspropertyresults.Source& "AND (( ContentBedRooms >= " & Replace(rspropertyresults__varBedroomsNoMin, "'", "''") & " "
rspropertyresults.Source = rspropertyresults.Source& "AND ContentBedRooms <= " & Replace(rspropertyresults__varBedroomsNoMax, "'", "''") & " ) "

rspropertyresults.Source = rspropertyresults.Source& "AND ( ContentPrice > = " & Replace(rspropertyresults__varPriceMin, "'", "''") & " "
rspropertyresults.Source = rspropertyresults.Source& "AND ContentPrice <= " & Replace(rspropertyresults__varPriceMax, "'", "''") & " )) " & varSQL & " "

rspropertyresults.Source = rspropertyresults.Source& "ORDER BY ContentPrice " & Replace(rspropertyresults__varSortWay, "'", "''") & " "

rspropertyresults.CursorType = 0
rspropertyresults.CursorLocation = 2
rspropertyresults.LockType = 1
rspropertyresults.Open()

rspropertyresults_numRows = 0
%>

However, the client asked that instead of just matching one of the values, it should work in such a way that, if speaking to the address "Street and City", send an email to this property agent, or if "City" and " Zip "match, this property agent.

As you can imagine, I think the query will get quite complex, but I'm not sure how to best design such a query.

I wondered if anyone could help or point me in the right direction?

+1


source to share


4 answers 


SELECT  *
FROM    (
        SELECT  id
        FROM    (
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentStreet = 'Holderness Road'
                UNION ALL
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentTown = 'Hull'
                UNION ALL
                SELECT  id
                FROM    VWTenantPropertiesResults
                WHERE   ContentPostCode = 'HU'
                ) qi
        GROUP BY
                id
        HAVING  COUNT(*) >= 2
        ) q
JOIN    VWTenantPropertiesResults r
ON      r.id = q.id
WHERE   ContentBedrooms BETWEEN 1 AND 4
        AND ContentPrice BETWEEN 50 AND 500
ORDER BY
        ContentPrice

This will return you all records that match the conditions at least 2

.

This solution is indexing: unlike proposals, OR

it will use indexes on ContentStreet

, ContentTown

and ContentPostCode

.

See this blog post for details on performance:



For maximum performance and safety, replace the overridden parameter values ​​with associated parameters.

This will save time on parsing queries and protect you from SQL

injection.

+4


source


Including SQL on your website is a bad practice IMO. But I am not familiar with asp-classic. Also the way you do it, you are threatened by SQL injection. You should not mix user interface and data access logic.



Have a look at http://en.wikipedia.org/wiki/SQL_injection

+3


source


It would be nice to create a SQL Stored Procedure to handle the logic you described. In ASP code, you can call this procedure with user-supplied parameters. This avoids the problems with dynamic SQL generation and also alleviates this problem.

See http://authors.aspalliance.com/stevesmith/articles/sprocs.asp for some examples of using ADODB with stored procedures.

+1


source


The approach I have used in a similar situation is to use LIKE, not column = value, then you can use any values ​​in any combination of fields. For example:

WHERE LIKE ('%' + @town + '%') and zip LIKE ('%' + @zip '%') AND street LIKE ('%' + @street '%') Etc.

It wouldn't matter then, only they filled in some of the fields, but will still return valid results. The basic approach to this approach is that all fields must be string values, since LIKE does not work on numeric columns. Thus, you will need to do casting on numeric fields, which can lead to some things depending on how much conversion needs to be done in order for it to give and perceive the situation.

I also agree that this should indeed be done in a stored procedure passing in parameters to find fields.

0


source






More articles:


All Articles