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

Question

I've got a database that contains two tables, both tables are containing postcodes i.e. "SY25 6WB" I've built a query that will query the database for a postcode entered in a textbox, if the postcode is in the database, it does some jquery - all works fine,

what i'm aiming to do is, have the query run first based on postcode, then if that's not found, search the next table for the postcode IF the dropdown is populated with a certain value. I'm also looking for a way to ignore whitespace, so if a user was to enter "SY256WB" or "SY25 6WB" it would pull the same result.

I've pasted my 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 your full postcode with 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 * FROM cscopc WHERE POSTCODE LIKE '".$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 * FROM cscowb WHERE PCODE LIKE '".$term."'"; 
    $nextab = mysql_query($sqltwo);
        if ( mysql_num_rows($nextab) > 0 ) {
        echo 'YES WITH OPTION';
    }
        else { 
    echo 'NO';
     }
}
}
?>

If anyone could shed some light on how i could do this or point me in the right direction for some reference, it would be much appreciated!

UPDATED with 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 * FROM cscopc WHERE 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 * FROM cscowb WHERE POSTCODDE = :id');
            $stmtwb->execute(array('id' => $id));
                if ($row = $stmtwb->fetch(PDO::FETCH_OBJ)) {
                echo 'YEP';
            }

    }
        else {
        echo 'nope';
        }
    }

unless you want to remove continuing spaces as well, then you'll need some regex — , Jun 02 '14 at 17:59

score 2 · Accepted Answer · edited May 23 '17 at 12:05

Assuming the postcodes in your database are correctly formatted (i.e. containing the spaces), then you'll need to do some validation of the user input (this is a good idea anyway).

There are examples of validation here: United Kingdom (GB) postal code validation without regex

Once you've validated the user input, you can easily format the postcode so that you can look it up in your database. Here's a simple example:

// remove all spaces and upper case
$userInput = strtoupper(preg_replace('/\s+/', '', $_REQUEST['term']));

if (isValidPostcode($userInput)) {
    // Basic postcode formatting - add a space before the last 3 characters
    $formatted = substr($userInput, 0, -3) . ' ' . substr($userInput, -3);

    // now look up in db using $formatted
} else {
    // invalid postcode!
}

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

// create a regex for your lookup: turns 'SA13XL' into 'SA1\s*3XL'
$regex = sprintf('%s\s*%s', substr($userInput, 0, -3), substr($userInput, -3));

// now look up using the regex
$statement = $db->prepare('SELECT * FROM cscopc WHERE POSTCODE REGEXP :postcode');
$statement->bindParam(':postcode', $regex);

Note: the above example uses PDO, which you should really look at using, since the mysql_ functions are deprecated. See this question: Why shouldn't I use mysql_* functions in PHP?

Thanks for the info! I'm reading a lot about PDO this evening, but i'm having trouble understanding it completely, being quite new to PHP/MYSQL All the examples/learning process i've followed so far seems to have been depreciated - I'll continue my research and report back. if you have anywhere I can find good examples of using PDO over mysql they would be appreciated. — fish_r, Jun 03 '14 at 00:28
I seem to have wrapped my head around the PDO (i hope!) Will move on to the validation shortly. I'll post my updated code here for reference and any other viewers having similar issues, also if you could just let me know i've used best practice etc as this is all new to me — fish_r, Jun 03 '14 at 03:18
Looks good. If you combine your updated PDO changes with the logic for creating the `$formatted` postcode above, you should be on to a winner :-) — cmbuckley, Jun 03 '14 at 08:38

score 0 · Answer 2 · answered Jun 03 '14 at 04:05

You can remove every occurence of a space(' ') using the str_replace or preg_replace functions. I would prefer you to use the str_replace function. Here I have put some examples for you for help. Hope this will solve your query.

<?php
$str1="SY25 6WB";
$new=str_replace(' ', '', $str1);
echo "<b>Case I:</b><br>Old String : $str1<br>New String : $new";

$str2="    SY25 6WB   ";
$new=str_replace(' ', '', $str2);
echo "<br><b>Case II:</b><br>Old String : $str2<br>New String : $new";

$str3="SY256WB";
$new=str_replace(' ', '', $str3);
echo "<br><b>Case III:</b><br>Old String : $str3<br>New String : $new";

$str4="   SY256WB    ";
$new=str_replace(' ', '', $str4);
echo "<br><b>Case IV:</b><br>Old String : $str4<br>New String : $new";
?>

/*The output will be -

Case I:
Old String : SY25 6WB
New String : SY256WB
Case II:
Old String : SY25 6WB
New String : SY256WB
Case III:
Old String : SY256WB
New String : SY256WB
Case IV:
Old String : SY256WB
New String : SY256WB

*/

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

2 Answers2