CODEWITHSUNDEEP
phpmysqldatabase
Imrul.H
Imrul.H

Reputation: 5870

MySQL search help

I am using php/mysql for a search. My table is 'height' and data type = varchar(10) which contains value like (5ft 2in, 5ft 3in,...and so on). While searching I got 2 values - height1 and height2 which are basically the ranges. How can i search in that table with the ranges? say - i will give ranges 5ft 1in to 5ft 10in and want to get the data between those values. I am using php. Please help me about this. thanks.

Upvotes: 0

Views: 556

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

It makes life complicated, but you can add a "0" into the "inch" portion whenever it is single digit. Given that you are storing height, let's ignore the case where the height goes into 10ft or more.

Sample data

create table height(height varchar(20) collate utf8_general_ci);
insert height select '5ft 10in';
insert height select '6ft 1in';
insert height select '7ft 10in';
insert height select '8ft 1in';
insert height select '6ft 11in';
insert height select '7ft 2in';

Select statement

select *
from height
cross join (select '5ft 9in' as low, '7ft 3in' as high) inputs
where
  case when height like '%ft _in' then
    concat(left(height, instr(height,'ft')+2), '0', right(height,3)) else height end
between
  case when low like '%ft _in' then
    concat(left(low, instr(low,'ft')+2), '0', right(low,3)) else low end
and
  case when high like '%ft _in' then
    concat(left(high, instr(high,'ft')+2), '0', right(high,3)) else high end

In essence, you plug the height1 and height2 inputs into this part

cross join (select '$height1' as low, '$height2' as high) inputs

Upvotes: 1

Phoenix
Phoenix

Reputation: 4536

You should really do as the others have said and store it as a single integer value (inches), so 5ft 10in is 70.

To display it as ft/in again, you just do something like this:

$result = 70; //Whatever is retrieved from the DB really, in inches
if($result >= 12) //If result is a foot or more
{
     $feet = floor($result / 12); //Divide the total inches by 12 and round down
     $inches = $result % 12; //Modulus total inches by twelve to get remainder inches
     $string = $feet . 'ft ' . $inches . 'in'; //Combine into string
}
else
{
     $string = $result . 'in'; //Unless you want it to display 0ft 7in or whatever if less than a foot
}

You could pretty easily convert all the values in the DB to an integer with a simple script that pulls the data in from the DB, splits the feet and inches apart, trims off the string part (which could be done just by casting to int once they're split apart), and then multiplies the numeric part of feet by 12 and adds it to the numeric part of inches and updates the DB.

Would look something like this:

$sql = mysql_query("SELECT `id`,`height` FROM `table` WHERE 1");
while($row = mysql_fetch_assoc($sql))
{
     $values = explode(" ",$row['height']); //Split it into two parts at the space between feet and inches
     $feet = (int)$values[0];  //Could also trim off the last 2 characters in the string
     $inches = (int)$values[1];
     $total = ($feet * 12) + $inches; //Of course, given order of operations, the parentheses aren't necessary, but makes it look nicer
     mysql_query("UPDATE `table` SET `height` = $total WHERE `id` = $row['id']");
}

Of course, what you'd want to do is make a new empty column on the table to hold the new integer values, like heightint or something, and then rename the old column to something else and then rename new column to height probably.

Okay, if you don't have the ability to modify the table (which you should really suggest such an improvement to whomever your client is), what you'll need to do, I think, is run a query of something like: 

SELECT * FROM `table` WHERE `height` >= '5ft 10in' AND `height` <= '5ft 1in'

But like that one person said, 5ft 10in would come before 5ft 1in in that data type, so it's gonna be tough to figure out which range should come first and which should come second if it's an arbitrary input, not even sure if that would catch all the values if the range was outside of a single foot.

Then, probably the easiest way to sort them would be to convert the height into total inches as above, and sort it by that in PHP using whichever array sorting function you want.

Upvotes: 0

pritam
pritam

Reputation: 1

Since you're using varchar() to store your height data, the height value is stored as a string. So to get the values between a certain range you must first sort the data. But since you're using varchar(), when you sort your column alphabetically, the value 5ft 10in comes before 5ft 1in.

Therefore i suggest you store the feet value and inches value as integers in 2 columns and then sort the data based on both columns giving higher priority to feet and lesser priority to inches.

Then while reading from your resultset, just concatenate the strings 'ft' and 'in' to your data.

Upvotes: 0

Sam Dufel
Sam Dufel

Reputation: 17598

I'd suggest you store the height values differently. If you store it as a float (5.1 feet, etc), or an int (63 inches, etc), you can easily compare the values. A string containing feet and inches will be much more difficult to parse with mysql.

For example:
SELECT * FROM height WHERE rowHeight BETWEEN 12 AND 20

Upvotes: 2

Related Questions