Encoding json from database

Question

I know this has been asked thousands of times, but I still can't wrap my head around it. Here it comes: I want to encode my database data into json.

I had my code working under an old version of php using msql_connect, etc in stead of mysqli. I updated the server yesterday and I can't get it working with mysqli.

This is my code, returning nothing, not null, no empty brackets, nothing.

<?php
$mysqli = new mysqli("$host", "$username", "$password","$db_name");
$myQuery = "SELECT * FROM myTable ORDER BY date DESC LIMIT 4";
$result = $mysqli->query($myQuery);

$data = array();

while($row = mysqli_fetch_assoc($result)) {
    $data['nameOfArray'][] = $row;
}
echo json_encode($data);

$mysqli->close();
?>

I would like an output looking something like

"nameOfArray":[{object},{object},{object}]

Weird thing is, I had ik working properly using old methods, which I removed (Being quite stupid, honestly)

So hopefully someone here can point me in the right direction. Thanks a lot in advance.

Edit: When I do a print_r, I see the data, so it has to do with encoding it to json, the mysql bit is coorect. I also found some answers using a jsonSerializer, but I just can't imagine it needs to be so difficult when it used to be quite easy.

As a first step, turn on [error reporting](http://stackoverflow.com/a/6575502/1438393). Also, instead of blindly assuming that the query execution succeeded, check for the return values. Use the MySQLi error handling functions. — Amal Murali, Feb 17 '14 at 12:03
Agree with @AmalMurali. You should check that that mysqli connection did not fail, and also that your query did not fail. After calling `new mysqli()`, perhaps you should wrap the next part in `if ($mysqli) {...}` to ensure that the connection succeeded; if not, then check out `mysqli_connect_error()`. Similarly, you should do a check on `if ($result) {...}` to ensure that the query was successful; if not, then check out `mysqli_error()`. — Alvin S. Lee, Feb 17 '14 at 12:35
First of all: thanks for the reactions. But print_r resulted in a correct display of my data. And I will of course check and catch errors, but for now, I just got the minimum pasted, so you could easily understand it and help. — stephanmantel, Feb 17 '14 at 19:48

score 1 · Answer 1 · answered Feb 17 '14 at 12:08

1

try this

<?php
$mysqli = new mysqli("$host", "$username", "$password","$db_name");
$myQuery = "SELECT * FROM myTable ORDER BY date DESC LIMIT 4";
$result = $mysqli->query($myQuery);

$data = array();


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

$mysqli->close();
?>

answered Feb 17 '14 at 12:08

Satish Sharma

9,547
6
29
51

`mysqli_fetch_assoc($result)` is synonymous with `$result->fetch_assoc()`. Both can be used. OP did not make a mistake there. Here is a link to [documentation on mysqli_fetch_assoc](http://php.net/manual/en/mysqli-result.fetch-assoc.php) – Alvin S. Lee Feb 17 '14 at 12:30
see your link once again there is 2 examples one of `simple mysqli_*` functions and other is as object `mysqli`. in op query he using the object and assigning a object in `$result` – Satish Sharma Feb 17 '14 at 12:32
@AlvinLee both have the different style `Object oriented` and `Procedural` – Satish Sharma Feb 17 '14 at 12:34
Both the object oriented style `mysqli::query()` and the procedural style `mysqli_query()` return the same type of data - a [`mysqli_result`](http://php.net/manual/en/class.mysqli-result.php) object. From there, you can use either the object oriented style of `mysqli_result::fetch_assoc()` or you can use the procedural style and pass the object as an argument to `mysqli_fetch_assoc()`. Both will yield the same result. Granted, the OP may not have been *consistent* in mixing his use of the object oriented and the procedural styles - but this inconsistency will not result in an error. – Alvin S. Lee Feb 17 '14 at 12:39
Thanks for the reactions so far. Indeed, I realize that when I pasted the code it is inconsistent, I have tried both ways and it is this way due to trying a lot. – stephanmantel Feb 17 '14 at 19:44

score 0 · Answer 2 · answered Oct 19 '14 at 09:08

although the question is old but may help others falling in similar problem. This is a common mistake when u change your mysql code to mysqli code. you dint passed the connection variable on procedural calling mysqli_fetch_assoc() . something like this will work.

<?php
$mysqli = new mysqli("$host", "$username", "$password","$db_name");
$myQuery = "SELECT * FROM myTable ORDER BY date DESC LIMIT 4";
$result = $mysqli->query($myQuery);

$data = array();

while($row = mysqli_fetch_assoc($mysqli, $result)) {
$data['nameOfArray'][] = $row;
}
echo json_encode($data);

$mysqli->close();
?>

Encoding json from database

2 Answers2