How can i query mysql with just numbers when a user may type in a suffix?

I have a function below which works perfectly, but now the client came back and asked that the number only be taken to do the search because most of his clients won't type in the suffix "h" or whatever it may be as per my example below:

38039 or 38039h

However he also said he only has one group of product codes which begin with "T" so they could be typing in "T760" in which case we would need the prefix.

My code below does a search on the exact product currently, can anyone help me work in these examples?

&l开发者_StackOverflow中文版t;?php 

//Find Stock Value
function checkstock($prodCode) { 

  $prodCode = strtoupper($prodCode);

  require '../../../../config.php';
  $dbh = new PDO(DB_DSN, DB_USER, DB_PASS);
  $sql = "SELECT * FROM isproducts WHERE prodCode = '".
         $prodCode."' AND AllowSalesOrder = '1'"; 
  $stmt = $dbh->query($sql);
  $obj = $stmt->fetch(PDO::FETCH_OBJ);

  $count = $stmt->rowCount();

  echo ($count == 1 ? 
   ROUND($obj->FreeStockQuantity, 0) : 'Invalid product code '.$prodCode.'');   

}

//Call Stock Function
checkstock($_POST['productcode']);

?>

---

Change the query to like below ?

SELECT * FROM isproducts 
WHERE 
  (
    prodCode='{$prodCode}'          // for product with prefix or suffix
    OR prodCode LIKE '{$prodCode}%' // without suffix
    OR prodCode='T{$prodCode}'      // without prefix
  )
  AND AllowSalesOrder = ''"; 

Wild-card by single character

 OR prodCode LIKE '{$prodCode}_' // single character wild-card

---

It seems that you may have products with the same number but not the same suffix? like 8512n and 8512h ? You could use LIKE '%$code%'

$sql = "SELECT * FROM isproducts WHERE prodCode LIKE '%".$prodCode."%' AND AllowSalesOrder = ''";

and I think its the more secure way in your case, so that all products containing the number will appear, regardless of suffix or prefix.

The above may return more than one product, so the user still has to choose which one it is he is actually looking for.

---

You can use % wild card for this kind of problem.

---

check this out....

http://www.w3schools.com/SQL/sql_wildcards.asp

it might be useful... :-)

---

In some of my code I use the following strategy:

# psuedo-code ... NOT intended for real use:
SELECT COUNT(*) FROM someTable WHERE someColumn = "{XXX}"
# If that returns exactly one than use the corresponding query
SELECT COUNT(*) FROM someTable WHERE someColumn LIKE "{XXX}"
# If that returns exactly one then use it
SELECT COUNT(*) FROM someTable WHERE someColumn LIKE "{XXX}%"
# If that returns exactly one then use it
SELECT COUNT(*) FROM someTable WHERE someColumn LIKE "%{XXX}%"
# If that returns exactly one then use it

... (where {XXX} is the placeholder for the user supplied search term).

The idea here is that I first try a precise match, then I try it under the assumption that the term already may contain SQL wildcards, then I try suffixing the % wild card and finally I try wrapping it with % wild cards.

At any point if I've found an unambiguous match then I use it. If I find more than one match at any point (not shown in the psuedo-code here) then I might throw an exception or I might return them or a subset of them based on the specifics of what I'm doing.

(In reality I'm using the parameter interpolation features of Python or Perl or sanitizing my inputs to allow wild cards while preventing SQL injections; so the code doesn't look like what I'm showing here. This is just to convey the general idea).

My goal is to allow my scripts to be called with the minimum unambiguous arguments supplied which sounds roughly similar to what your clients are requesting here.

From a usability perspective most users will get the first characters of any input right. So exact match following by suffixed wildcard match is most likely to succeed most of the time. In my case my users are likely to be familiar with SQL wildcards and may prefer to use them to construct their own unambiguous match; and logically that attempt has to be inserted before I start suffixing or wrapping it with my own wildcards.

This is why I use this specific sequence of matching attempts.