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?
Php/MySQL: query ranges, but ignore other chars?
Moderator: General Moderators
Re: Php/MySQL: query ranges, but ignore other chars?
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
}
}
?>