SQL/Wordpress/AJAX : Format JSON With Loop

Question

PROBLEM

I'm trying to query my database (wp_usermeta table) and export it in a JSON format to be processed by an autocomplete plugin. I need the data to be formatted as such:

{"suggestion":"copmany1", "umeta_id":"1"},{"suggestion":"company2", "umeta_id":"2"}, etc.

SO FAR

My current code outputs the info as such:

{"suggestions":["concept9 test","Company"],"data":["58","77"]}

This is my code:

$query = $_GET["query"];



    // escape values passed to db to avoid sql-injection
    $query = $wpdb->get_results( "SELECT DISTINCT umeta_id, meta_value FROM wp_usermeta WHERE meta_key='company' AND meta_value LIKE '".$query."%' order by umeta_id" );

    $suggestions = array();

    foreach($query as $row) {
        $suggestions[] = $row->meta_value;
        $data[] = $row->umeta_id;

        $response1 = array(
        'suggestions' => $suggestions,
        'data' => $data,
    );
    }


    $response = json_encode( $response1 );
    echo $response;
    exit();

Thanks!

user4754887 · Accepted Answer · 2016-03-01 14:09:37Z

2

Try it like this:

$query = $_GET["query"];
// escape values passed to db to avoid sql-injection
$query = $wpdb->get_results( "SELECT DISTINCT umeta_id, meta_value FROM wp_usermeta WHERE meta_key='company' AND meta_value LIKE '".$query."%' order by umeta_id" );
$suggestions = array();
foreach($query as $row) {
    array_push($suggestions, array(
            'suggestion' => $row->meta_value,
            'umeta_id' => $row->umeta_id
        )
    );
}
echo json_encode( $suggestions );
exit();

Comment here if you need further explanation

answered Mar 1, 2016 at 14:09

user4754887

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

8 Comments

Dan Over a year ago

Hi @Sasha89 - thanks for the suggestion! Unfortunately, this yields null. This is maybe because the foreach loop will go through the queries, not the companies. Perhaps we need another loop inside it since the wp_usermeta table is formatted like this: codex.wordpress.org/Database_Description#Table:_wp_usermeta ? thanks!

user4754887 Over a year ago

can you add this line inside foreach and post results: var_dump($row); die;

Dan Over a year ago

this is what i get object(stdClass)#451 (2) { ["umeta_id"]=> string(2) "58" ["meta_value"]=> string(13) "concept9 test" }

user4754887 Over a year ago

That is what i expected, so im not sure why you get "null"

Dan Over a year ago

oh god, you are completely right..Was outputting the old array. it should work now! i get [{"suggestion":"concept9 test","umeta_id":"58"},{"suggestion":"Company","umeta_id":"77"}] - thanks so much!

|

Collectives™ on Stack Overflow

SQL/Wordpress/AJAX : Format JSON With Loop

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related