MySQL/PHP - Filter on numeric values on fields that are not numeric

Question

Basically, the idea is that people filter search results on the length. However, length is stored in a text field, as this can be different from item to item. An item could be 16,20 metres, and another could be 8.1m.

How would one even start? Get all the possible values from the database, change them to the format that is filtered on (parse everything to numeric only, comma separated?), get the associated ID, and then only show all the info related to those IDs, or is there a way I haven't found yet?

Kind regards.

edit: Standardizing format is a solution, but not one I can apply in this situation. I am not allowed to do this. It gets worse - the filtering can have both a minimum and a maximum value. Minimum: 4 and maximum: 8 should show everything between those lengths. 6.1m, 7,82 metres, 5.

You should normalize the data which means maybe splitting up value and measuring unit into two fields. — frlan
– frlan, Commented Jan 6, 2014 at 9:11
I really should, and I really wish I could. However, this is a project of someone else, and I both can't and am not allowed to change it, sadly. — Mave
– Mave, Commented Jan 6, 2014 at 9:12
You don't have to change the core table, but you could create a mirror table that does it properly, and search on the mirror table. The mirror table can be recreated from scratch every hour/day (depending on size and update frequency) or, if you have an updated_at column in your broken table, you can keep it nearly identically synced every 10 minutes. — halfer
– halfer, Commented Jan 6, 2014 at 10:22

Mave · Accepted Answer · 2014-01-06 09:57:59Z

Because I couldn't change the way the database was set up (standardize is, keep separate fields for the length itself, a float/double, and a field for the appendix), I've decided to go with this approach.

First get all the possible lengths, then:

foreach($lengths as $length) {
 $cLengths[$length['item_id']] = preg_replace("/[^0-9,.]/", "", str_replace(',', '.', $length['field_value']));
}

Assuming the page would then be called with &minLength=2&maxLength=10 in the URL:

$minLength = $_GET['minLength'];
$maxLength = $_GET['maxLength'];
if(!is_null($minLength) && !is_null($maxLength)) {
 $filteredItems = '';
 foreach($cLengths as $itemId => $cL) {
  if($cL >= $minLength && $cL <= $maxLength) {
   $filteredItems .= $itemId . ',';
  }
 }
 $filteredItems = substr($filteredItems, 0, -1);
 $where .= 'item_id IN(' . $filteredItems . ') AND ';
}

flu · Accepted Answer · 2014-01-06 09:35:26Z

0

I would recommend to standardize a format for length. It is hard to filter numeric values stored in different formats.

You can use the LIKE operator to search for any pattern in a string:

SELECT * FROM table WHERE length LIKE '%filtervalue%'

edited Jan 6, 2014 at 9:35

flu

14.8k8 gold badges78 silver badges73 bronze badges

answered Jan 6, 2014 at 9:19

kormik

8678 silver badges18 bronze badges

3 Comments

Mave Over a year ago

It gets worse. It has to have both a minimum value and a maximum value. One of those could be empty, both of those could be empty, both of those could be initialized.

kormik Over a year ago

Definitely you have to normalize format. You need to write a function that can do it (parse input according to all possible values and return normalized (float) value). Better is use this function before saving value to DB and store value normalized (you can add new column to table in case you also need original value). Than you can easily filter values using SQL. If you can't modify DB, you have to select all rows and filter it in PHP (using same function as before) but this will lead to performance issues .

Mave Over a year ago

As explained in my edit and as response to the other answer -- I wish I could, but I can't. That's out of the question. Believe me, that's the first thing I jumped to, but no can do.

Bartek Ferek · Accepted Answer · 2014-01-06 10:28:51Z

0

I would simply enforce standard during your query. Assuming your length column is named "length" and you query table is "table":

SELECT something, replace(length,',','.') + 0 as mylen FROM `table` HAVING mylen BETWEEN 16.2 AND 18

It would not be very fast, you can also use replace + 0 directly in where, it would remove the need for HAVING that is:

SELECT something FROM `table` replace(length,',','.') + 0 BETWEEN 16.2 AND 18

Good Luck!

answered Jan 6, 2014 at 10:28

Bartek Ferek

1

Collectives™ on Stack Overflow

MySQL/PHP - Filter on numeric values on fields that are not numeric

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related