在PHP文件中创建的名称 - 值和键 - 值对（用于json编码）

Question

I am generating PHP arrays from a MySQL database, with the output to be converted to JSON using json_encode (ultimately for use with Twitter typeahead).

Based upon everything I've read about PHP arrays, I had expected the select query to generate - for any record - just a single key-value pair for each column.

Instead, I seem to be getting both a name-value pair and a key-value pair.

For instance, for a table with 5 columns, the set of values FOR A SINGLE RECORD are in the form:

[fieldname1] => value1 [0] => value1
[fieldname2] => value2  [1] => value2
[fieldname3]] => value3 [2] => value3 
[fieldname4]=> value4  [3] => value4 
[fieldname5] => value5 [4] => value5

When I use json_encode it generates the following JSON string for that record - which is twice as long as it needs to be because I am getting both the name-value pair and the key-value pair (so the the value is repeated very line).

"fieldname1":"value1","0":"value1",
"fieldname2":"value2","1":"value2",
"fieldname3":"value3","2":"value3",
"fieldname4":"value4","3":"value4",
"fieldname5":"value5","4":"value5",

What I would prefer is just the 'fieldname1' => 'value1' or its json equivalent.

Here is an example of the code I use in PHP to select the data from the database.

<?php 
$sql = "SELECT * FROM `tr_trades_anzsco` WHERE `trade_alias_id` > 898";
$trade = $dbh->query($sql);

    foreach($trade as $trade):  
            $trade_alias_id = $trade['trade_alias_id'];
            $trade_alias = trim($trade['trade_alias']);
            print_r($trade);
    endforeach; 
?>

Here is the resultant dataset for just three records.

Array ( [trade_alias_id] => 899 [0] => 899 [trade_alias] => Boilermaker [1] => Boilermaker [ANZSCO_title] => Structural Steel And Welding Trades Workers [2] => Structural Steel And Welding Trades Workers [ANZSCO_code] => 322300 [3] => 322300 [source] => Raj [4] => Raj ) Array ( [trade_alias_id] => 900 [0] => 900 [trade_alias] => Welder [1] => Welder [ANZSCO_title] => Structural Steel And Welding Trades Workers [2] => Structural Steel And Welding Trades Workers [ANZSCO_code] => 322300 [3] => 322300 [source] => Tom [4] => Tom ) Array ( [trade_alias_id] => 901 [0] => 901 [trade_alias] => Rigger [1] => Rigger [ANZSCO_title] => Construction Rigger [2] => Construction Rigger [ANZSCO_code] => 821711 [3] => 821711 [source] => Jack [4] => Jack )

Finally, here is the json string for the final record only (broken up so the structure is more easily visible).

Array ( [0] => Array (
[trade_alias_id] => 901 [0] => 901
[trade_alias] => Rigger [1] => Rigger 
[ANZSCO_title] => Construction Rigger [2] => Construction Rigger 
[ANZSCO_code] => 821711 [3] => 821711 
[source] => Jack [4] => Jack )
)

The duplicate values matter considerably for performance reasons - because the data is to be extracted for use on mobile devices (with Twitter TypeAhead and possibly AngulularJS). Our datasets are already very large ... so twice the json will double the time for fetching the data.

Key question: How I can remove the duplicates either at the PHP stage OR the json_encode stage so as to generate ONLY a name-value pair but not both a name-value and a key-value pair?

Thank you!

Answer 1

Presuming you are using pdo:

$trade = $dbh->query($sql, PDO::FETCH_ASSOC);