1

I have a database where one of the fields is for bathrooms and users usually input 1 or 3 or sometimes 1.1 which means 1 full bathroom and 1 half bathroom so I couldn't store this field as number, integer or double, or decimal. I had to store them as text.

My question is, how can I do this in a query if I wanted to pull only those records who's bathrooms is greater than 3 or something? I am looking for something like:

SELECT * FROM agentdb WHERE converttointeger(bathrooms) => 3

Obviously converttointeger is not valid but is there a way to achieve this? I tried searching and Googling but I keep getting the wrong results or wrong topic.

Improve this question
5
  • First, you shouldn't store data like this. Does CAST(bathrooms AS DECIMAL) helps? Commented Jul 4, 2016 at 2:43
  • SELECT * FROM agentdb WHERE CAST(bathrooms AS INT) >=3 ? Commented Jul 4, 2016 at 2:44
  • Thanks guys, I can't use decimal. Decimal reads 1.1 as 1 and 10% of 1. The users input 1.1 bathrooms meaning 1 and a half bathroom. This is already how they been doing it for years so I can't change that :( Commented Jul 4, 2016 at 3:10
  • Store it one way but display it another. Users won't see that secretly you're doing what makes sense and storing 1.1 as 1.5 Commented Jul 4, 2016 at 3:15
  • @Terminus that makes sense. I might just do it that way.:) Commented Jul 4, 2016 at 3:24

4 Answers 4

Reset to default
1

Don't use text converted to integers. Store the values as numbers with a decimal portions such as decimal. Then you can use the > comparison with no need for casting.

I would use DECIMAL(2,1), meaning 2 digits, including 1 decimal digit. "1.1" would be saved as 1.1, not 1.10

Alternative: You could also have two table fields: full_baths and half_baths, each of type tinyint or smallint. When your user enters "1.1" just split it before storing

$baths = $_POST['bathrooms'];// eg: "2.1"
$baths_arr = explode(".",$baths); // eg: [2,1]

$full = $half = 0; //full baths, half baths
if(!empty($baths_arr)):
    $full = (int)$baths_arr[0]; //we explicitly convert to integer
    $half = empty($baths_arr[1])? 0: (int)$baths_arr[1];
endif;

Then you can save $full and $half in their respective columns

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Because users input bathroom counts as "1.1" meaning 1 full bathroom and 1 half bathroom. If decimal, this will be read as "1.10" or 1 + 0.1. Therefore 1.1 + 1.1 should result to 3 but in Decimal it will result to 2.2
Does any part of your script require you to do 1.1 + 1.1 ? The only requirement you mentioned was how to compare two numbers to find which is greater (in your OP: bathrooms >= 3)
Sorry, my initial requirement was explained that I needed to compare a number stored as string as a number VS a number. Which is why I added that it should be something like "WHERE converttointeger(bathrooms) => 3" or semething that converts the bathrooms (text type) to integer to compare it to a number like 3 or 3.1
The solutions we gave you would allow you to make a comparison without worry so your initial requirement is met no? In your comment to my solution, you introduced the prospect of doing 1.1 + 1.1. My follow-up question was: When would you need to do that? (by the way I edited my answer with an alternative)
Yes somewhere along the process they will eventually be compared as 1.1 + 1.1 when I need to add the total bathrooms being offered per groups of rooms. I will try your solution sir :)
|
1

This should work: SELECT * FROM agentdb WHERE CAST(bathrooms AS SIGNED) >= 3

I got the answer from here: Cast from VARCHAR to INT - MySQL

Improve this answer

2 Comments

This worked, my solution was CAST(bathrooms AS DECIMAL(2,1)) >= ?
Sorry my bad, this has problems. 3.0 >= 3.1 is returning true
0

Store them as floats so that you can have decimals but keep them as numeric.

Improve this answer

1 Comment

Even better, store it as DECIMAL(3,1) or DECIMAL(2,1). You don't need FLOAT's precisions.
0

'bathrooms' data = { 1, 3, {3 < int}}

You need only 3 or 3 over integer. Conversely Think! You don't need '1' data. So...

SELECT * FROM agentdb WHERE bathrooms <> '1'

And, Casted columnn is not working index.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.