topic Re: sql Address Select Statement in ArcGIS JavaScript Maps SDK Questions

sql Address Select Statement

MeganWirth — Sun, 28 May 2017 14:45:50 GMT

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.

Re: sql Address Select Statement

JoshuaBixby — Sun, 28 May 2017 15:18:04 GMT

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

Re: sql Address Select Statement

MirHashmi — Mon, 29 May 2017 06:52:48 GMT

Hi,

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

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

Re: sql Address Select Statement

MeganWirth — Mon, 29 May 2017 13:34:10 GMT

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';
}

Re: sql Address Select Statement

JoshuaBixby — Mon, 29 May 2017 20:11:26 GMT

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.

Re: sql Address Select Statement

MirHashmi — Tue, 30 May 2017 07:03:55 GMT

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

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

Re: sql Address Select Statement

MeganWirth — Tue, 30 May 2017 17:02:03 GMT

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

Re: sql Address Select Statement

MirHashmi — Wed, 31 May 2017 07:22:43 GMT

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.

Re: sql Address Select Statement

MeganWirth — Wed, 31 May 2017 20:02:24 GMT

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?

Re: sql Address Select Statement

MirHashmi — Sun, 12 Dec 2021 02:50:32 GMT

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.

Re: sql Address Select Statement

MeganWirth — Thu, 01 Jun 2017 14:51:45 GMT

Thank you so much sir! with a slight modification it is now up and running!

$search = "509 BERRY CT"; changed to $search=$_POST['search']; in order for the user defined form entry to work.

My only issue now is to be able to return the DOCUMENTS field as a hyperlink with a friendly name.
echo $results['PERMIT DOCUMENT'];
Thanks again mirhashmi2014‌ and bixb0012‌ greatly appreciate your efforts.

Re: sql Address Select Statement

MirHashmi — Sun, 12 Dec 2021 02:50:35 GMT

I think something like below should work for you.

// define friendly name
$permitDocument = $results['PERMIT DOCUMENT'];
if(strcasecmp($permitDocument, '123456' )==0)
{
   $friendlyName = "ABC";
}

echo "<a href='".$results['PERMIT DOCUMENT']."'>‍‍‍‍‍‍‍‍‍".$friendlyName."</a>"‍‍‍‍‍‍‍‍