Populating a search input box using PHP with values from a MySQL table

Question

Im currently using This JQuery as a search box on my website. The Javascript currently calls in values from a .txt file for the autocomplete results. However as I want my site to eventually have the option of the user adding these values, I'd like It to call in values from my database table instead.

JQuery on index page that calls the .txt file:

    $(document).ready(function(){                
        $("#select3").fcbkcomplete({
            json_url: "data.txt",
            addontab: true,                   
            maxitems: 10,
            input_min_size: 0,
            height: 10,
            cache: true,
            newel: true,
            select_all_text: "select",
        });
    });

Formatting of the .txt file:

[{"key": "hello world", "value": "hello world"}, {"key": "movies", "value": "movies"},

I thought the solution would be instead of calling data.txt, to call data.php and have this code pull in values:

$getData = mysql_query("SELECT Name FROM tblCocktails"); 

 while($info = mysql_fetch_array($getData)) 
 {
     echo "key: value:".$item['Name']"key: value:".$item['Name']';
 }

however this doesn't seem to work and my debugging on dreamweaver has decided to not work. Any help would be appreciated.

Try to look at your code a little more carefully. The php code you posted has numerous syntax errors. — Sam Dufel, Apr 17 '12 at 23:51
I managed to sort this out, I seperated the echo into seperate lines to find out the syntax errors and the php displays the values of my table in the correct format: $getData = mysql_query("SELECT Name FROM tblIngredient"); while($info = mysql_fetch_array($getData)) { echo "{\"key: value:\"; echo $info['Name']; echo "key: value:\""; echo $info['Name']; echo "}, "; } — MattHeywood, Apr 18 '12 at 14:46

score 2 · Answer 1 · answered Apr 18 '12 at 00:09

2

i would use json_encode() instead a hardcoded string

//if you want other field of the database like value, I mofidied your query for this
$getData = mysql_query("SELECT Name, Value FROM tblCocktails"); 
$index=0;
 while($info = mysql_fetch_array($getData)) 
 {
    //this will be storage every row of your table in an array until while is finish
    $value[$index]=new Array("key"=>$info['Name'],
                             "value"=>$info['Value']
                              );
    $index++;
 }
//convert the array into a json string
echo json_encode($value);

this should output something like you need

answered Apr 18 '12 at 00:09

Javier

55
3

1

You can simply that further by losing the `$index` variable and instead doing `$value[] = new Array...`. – Indrek Apr 18 '12 at 00:17
This threw up an error to do with the variables: "Parse error: syntax error, unexpected T_ARRAY, expecting T_STRING or T_VARIABLE or '$' in /home/ignitet1/public_html/WhatCocktail/data.php on line 12" – MattHeywood Apr 18 '12 at 00:34

score 1 · Accepted Answer · edited Apr 18 '12 at 00:00

1

In while loop look at the echo line. You should replace item with info.

Like this.

$getData = mysql_query("SELECT Name FROM tblCocktails"); 

 while($info = mysql_fetch_array($getData)) 
 {
     echo "key: value:".$info['Name']."key: value:".$info['Name'];
 }

edited Apr 18 '12 at 00:00

Indrek

867
8
27

answered Apr 17 '12 at 23:52

Jānis Gruzis

905
1
10
25

1

I fixed a couple other syntax errors, but the OP should really be using [json_encode()](http://www.php.net/manual/en/function.json-encode.php) rather than trying to put the data together manually. – Indrek Apr 17 '12 at 23:58

score 1 · Answer 3 · answered Apr 18 '12 at 00:08

1

Your response must be on json format, try this in your php file:

$getData = mysql_query("SELECT Name FROM tblCocktails"); 

$data = array();
while($info = mysql_fetch_assoc($getData)) 
{
   $data[] = $info['Name'];     
}
echo json_encode($data);//data array is converted in json format response

answered Apr 18 '12 at 00:08

luchopintado

899
11
15

I tried this and no errors are thrown up but the data.php file doesn't contain any database values so no autosuggest values are dropped-down. – MattHeywood Apr 18 '12 at 00:31

Populating a search input box using PHP with values from a MySQL table

3 Answers3

Linked