1

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>
Andrew
  • 4,443
  • 5
  • 33
  • 75
  • That's not an array, that's an object that represents a query result. I know nothing about CI but I'm sure the documentation explains how to make a database query. – Álvaro González Sep 01 '12 at 19:32
  • @ÁlvaroG.Vicario you were correct, I added ->result_array() and now i'm getting an array inside an array. I just need to figure out how to remove the first array completely – Andrew Sep 01 '12 at 19:46
  • You could try and see what `row_array()` returns instead `result_array()`, or get your array data by using `$newArray = $oldArray[0];`. This simply puts into `$newArray` a first element of `$oldArray` (that first element happens to be an array too). – Slavenko Miljic Sep 01 '12 at 20:13
  • row_array gives 1 entry so does $data['typeahead'] = $temp['0']; – Andrew Sep 01 '12 at 20:34

2 Answers2

2

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!

jleft
  • 3,457
  • 1
  • 23
  • 35
  • Perfect! Now I just have to figure out how to make an array variable work with typeahead. Any thoughts there? – Andrew Sep 01 '12 at 22:43
  • Hi, @AndrewWilson, I've updated my answer so it should now work with Bootstrap's typeahead. – jleft Sep 01 '12 at 23:21
  • with that I get Message: implode(): Invalid arguments passed – Andrew Sep 02 '12 at 01:01
  • Oops, using `rtrim($typeahead_string, ",")` instead (as shown in the edit above), should work. – jleft Sep 02 '12 at 01:19
  • the array looks correct with rtrim($typeahead_string, ", "), but it still won't call from typeahead correctly – Andrew Sep 02 '12 at 01:23
  • What do you mean by won't call correctly? Won't call at all? Or something else? If you edit your question to include the code for the view then that'd be useful :) – jleft Sep 02 '12 at 01:36
  • Got it working with JS Thank you!!!! @Lefters – Andrew Sep 02 '12 at 01:46
  • Also, if you think that there might be a lot of possible options for the typeahead, then it would be worth looking into an Ajax call that would populate the dropdown as it should be more efficient. – jleft Sep 02 '12 at 01:56
  • There will probably be around 11,000 entries. I haven't touched AJAX at all, but I know I'll need to with this project. Would it be hard to convert it to AJAX? – Andrew Sep 03 '12 at 16:50
  • I don't have that much experience with AJAX but I think it'd be fairly easy, [this](http://stackoverflow.com/questions/9232748/twitter-bootstrap-typeahead-ajax-example) should help. – jleft Sep 03 '12 at 17:01
0

still doesn't work for me. when I inspect elements the data like this " uncategories","php" . what means the first is always spasing.

found the way instead using this $formatted_name = '"' . $name['Name'] . '", '; just change to $formatted_name = '&quot;' . $name['Name'] . '&quot;, ';

nady saptra
  • 39
  • 1
  • 6