$result->fetch_all(MYSQLI_ASSOC) returns nothing

Question

I'm trying to create a JSON callback. I got two files, json.html and json.php. Also, I've a database with like this:

Type: MySQL
DB Name: user_table
Table name: customers
Fields: id, name, product, supplier

Codes of my json.html is:

<html>
<head>

</head>
<body>

<div id="demo" style="font-size: 20px;"></div>

<script>

obj = { "table":"customers", "limit":10 };
dbParam = JSON.stringify(obj);
xmlhttp = new XMLHttpRequest();
xmlhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
        myObj = JSON.parse(this.responseText);
        for (x in myObj) {
            txt += myObj[x] + "<br>";
        }
        document.getElementById("demo").innerHTML = txt;
    }
};
xmlhttp.open("POST", "json.php", true);
xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xmlhttp.send("x=" + dbParam);

</script>

</body>
</html>

And here is the codes of json.php:

<?php

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = "db";

header("Content-Type: application/json; charset=UTF-8");
$obj = json_decode($_POST["x"], false);

$conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT name FROM ".$obj->$table." LIMIT ".$obj->$limit);
$outp = array();
$outp = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($outp);

?>

Here is the error log reports:

PHP Notice: Undefined variable: table in /home/user/public_html/json/json.php on line 7

PHP Fatal error: Cannot access empty property in /home/user/public_html/json/json.php on line 7

PHP Notice: Undefined index: x in /home/user/public_html/json/json.php on line 4 PHP Notice: Undefined variable: table in /home/user/public_html/json/json.php on line 7

PHP Notice: Trying to get property of non-object in /home/user/public_html/json/json.php on line 7

PHP Notice: Undefined variable: limit in /home/user/public_html/json/json.php on line 7

PHP Notice: Trying to get property of non-object in /home/user/public_html/json/json.php on line 7

PHP Fatal error: Call to a member function fetch_all() on a non-object in /home/user/public_html/json/json.php on line 9

How can I make it work?

score 1 · Accepted Answer · answered Jan 08 '17 at 12:38

As mentioned in a comment to David, the problem was with fetch_all(). I guess what was making the problem is the server resources because the page returned 500 on call.

In any case I retrieved the required array using this method instead:

$conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT * FROM customers LIMIT 10");
$outp = array();    

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


echo json_encode($outp);

And it worked.

Now, I'm going to make it work with my JSON callback.

score 0 · Answer 2 · answered Jan 08 '17 at 10:32

0

Try replacing

$result = $conn->query("SELECT name FROM ".$obj->$table." LIMIT ".$obj->$limit);

with

$result = $conn->query("SELECT name FROM ".$obj->{'table'}." LIMIT ".$obj->{'limit'});

answered Jan 08 '17 at 10:32

Jensui

19
3

Thanks for replying. I tried your suggestion but the same problem with the same error in error logs returned. – Arashtad Jan 08 '17 at 10:40
Isn't this just the same thing? – David Jones Jan 08 '17 at 10:42
Also, `$obj->{'limit'}` can just be `$obj->limit` :) – Rwd Jan 08 '17 at 10:42

score 0 · Answer 3 · answered Jan 08 '17 at 10:40

Try like this...

In your javascript send object..

xmlhttp.send(obj);

In PHP:

<?php

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = "db";

header("Content-Type: application/json; charset=UTF-8");
extract($_POST); //Extracting 

$conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT name FROM ".$table." LIMIT ".$limit);
$outp = array();
$outp = $result->fetch_all(MYSQLI_ASSOC);

print_r($outp);
echo json_encode($outp);

?>

score 0 · Answer 4 · edited May 23 '17 at 12:16

0

Looks like the JSON you are trying to send is not making it to the PHP script.

Some things I would do are:

Change the request header content type to application/json (see this https://stackoverflow.com/a/9880122/2315558)
Check that x has a value before trying to use it

edited May 23 '17 at 12:16

Community

1
1

answered Jan 08 '17 at 10:42

David Jones

4,275
6
27
51

It looks like I'm not receiving data from mysql at all. – Arashtad Jan 08 '17 at 11:08
I ignored JSON and put the values right in my SQL string but the PHP file still doesn't work! $result = $conn->query("SELECT name FROM customers LIMIT 10"); – Arashtad Jan 08 '17 at 11:09
Ok then that is a separate issue to what our question is. Whats the issue when you are code the query? Is there an error? – David Jones Jan 08 '17 at 11:29
The error log says: Call to undefined method mysqli_result::fetch_all() in /home/user/public_html/json/json.php on line 11 – Arashtad Jan 08 '17 at 12:08
Now the the php trying this: $conn = new mysqli($servername, $username, $password, $dbname); $result = $conn->query("SELECT name FROM customers"); $outp = array(); $outp = $result->fetch_all(MYSQLI_ASSOC); print_r($outp); – Arashtad Jan 08 '17 at 12:09
And the browser returns 500 – Arashtad Jan 08 '17 at 12:10
The problem is fetch_all for sure. When I try fetch_array instead, it works fine. But fetch_all or fetch_assoc don't work. – Arashtad Jan 08 '17 at 12:21
Maybe this could help you out http://stackoverflow.com/questions/6694437/mysqli-fetch-all-not-a-valid-function – David Jones Jan 08 '17 at 12:48

$result->fetch_all(MYSQLI_ASSOC) returns nothing

4 Answers4