1,105,281 Community Members

Autocomplete jquery PHP Access Database

Member Avatar
brenton_77
Newbie Poster
21 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi All,

I've hit a brick wall trying to get the jquery autocomplete to work correctly. The problem is with the .php file and the way the While loop works, as what it does is create a array for every name rather than one single array. I've tried to move the 'echo json_encode' outside of the loop but then it only returns the very last value.

So question is how do I make the While function build an array of every distinct name in the database then echo as json_encode.

Thanks

CODE:

<?php
$conn = new COM("ADODB.Connection") or die("Cannot start ADO"); 

$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\\Admin.mdb");

$queryString = $_GET['term'];

$query = "SELECT DISTINCT [PersonNameFirst] FROM Person WHERE PersonNameFirst like '$queryString%'";

$result = $conn->Execute($query);

while (!$result->EOF) 
    {
        $fz = $result->Fields("PersonNameFirst");
        $array = array($fz->value);
        $result->MoveNext();
        $output = $array;
        echo json_encode($output);  
    }

?>
Member Avatar
radhakrishna.p
Posting Whiz in Training
281 posts since Nov 2012
Reputation Points: 29 [?]
Q&As Helped to Solve: 64 [?]
Skill Endorsements: 11 [?]
 
0
 

at line 15 you are creating a new array for each and every record thats giving the invalid output to you

move that declarion out side of the while loop and make changes as follows

$my_array = array();
$rec_count =0;
while (!$result->EOF) 
    {
        $fz = $result->Fields("PersonNameFirst");
        $my_array[$rec_count] = $fz->value ;
        $result->MoveNext();        
        $rec_count++;
    }
echo json_encode($my_array);  

please check it once
let me know the status

Member Avatar
brenton_77
Newbie Poster
21 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi radhakrishna,

That worked perfectly I am now seeing a single array of results. Just one more question

On the line "$my_array[$rec_count] = $fv->value ' ' $fz->value" how do I get it join first name and lastname together with a space in between e.g. "John Smith"? json complain about my formatting.

Thanks again.

$query = "SELECT DISTINCT [PersonNameLast], [PersonNameFirst] FROM Person WHERE PersonNameFirst like '$queryString%'";

$result = $conn->Execute($query);

$my_array = array();
$rec_count = 0;

while (!$result->EOF) 
    {
        $fv = $result->Fields("PersonNameFirst");
        $fz = $result->Fields("PersonNameLast");
        $my_array[$rec_count] = $fv->value ' ' $fz->value;
        $result->MoveNext();
        $rec_count++;

    }
echo json_encode($my_array);    
Member Avatar
radhakrishna.p
Posting Whiz in Training
281 posts since Nov 2012
Reputation Points: 29 [?]
Q&As Helped to Solve: 64 [?]
Skill Endorsements: 11 [?]
 
0
 

replace this $my_array[$rec_count] = $fv->value ' ' $fz->value;
with the following

 $my_array[$rec_count] = $fv->value.' '.$fz->value;

in php '.' is used for concatenating operations

please check it
and let me know the status

happy coding

Member Avatar
brenton_77
Newbie Poster
21 posts since Jun 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

It worked. Thanks again for your help

Question Answered as of 1 Year Ago by radhakrishna.p
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: