Convert data from db to JSON using php

Question

I have already seen many questions but nothing has helped. I want to convert my data from database (MySQL) to JSON using PHP. This is my PHP code:

init.php

<?php
$db_name = "webappdb";
$mysql_user = "root";
$mysql_pass = "root";
$server_name = "localhost";
$charset= "utf8";

$con = mysqli_connect($charset, $server_name, $mysql_user, $mysql_pass, $db_name);
?>

listViewBooks.php

<?php

include("init.php");

header('Content-Type: application/json');

// get all items from user_info_book table
$sql = mysqli_query("SELECT * FROM `user_info_book`");

$res = mysqli_query($con,$sql);

$result = array();

while($row = mysqli_fetch_array($res)){
    $output[] = $row;
}

echo json_encode($output);
echo json_last_error();

mysqli_close($con);

?>

The error is 0, so it's nothing.

Also I dont think you can set the charset directly into the connection,you might need to do $con->set_charset('utf8'); — Mihai
– Mihai, Commented Mar 8, 2016 at 19:39

elixenide · Accepted Answer · 2016-03-08 19:39:47Z

1

There are a bunch of problems in your code. For starters, you have this:

$sql = mysqli_query("SELECT * FROM `user_info_book`");

$res = mysqli_query($con,$sql);

$sql is a mysqli_result object on success or boolean false on failure. Here, it's false because you didn't pass the database link ($con). See the docs. You shouldn't, don't need to, and can't store the result of mysqli_query in a variable ($sql) and then pass that variable in another call to mysqli_query. Just do:

$sql = "SELECT * FROM `user_info_book`";

$res = mysqli_query($con, $sql);

Also, you initialize one array, then add to another:

$result = array();

while($row = mysqli_fetch_array($res)){
    $output[] = $row;
}

Perhaps you mean to do $output = array();?

You would benefit from using an IDE like PHPStorm.

edited Mar 8, 2016 at 19:39

answered Mar 8, 2016 at 19:34

elixenide

44.9k16 gold badges79 silver badges103 bronze badges

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

2 Comments

Carlo Over a year ago

Thank you very much, I'm new in php. Thank you Ed.

elixenide Over a year ago

@Carlo Glad to help out!

AbraCadaver · Accepted Answer · 2016-03-08 19:32:43Z

0

So you execute a query and assign the result to $sql:

$sql = mysqli_query("SELECT * FROM `user_info_book`");

But then you query again and use the $sql result as if it were a string:

$res = mysqli_query($con,$sql);

Probably more what you were thinking:

$sql = "SELECT * FROM `user_info_book`";
$res = mysqli_query($con,$sql);

You should use error reporting when developing:

error_reporting(E_ALL);
ini_set('display_errors', '1');

answered Mar 8, 2016 at 19:32

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

1 Comment

Carlo Over a year ago

Thank you very much, I'm new in php. Thank you AbraCadaver.

Marc B · Accepted Answer · 2016-03-08 19:34:57Z

0

You code is bad:

$sql = mysqli_query("SELECT * FROM `user_info_book`");
                    ^---missing DB handle
 ^---query result handle

So $sql becomes boolean false for failure, because you didn't call the query function correctly.

You then blindly use this boolean false as if it was a query string:

$res = mysqli_query($con,$sql);
                           ^---boolean false, due to previous errors

So basically, you assumed your code was perfect, and could never ever possibly have any problems, so failed to add any error handling. Since you have no error handling, you utterly ignored the errors that DID occur.

Never EVER assume success - this code is a perfect example of WHY. Your sql is syntactically perfect, yet it failed because you didn't call the query function properly.

Always assume failure, check for failure, and treat success as a pleasant surprise.

answered Mar 8, 2016 at 19:34

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Carlo Over a year ago

Thank you very much, I'm new in php. Thank you Marc.

Gustavo Morales · Accepted Answer · 2016-07-04 04:40:16Z

0

You initialize an array called $result but try to use an array with the $output identifier which has not been initialized, PHP will not auto-initialize an array variable for you. That is where one error comes from.

The second error I noticed is:

mysqli_query("SQL")

You forgot to pass in the database connection resource as it should be:

mysqli(db_connection, sql_query)

Fix those then if you need more assistance, comment below.

Have a good day.

edited Jul 4, 2016 at 4:40

Gustavo Morales

2,6709 gold badges34 silver badges40 bronze badges

answered Jul 4, 2016 at 4:10

Philip Rollins

1,2899 silver badges20 bronze badges

Collectives™ on Stack Overflow

Convert data from db to JSON using php

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related