Welcome to WebmasterWorld Guest from 50.19.34.255

Forum Moderators: open

Searching a VARCHAR like an INTEGER

     
1:22 am on Jun 13, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


I have a field that's VARCHAR(30). It's a price field, where the user can enter whatever they want. So in the database, I might have anything like (but not limited to) the following:

NULL (no price entered)
$3,000
300
300.00
$300.00
$300-$400.00
$300 obo
Best offer
$300 each, $500 for both
$300 or trade
Trade for guns or $300
I don't know, around 300 or so (I'm not joking, I have something like this)
three hundred dollars

I just print the field to the screen so it's worked just fine for about 7 years, but now I'm wishing that I'd limited it to numbers. Because I would like to offer a search option letting the user search for items between $_GET['low'] and $_GET['high'].

A normal query would look like:

sprintf(<<<EOF
SELECT * FROM table WHERE
price >= %s AND
price <= %2
EOF,
mysqli_real_escape_string($_GET['low']),
mysqli_real_escape_string($_GET['high']));

But how do I make a logical modification to this query to make it only recognize the numbers in price?

I guess, realistically, I would need it to remove commas (eg, 3,000), then ignore any non-numeric character before the first number, then ignore everything after the second non-numeric character?

I don't know how to do this in MySQL at all, so right now I'm thinking about doinging the query without the price query, then in PHP loop through and convert each price to a number and compare it.

Something like:

while($row = mysqli_fetch_row($sth)) {
if ($_GET['low'] || $_GET['high']) {
list($id, $username, $price) = $row;

$price = str_replace(',', '', $price);
$price = preg_replace('#^[^0-9]+#', '', $price);
$price = preg_replace('#([0-9]+).*#', '$1', $price);

if ($price >= $_GET['low'] && $price <= $_GET['high'])
array_push($arr, $row);
}

else array_push($arr, $row);
}


Thoughts?
8:27 am on June 27, 2017 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3355
votes: 39


i'd change the structure of the database, to have 3 fields instead of one.

price, comment

price should be integer or decimal field (if you need decimal points)
comment shoulds be a varchar - where they can write what they want. (eg. trade, ono, 300 each 400 for both)
tell them that unless they enter a price then their item may not be found in a price search.

you will have to convert all the old entries over .... many should be possible programatically by running queries, others may have to be done manually.

it sounds like a hassle but is the best thing to do in the long run.
2:20 am on June 30, 2017 (gmt 0)

Preferred Member

Top Contributors Of The Month

joined:Mar 15, 2013
posts: 570
votes: 47


Topr8, I think I might be leaning that direction, too. It's gonna be a HUGE pain, but probably best in the long run :'-(