Dynamic Search with PHP, jQuery and AutoComplete

After I created a Cron job using EasyCron to populate my cryptocurrency coin history tables, my next step was to create the capability to search through the 500 coins I had in the database.  The To The Moon! Coin Calculator displays the Top 50 coins by market capitalization rank by default. The search would narrow it down to one coin. I am hoping, that in the next phase, that when the user selects one coin they will get more information than the list of 50.

Since I want to prevent errors in retrieving the one coin, the best method is to reduce the choices of the user.  However, instead of showing a long list of 500 coins, the preferred method is to give a text field and let the user narrow down the choices by typing portions of the word into the box.  To do this, we need a text field, a action call and a source of the data.

The text field is easy enough, but since we are using Bootstrap to handled things, we want to make sure that the field isn’t too large.  I used the ‘col-sm-4’ class to keep the text field just big enough to handle the names of the crypto coins.

<input id="coinSearch" name="coinSearch" type="text" class="form-control input-group-sm col-sm-4" placeholder="Search for coins...." />

The next part is to set a listener on the input file so that an action is triggered.  The listener that I used is ‘autocomplete‘, which is available with the jQuery UI script library. In particular, you need to use at a minimum the 1.12.1 version, because earlier versions cause an error.

$('#coinSearch').autocomplete({
	source: 'data/getCoinSearch.php',
	minLength: 0
});

The only parameter that you need to add is the php filename that will have the source output. I would prefer to point to a php function, but I don’t think that would work correctly.  When the php file is used, the url parameter is passed and can be found using the $_GET predefined parameters function.  The predefined parameter passed from the autocomplete input field is “term”.  I set the minLength to 2, so that it would take at least 2 characters before the php file was called to get the source data.

isset($_GET['term'])

Which brings us to the source code.  Since we are calling a php file, we don’t need a function, but we do need to check to see if the term is there. If it is there, it is put in to a parameter and the sql statement is built, executed and then outputted.

$searchTerm = $_GET['term'];
//echo 'search: ' . $searchTerm;
// create database connection
$query = $conn->query("SELECT `ID`, `Name` FROM `coin` WHERE `Name` LIKE '%".$searchTerm."%' ORDER BY `Name` ASC");
//loop through coin table results
while ($row = mysqli_fetch_assoc($query)) {
   $data[] = array('value'=>$row['ID'],'label'=>$row['Name'] . ' (' . $row['Symbol'] . ')');
}
//return json data
echo json_encode($data);

The output created the value and the label, so when the user types, they see the coin name, but when they select it and search, it uses the shorter coin ID created on Cryptocurrency Market Capitalizations.

Once the user selects a coin, then the function that populates the table is called again from the To The Moon! button, but checks to see if the URL now has the search value.  If the value exists, it checks against the coin ID to only show that coin.

// check for search value and create sql
if(isset($_GET['coinSearch']) && strlen($_GET['coinSearch']) > 0){
   $searchCoin = $_GET['coinSearch'];
   $sqlCoinSupply = "SELECT mh.`Rank`, c.`Name`, c.`Symbol`, mh.`ID`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`, MAX(mh.`RowAdded`) FROM `market_history` AS mh, `coin` c WHERE c.`ID` = mh.`ID` AND c.`ID` = '" . $searchCoin . "' GROUP BY mh.`ID`";
} else {
    $sqlCoinSupply = "SELECT mh.`Rank`, c.`Name`, c.`Symbol`, mh.`ID`, mh.`Price_usd`, mh.`Price_btc`, mh.`SupplyAvailable`, mh.`SupplyTotal`, mh.`SupplyMax`, MAX(mh.`RowAdded`) FROM `market_history` AS mh, `coin` c WHERE c.`ID` = mh.`ID` GROUP BY mh.`ID` ORDER BY mh.`Rank` LIMIT 50";
}

There is a way to choose multiple coins, so I might decide to use that, but if the user only selects one coin, to display other information. I might have to figure out something so that the user can use the coin symbol to search on as well, but for now I’ve added the symbol to the coin name display.

I also added coin icons from github.com/cjdowner. While there are 2,000 icons, not every cryptocurrency is available. So, in order not to display an image without a source, I am doing a file check before and then adding the text for an image.

// check for icon and add if it exists
$iconFile = 'images/icons/32/white/' . strtolower($row["Symbol"]) . '.png';
$iconFileURL = '';
if(file_exists($iconFile)){
	$iconFileURL = '<img src="' . $iconFile .'" border="0" />&nbsp;';
}

echo '<td>' . $iconFileURL .  $row["Name"] . '(' . $row["Symbol"] . ')</td>';

Since $iconFileURL is set to blank before the check and the full HTML tag is added within the check, if there isn’t an icon, then nothing is added to the display output.