Page 1 of 1

Php/MySQL: query ranges, but ignore other chars?

Posted: Thu Aug 27, 2009 12:34 pm
by electroze
I have a mysql database with thousands of records, which each have an associated 'method number' ranging from 1 to 9,000. When someone clicks on the links for just the 200s, 300s, 1600s, etc. I want it to show only that grouping. BUT the problem is some the data has letters in it, like 232B, decimals, 200.1, spaces, hyphens, and other non-numerical characteristics. So, if I want to show only 200s for example, my MySQL command should search where method > 199 and <300, -BUT- ignore non-numerical characters, so it will do this:

Query 200s:
200
200A
200B
200.1
205A-modified
232B
239
290.5 C
etc

Anyone know how to get that result through a query + php?

Re: Php/MySQL: query ranges, but ignore other chars?

Posted: Thu Aug 27, 2009 7:54 pm
by AlanG

Code: Select all

<?php
$search = (isset($_GET['search']) && !empty($_GET['search']) && is_numeric($_GET['search'])) ? $_GET['search'] : ''; // index.php?search=200
 
if(!empty($search)) {
    $length = strlen($search);
    $search = $db->real_escape_string($search);
 
    $first = $search[0];
    $second = ($length > 1) ? $search[1] : '';
 
    $query = "SELECT method FROM mytable WHERE CHAR_LENGTH(method) >= '$length'";
    $query .= (!empty($second)) ? " AND method LIKE '".$first.$second."%'" : " AND method LIKE '$first%'";
 
    if($result = $db->query($query)) {
        // Mess with the results here
    }
}
?>
Haven't actually tested this so sorry if there's a syntax error or other silly mistake. Logic should be ok though. :)