How to query a MSSQL database using a concatenated field

Is there anybody that can give advice on solving this issue that I am having. I am running PHP 5.3 with MSSQL. Just to explain what happens and what I need to be able to do...

The user selects a specific run (row) from a table on the home page. The columns in the table are:

• date
• division
• start mile/yard (in the format 565.1211 i.e. mile 565 and yard 1211) [this column is made up of a concatenation of two separate columns "mile" and "yard" from my database]
• end mile/yard
• start lat
• start long
• end lat
• end long
• total
• report available (yes or no)

The user can select a row by clicking on a cell in the report column where report=yes. This data is posted onto the next page. The next page allows the user to change the start and end mile/yard data so that they can see a specific section of that run.

For example the user has selected on the home page to view data from start mile/yard 565.1211 to end mile/yard 593.4321. The user can change the section that they want to see by typing into two text boxes. One box is a "start mile/yard" and the second box is "end mile/yard". So the user may type into the "start mile/yard" text box 570.2345 and "end mile/yard" 580.6543. What I want to happen is to query data from where the user has input...

SELECT     id, C开发者_如何转开发AST(mile AS varchar(6)) + '.' + CAST(yard AS varchar(6)) AS Mile, gps_lat, gps_long, rotten, split, broken, quality
FROM         table
WHERE mile/yard BETWEEN start mile ??? AND end mile ???
ORDER BY Mile

My problem is, how would I go about querying this information from my database when the user types in a combination of mile/yard (580.6543)? I assume that I will have to split the data into mile and yard again (How would I do this)... also How do retrieve the information? It would be simple to do just search by mile (e.g. WHERE mile BETWEEN 570 AND 580), but how do I search by yard and mile at the same time?

Unfortunately I will not be able to change the database structure as this is what I have to work with... If anyone can think of a better way of doing what I am doing... I am all ears!!

I understand this is a long question, so anything that is unclear, please let me know!

Cheers,

Neil

You shouldn't cast those mile/yard numbers to varchars. You lose the ability to compare them AS NUMBERS, which is what they were to start with.

Convert the user's mile/yard values to a number, then compare against those numbers in the database.

You're trying to force apples and grapes to be oranges, and comparing them to pears and plums... just make everything a pineapple, so to speak.

Besides, by doing the cast + concatenation, you lose any chance of ever possibly putting indexes on those fields. If your table grows "large", you'll kill performance by forcing full-table scans for every query.

As Marc B said I was trying to force apples and grapes to be oranges, and comparing them to pears and plums... just make everything a pineapple, so to speak. I have changed the structure of my script now.

Cheers,

Neil