sql Address Select Statement

[MeganWirth]

Need help with a sql statement used to select a specific address. For example I have a database named musickey_TESTER, and a table named PERMITMASTER. I would like to select an exact record from the ADDRESS field. i.e 509 BERRY CT. I have tried many statements but can only get it to work searching on the address number. 

Here is what my statement looks like. 

"select * from PERMITMASTER where ADDRESS LIKE '%$search%' "

This produces no results however when I input just the numeric part of the address it will return any address with that number in the string.

Thanks in advance for any help.

[JoshuaBixby]

It is helpful to post the SQL you have tried, and what the results are compared to what you are expecting.

[MirHashmi]

Hi,

If target is an exact record did you try using the Equal operator.

select * from PERMITMASTER where ADDRESS='509 BERRY CT'

[MeganWirth]

Thanks for taking a look. Yes I have tried that. The $search in the statement is tied to a user defined form field.

Here is the entire php code.

// Search from MySQL database table
$search=$_POST['search'];
$query = $pdo->prepare("select * from PERMITMASTER where ADDRESS LIKE '%$search%' ");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
// Display search result
if (!$query->rowCount() == 0) {
echo "Search found :<br/>";
echo "<table style=\"font-family:arial;color:#333333;\">";
echo "<tr><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">ADDRESS</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">PERMIT TYPE</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">PERMIT DOCUMENT</td></tr>";
while ($results = $query->fetch()) {
echo "<tr><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
echo $results['ADDRESS'];
echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
echo $results['PERMIT TYPE'];
echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
echo $results['PERMIT DOCUMENT'];
echo "</td></tr>";
}
echo "</table>";
} else {
echo 'Nothing found';
}

[JoshuaBixby]

I don't work with MySQL much, or PHP at all, but I work with other SQL variants a fair amount.  Are you sure your variable substitution is correct?  To me, it looks like you are searching on $search, the actual text, and not the value behind the variable.  What about if you change the SQL line to read:

$query = $pdo->prepare("select * from PERMITMASTER where ADDRESS LIKE '%" + $search + "%' ");‍

Also, what about capitalization?  You could UCASE both the search condition and field values to eliminate any case sensitivity.

[MirHashmi]

Hi,
Just to add a little more to @JoshuaBixby's reply. Instead of "+" sign use "." which is used for string concatenation in php. 

'%".$search."%'"‍

[MeganWirth]

Thanks you guys very much for taking a look! I modified  bixb0012 statement to include your recommendations. 

"select * from PERMITMASTER where ADDRESS LIKE '%" . $search . "%' "

I still get no results when entering 509 BERRY CT. However I can search BERRY CT and get results. It appears to be the the space after the numeric value in the address that is bombing it?

Here is a link to the actual page so you can take a look. thanks again guys! 

www.keytek.co/search-form.php

[MirHashmi]

I've tested through your page.  Right now what is happening is that the query is kind of looking for the whole term "509 BERRY CT" but trying to maintain the position of each word within it.  Instead if we break this whole term into words and send to the query it will return the results wherever it finds each word regardless of its position.  So in your backend page check for spaces for the input and substitute those with "%" and send to query. 

For example: 509%BERRY%CT

  

This above screen shot from your page returned me results with the address having 509.

[MeganWirth]

Thanks for the reply Mir! We are so close. Is there a way the user can enter a literal string 509 BERRY CT in the search without having to enter a wildcard?

[MirHashmi · Accepted solution]

This wildcard need not be entered by the end user.  Below are the steps you will have to implement.

1. User submits the literal string eg."509 BERRY CT" to your php page.

2. In php page break this string into multiple words.

3. Parse multiple words with wildcard character.

4. Send this new parsed string to the query. 

The code could be something like below.

$search = "509 BERRY CT";
$multipleWords = explode(" ",$search);
$parsedTerm = "%";

foreach($multipleWords as $word){
  $parsedTerm .= $word."%";  
}

$query = $pdo->prepare("select * from PERMITMASTER where ADDRESS LIKE '".$parsedTerm."'");‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Here is a php sandbox link with a working snippet for this case.