mysql - PHP Query based on text input and dropdown option - and ignoring whitespace? -

i've got database contains 2 tables, both tables containing postcodes i.e. "sy25 6wb" i've built query query database postcode entered in textbox, if postcode in database, jquery - works fine,

what i'm aiming is, have query run first based on postcode, if that's not found, search next table postcode if dropdown populated value. i'm looking way ignore whitespace, if user enter "sy256wb" or "sy25 6wb" pull same result.

i've pasted code below.

form:

<form action="" method="post">   search: <input type="text" name="term" />   <select name="options">   <option value="yes">yes</option>   <option value="no">no</option> </select> <p>enter full postcode spaces i.e. sa1 3xl</p> <br /> <input type="submit" value="submit" />   </form>   

php

<?php if (!empty($_request['term'])) { $term = mysql_real_escape_string($_request['term']);     $benefit = mysql_real_escape_string($_request['yes']);      $sql = "select * cscopc postcode '".$term."'";  $r_query = mysql_query($sql); if ( mysql_num_rows($r_query) > 0 ) {     echo 'yes'; } else if ( mysql_num_rows($r_query) == 0 ) {     $sqltwo = "select * cscowb pcode '".$term."'";      $nextab = mysql_query($sqltwo);         if ( mysql_num_rows($nextab) > 0 ) {         echo 'yes option';     }         else {      echo 'no';      } } } ?> 

if shed light on how or point me in right direction reference, appreciated!

updated new query using pdo:

        try {             $conn = new pdo('mysql:host=localhost;dbname=cscoapp', $username, $password);             $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception);                 }          catch(pdoexception $e) {             echo 'error: ' . $e->getmessage();         }         $id = $_post['term'];         if (!empty($_request['term'])) {             $stmt = $conn->prepare('select * cscopc postcode = :id');             $stmt->execute(array('id' => $id));                 if ($row = $stmt->fetch(pdo::fetch_obj)) {                     echo 'yep';                 }         else if ($_post['benefits'] == 'yes') {              $stmtwb = $conn->prepare('select * cscowb postcodde = :id');             $stmtwb->execute(array('id' => $id));                 if ($row = $stmtwb->fetch(pdo::fetch_obj)) {                 echo 'yep';             }      }         else {         echo 'nope';         }     } 

assuming postcodes in database correctly formatted (i.e. containing spaces), you'll need validation of user input (this idea anyway).

there examples of validation here: united kingdom (gb) postal code validation without regex

once you've validated user input, can format postcode can in database. here's simple example:

// remove spaces , upper case $userinput = strtoupper(preg_replace('/\s+/', '', $_request['term']));  if (isvalidpostcode($userinput)) {     // basic postcode formatting - add space before last 3 characters     $formatted = substr($userinput, 0, -3) . ' ' . substr($userinput, -3);      // in db using $formatted } else {     // invalid postcode! } 

however, if data in database isn't clean, you'll need work out way either (a) improve data quality, or (b) search table using regular expression:

// create regex lookup: turns 'sa13xl' 'sa1\s*3xl' $regex = sprintf('%s\s*%s', substr($userinput, 0, -3), substr($userinput, -3));  // using regex $statement = $db->prepare('select * cscopc postcode regexp :postcode'); $statement->bindparam(':postcode', $regex); 

note: above example uses pdo, should really @ using, since mysql_ functions deprecated. see question: why shouldn't use mysql_* functions in php?