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.

Jin Pards
  • 189
  • 1
  • 2
  • 13
  • First, you shouldn't store data like this. Does `CAST(bathrooms AS DECIMAL)` helps? – Raptor Jul 04 '16 at 02:43
  • `SELECT * FROM agentdb WHERE CAST(bathrooms AS INT) >=3` ? –  Jul 04 '16 at 02: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 :( – Jin Pards Jul 04 '16 at 03: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` –  Jul 04 '16 at 03:15
  • @Terminus that makes sense. I might just do it that way.:) – Jin Pards Jul 04 '16 at 03:24

4 Answers4

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

BeetleJuice
  • 39,516
  • 19
  • 105
  • 165
  • 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 – Jin Pards Jul 04 '16 at 03:02
  • 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`) – BeetleJuice Jul 04 '16 at 03:07
  • 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 – Jin Pards Jul 04 '16 at 03:14
  • 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) – BeetleJuice Jul 04 '16 at 03:18
  • 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 :) – Jin Pards Jul 04 '16 at 03:23
  • If you expect to perform arithmetic operations (not just compare as in your initial Q), your best bet is to use two table fields, one for full and the other for half-baths as in my alternative. If you find it helpful, let me know with positive feedback. Good luck. – BeetleJuice Jul 04 '16 at 03:25
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

Community
  • 1
  • 1
Luis Fernando Babinski
  • 36
  • 4
0

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

Lifz
  • 690
  • 6
  • 10
  • 1
    Even better, store it as `DECIMAL(3,1)` or `DECIMAL(2,1)`. You don't need `FLOAT`'s precisions. – Raptor Jul 04 '16 at 02:44
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.

Andy Ryu
  • 79
  • 2