php - Query to Json: Return Each Column Twice with Different Names

196

I have this php code to get all payments (payment amount, the name of the user, the month of the payment) deposited by all users in all months:

$result = $mysqli->query("SELECT p.id as id, p.amount as amount, 
                          u.name as user_id, m.name as month_id 
                          FROM payment p, user u, month m 
                          WHERE p.user_id = u.id AND p.month_id = m.id;");   

//Add all records to an array
$rows = array();
while($row = $result->fetch_array())
{
    $rows[] = $row;
}

//Return result
$jTableResult = array();
$jTableResult['Result'] = "OK";
$jTableResult['Records'] = $rows;
print json_encode($jTableResult);

And this is the json I get:

[{
  "Result": "OK",
  "Records": [
    {
      "0": "1",
      "id": "1",
      "1": "250",
      "amount": "250",
      "2": "user 1",
      "user_id": "user 1",
      "3": "jan 15",
      "month_id": "jan 15"
    },
...]

Now, I think these"0", "1", "2", "3" names/values are not supposed to be there and I must have done something wrong here. Is this the doing of thejson_encode()? Or is it the way I'm querying the db?

Thanks for the help!

672

Answer

Solution:

The issu here is that you are calling fetch_array().

fetch_array() will return an array of index based values as well as key based values. So if you only want the key(name) based values in the array, use the below code.

Try fetch_assoc().

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

Or fetch_array(MYSQLI_ASSOC)

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

People are also looking for solutions to the problem: php implode two tags into one array

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.