PHP MySql select to array

Question

I have working code which write result into array but values are doubled. Here is code:

<?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "databasename";
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    $index = array();
    $sql = "SELECT firstname, lastname, ID, parentID FROM table";
    $result = $conn->query($sql);

    while($row = $result->fetch_array()){
    $rows[] = $row;
    }

    // create an index on id
    foreach($rows as $row){
      $index[$row['ID']] = $row;
    }

    // build the tree
    foreach($index as $id => &$row){
      if ($id === 0) continue;
      $parent = $row['parentID'];
      $index[$parent]['children'][] = &$row;
    }
    unset($row);

    // obtain root node
    $index = $index[0]['children'];

    /* free result set */
    $result->close();
    /* close connection */
    $conn->close();

    // output json
    echo json_encode($index, JSON_PRETTY_PRINT);

but it produces JSON like this

[
  {
    "0": "Luka",
    "firstname": "Luka",
    "1": "Some",
    "lastname": "Some",
    "2": "287",
    "ID": "287",
    "3": "277",
    "parentID": "277"
},
{
    "0": "John",
    "firstname": "John",
    "1": "Test",
    "lastname": "Test",
    "2": "4080",
    "ID": "4080",
    "3": "277",
    "parentID": "277"
  }
]

Could someone tell me what is wrong with aboove PHP code so it does not produce double records in JSON. Thank you

Possible duplicate of double results in my array ( mysql_fetch_array ) — TopCheese
– TopCheese, Commented Mar 2, 2017 at 21:51

TopCheese · Accepted Answer · 2017-03-02 21:49:24Z

0

mysqli->fetch_array() uses MYSQLI_BOTH as default result type. Use the right paramater for the result you want.

mixed mysqli_fetch_array ( mysqli_result $result [, int $resulttype = MYSQLI_BOTH ] )

Result Type:

This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc(), while MYSQLI_NUM will behave identically to the mysqli_fetch_row() function. The final option MYSQLI_BOTH will create a single array with the attributes of both.

answered Mar 2, 2017 at 21:49

TopCheese

2201 silver badge8 bronze badges

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

Comments

beta-developper · Accepted Answer · 2017-03-02 21:49:39Z

0

Change

$row = $result->fetch_array()

To

$row = $result->fetch_array(MYSQLI_ASSOC)

answered Mar 2, 2017 at 21:49

beta-developper

1,7841 gold badge14 silver badges29 bronze badges

Collectives™ on Stack Overflow

PHP MySql select to array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related