[SOLVED] best way of storing/ merging values for query between calculation

[pixelgirl]

Hello, I have a question about what is the best angle to go from in regard to running a particular query on my database.

 

I have four fields: age(years) age(months) height(hands) height(inches)

 

originally I had the two age and height fields combined as decimal fields, but I seperated them to make it easier to display data on a webpage.

 

However, now I want to run a query to find values between the two ages and the two heights. Is the best thing to combine them again in the database as decimal values, changing my structure again so I have to re-input all the data I had in there.

 

Or is there some way to combine the two heights and two ages at run time to include both parts of each so I can run calculations on them?

 

Any advice would be appreciated

 

An example of my sql is below for clarity:

 

Bob = SELECT h.horseID, h.name, h.heightH, h.heightI, h.breed, h.price, h.colour, h.ageY, h.ageM, h.sex, hi.thumbName FROM horse h, horseimages hi 
WHERE h.horseID = hi.horseID AND hi.thumbName is NOT NULL AND h.heightH BETWEEN '11' and '13' 
AND h.price BETWEEN '1500' and '5000' AND h.ageY BETWEEN '5' and '10'

 

[friedemann_bach]

I think it would be easier if you combined both age and height values in one field, and split the values up only for displaying. Handling will be much more flexible if you convert all values to your smallest unit. I would advise storing the age in total months, as the height in total inches.

 

Manual re-inputting wouldn't be necessary, as rebuilding the age and height fields can be done automatically: Set up the fields age and height again, then execute the following query:

 

UPDATE horses SET age = age_years*12+age_months, height = height_hands*4 + height_inches

 

When you've set up the new field again, you can delete or rename the other columns.

 

For outputting, you can use something like:

 

SELECT floor(age/12) AS age_years, (age-floor(age/12)) AS age_months FROM horses

 

to get the age split up in months and years again. Hope this helps!

 

[pixelgirl]

thanks, I didnt think of doing that.

 

I may be being really stupid but what is the 'floor' bit for (or is it to set a base or floor for the ranges?)

[pixelgirl]

never mind, google is my friend

 

Thanks for the help!

[friedemann_bach]

You're welcome.

 

[pixelgirl]

Further to the posts above, I now want to query the data.

 

I am trying to get something like the query below, but get an error: #1054 - Unknown column 'height_hands' in 'where clause'

 

surely the whole point is to be able to query the created field name? Ive tried to find out more about this but cant find anything.  If anyone knows any more advanced tutorials etc that can tell me more about complex queries I would be grateful as I dont want to post about every query I have

 

Unless the whole point is to just use the created field name to output data, not to query the created fields themselves?

 

Again any help appreciated

 

SELECT h.horseID, h.name, h.height, floor(h.height/4) AS height_hands, (h.height -((floor(h.height/4))*4)) AS height_inches,
h.breed, h.price, h.colour, h.age, floor(h.age/12) AS age_years, (h.age -((floor(h.age/12))*12)) AS age_months, h.sex, 
hi.thumbName FROM horse h, horseimages hi WHERE h.horseID = hi.horseID AND hi.thumbName is NOT NULL 
AND height_hands BETWEEN 14 and 17 AND h.price BETWEEN 1000 and 15000 
AND age_years BETWEEN 5 and 15;

[friedemann_bach]

A simple solution would be

 

... AND floor(h.height/4) BETWEEN 14 and 17 ...

 

(replace "height_hands" by your formula - I think height_hands can only be used for output, but I am not sure about that)

 

I would make the query more simple by expressing all height values in inches (" ... height BETWEEN 56 AND 68 ... "). I recommend that you work completely with inch values while you are on the programming and querying level. Only for user input and output the values should be converted from or into other units.

 

Supposing that you use a HTML form evaluated by PHP, proceed like this:

 

$min_height_inches = $_POST['min_height_hands'] * 4;
$max_height_inches = $_POST['max_height_hands'] * 4;
...
mysql_query(" ... height BETWEEN $min_height_inches AND $max_height_inches ... ");

 

Have fun!