JSON and array structure typeahead

Question

I am trying to create a json file from an sql query, and search this json using twitter typeahead. However the json format doesn't look correct.

The json needs to be in a certain format for typeahead like below;

['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California' ...];

However my json is in the following format;

["{\"title\":\"Item 1\"}","{\"title\":\"Item 2\"}","{\"title\":\"Item 3\"}"

Newbie to php/sql/json I'm sure there is something really obvious I'm missing or doing wrong. Maybe I should be using a foreach and not while? I am able to echo out the $titles so I now the query is working.

If somebody cold point me in the right direction I would appreciate it.

My code so far;

$sql = ("SELECT title FROM publication");

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

$data = array();

while($row = $result->fetch_assoc()){
    $data[] = json_encode($row);
    $titles = json_encode($data);
    echo $titles;//for testing
}

file_put_contents('titles.json', $titles);

deceze · Accepted Answer · 2015-07-03 10:32:32Z

1

You're over-encoding your data and
you're not including the data you actually want.

Put the data you want into the array and JSON-encode the whole thing only at the end:

while ($row = $result->fetch_assoc()) {
    $data[] = $row['title'];
}

file_put_contents('titles.json', json_encode($data));

answered Jul 3, 2015 at 10:32

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jonboy Over a year ago

thanks for this @deceze however nothing is being written to my titles.json file whereas it was before, it's empty whenever I open it. Whenever I echo json_encode($data); it looks good though so that's a start!

Ashish Choudhary · Accepted Answer · 2015-07-03 10:32:21Z

1

You are performing json_encode twice which should not be the case.

Instead the Code should be like below:

$data[] = $row;
$titles = json_encode($data);

or simply

$titles = json_encode($row);

answered Jul 3, 2015 at 10:32

Ashish Choudhary

2,0341 gold badge20 silver badges26 bronze badges

Comments

PHP Worm... · Accepted Answer · 2015-07-03 10:44:29Z

0

Use json_encode once

    $data[] = $row; /*$data[] = json_encode($row);*/

and write this:

 $titles = json_encode($data);

OR

$titles = json_encode(array_values($data));  /*You may need to use this to get exact output*/

After while loop

edited Jul 3, 2015 at 10:44

answered Jul 3, 2015 at 10:37

PHP Worm...

4,2341 gold badge28 silver badges48 bronze badges

Comments

Toumash · Accepted Answer · 2015-07-03 10:34:46Z

-1

You are inserting associative array to your json array('title'=>'sometitle') but you only need that title.
The solution is to only save the title value from the db resulting row to the array:

while($row = $result->fetch_assoc()){
    $data[] = $row['title'];
    echo json_encode($data); // dont encode twice
}
file_put_contents('titles.json', json_encode($data));

edited Jul 3, 2015 at 10:34

answered Jul 3, 2015 at 10:29

Toumash

1,08712 silver badges28 bronze badges

Collectives™ on Stack Overflow

JSON and array structure typeahead

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related