Andrew Wilson Andrew Wilson - 3 months ago 18
PHP Question

PHP: Bootstrap, CodeIgniter - Typeahead data pulling from MySQL

I am attempting to query SQL to get an array of values to use for typeahead and I'm apparently missing the fundamentals of arrays because it's not giving me even close to the results that I expect

I want to gather the "Name" column from SQL for distinct data only

Here is my MySQL query

$data['typeahead'] = $this->db->query('SELECT DISTINCT Name from table')->result_array();


Here is the output it gives on var_dump

array(287) {
[0]=> array(1) { ["Name"]=> string(16) "'67 Shelby GT500" }
[1]=> array(1) { ["Name"]=> string(8) "Tooligan" }
[2]=> array(1) { ["Name"]=> string(24) "'67 Pontiac Firebird 400" }
[3]=> array(1) { ["Name"]=> string(17) "Volkswagen Beetle" }
}


The only thing I'm trying to do is build an array with the data from the columns. I'm using CodeIgniter for FrameWork and will be using Bootstrap's JS to work with the typeahead, but I'm stuck on the data array section.

Any help would be greatly appreciated, I've found numerous tutorials on how to use non MySQL arrays as typeahead, but none working with MySQL.

^^ Solved

Issue now:

Typeahead isn't pulling data from array

controller code:

$name_array = $this->db->query('SELECT DISTINCT Name from table')->result_array();

$typeahead_string = '';
foreach ($name_array as $name)
{
$formatted_name = '"' . $name['Name'] . '", ';
$typeahead_string .= $formatted_name;
}

$option_list = "[" . rtrim($typeahead_string, ", ") . "]";
$data['typeahead'] = $option_list;


View Code:

<input type="text" class="span3 search-query" placeholder="Search" id="typeahead" data-provide="typeahead" data-source="<?php echo $typeahead; ?>"><button type="submit" class="btn"><i class="icon-search"></i></button>

Answer

You could iterate through the results that your query is currently generating and add the Name elements to an new array, like this:

$name_array = $this->db->query('SELECT DISTINCT Name from HWC')->result_array();

$typeahead_array = array();
foreach ($name_array as $name)
{
    $typeahead_array[] = $name['Name'];
}

$data['typeahead'] = $typeahead_array;

Edit:

To use the typeahead functionality in Bootstrap, the data source can be a list of strings, for example: "Orange", "Apple", "Banana"

You could assign the $data['typeahead'] variable a string that is a list of options, pass it to the view and echo it to the relevant attribute.

Controller

The loop below will create a string variable that will contain all of the options for the typeahead. It adds quotation marks around each element and a comma at the end - so Apple would become "Apple",. It will then append each element to the string.

$name_array = $this->db->query('SELECT DISTINCT Name from HWC')->result_array();

$typeahead_string = '';
foreach ($name_array as $name)
{
    $formatted_name    =  '"' . $name['Name'] . '", ';
    $typeahead_string .= $formatted_name; 
}

$option_list = rtrim($typeahead_string, ",");  //Strips the last comma and any whitespace from the end string

$data['typeahead'] = $option_list;

View

Your input in the view should look something similar to this, the important part being where the option list is echoed: data-source="[<?php echo $typeahead; ?>]"

<input type="text" data-provide="typeahead" data-items="4" data-source="[<?php echo $typeahead; ?>]">

Hopefully this helps!