PHP returns and empty json array when accesing a specific content from a returned mysqli query result

Question

i've been experiencing a problem recently. A couple of months ago i developed this code and it was working, now i tried running the code again and it is simply not working. I will explain

I have a javascript file which queries data to a php file, that is supposed to query data from 3 tables, A, B, and C in form of an INNER JOIN. Like i said before it was working, and now it simply returns and error to my javascript due to that it returns and empty array and it gives me the JSON.parse error on my javascript.

Ok moving on, Whenever i push data obtained From tables A and B, the php returns a complete array, but if I push the results from table C, my php returns and empty array. I've tried running my sql to check if it is wrong, and my sql queries fine on my console. Its so frustrating because i see that the results are returned on the console form the 3 tables, but if i try to obtain them from my php file, it will return an array if it contains the data from tables A and B, but as soon as i include the results from table C, it simply returns empty.

It has happened to me before but i simply cannot find and answer as to why it is happening because in some files it does work when i query multiple tables as up to 5 tables long but on some it simply wont work.

Here is my php code:

<?php
if(!isset($_POST['Select']))
die("You Do Not Have Permission To Access This File");

require_once 'DB_Connect.php';
$db = new DB_Connect();
$conn = $db->connect();


$sql = "SELECT AssignedStudent.Student_ID, Programs.name, Student.name As StName, Student.email FROM AssignedStudent INNER JOIN Programs ON AssignedStudent.Programs_ID = Programs.ID INNER JOIN Student ON AssignedStudent.Student_ID = Student.ID WHERE AssignedStudent.status='Active' ORDER BY Programs.name, AssignedStudent.Student_ID ASC";
$resultsAssigned = mysqli_query($conn, $sql);

$data = array();
$data2 = array();
if(mysqli_num_rows($resultsAssigned)>0){

    while($row = mysqli_fetch_assoc($resultsAssigned)) {

        $StProgam = $row["name"];
        $StID = $row["Student_ID"];
        //$StName = $row["StName"];
        //$StEmail = $row["email"];
        $StName = "das";
        $StEmail = "das";

        //echo $StName."-".$StEmail."\n";
        array_push($data, array("ProgramName"=>$StProgam,"StudentID" => $StID, "StudentName" => $StName, "StudentEmail" => $StEmail));
        //$data[] = array("ProgramName"=>$StProgam,"StudentID" => $StID, "StudentName" => $StName, "StudentEmail" => $StEmail);

    }   
}
echo json_encode($data);
?>

To close off, if any body is reading this post, please focus on what is inside the While loop because everything else is working 100%, ive already tested it before. Inside the while loop i have some pieces of code commented which demonstrate my error that i was talking about. if I uncomment these two lines: //$StName = $row["StName"]; //$StEmail = $row["email"]; my php will return an empty result to my javascript, but these two lines work fine: $StName = "das"; $StEmail = "das";. As i mentioned before I access data from 3 separate tables, and as soon as i include data from table C, which are the variables that i metioned just now, my php simply wont return any value but as soon as i ommit them, it works just fine.

Only those 2 lines produce me the error, which basically is absurd because i obtain full results from my query, proven on my console, but as soon as i access them from row[] result and push it into the data array, it returns an empty array.

Moreover, i tried echoing the results when i obtain them from the row[] and it does return the results, but as soon as i push them into the array it stops working. To explain it i push $StName = "das"; $StEmail = "das"; into my array it works fine, but if i push $StName = $row["StName"]; $StEmail = $row["email"]; it wont work, yet if i echo the results in these variables, they do show.

The problem is i cant push into my array when the results in $row["StName"]; and $row["email"];

Here is a screen shot of the query from my console:

Lastly, im running the code on my localhost using xampp, i've uninstalled my xampp that was from 3 years ago and updated to the latest version and still have the same error. Its so frustrating. I know it might sound out of context but could it be hardware problem from my laptop?

P.S. This same code with the same database on my online server WORKS COMPLETELY FINE 100% but on my machine local host it gives me and empty array as result.

What's the content of data **before** json_encode? Keep in mind, that objects cannot be json_encoded. — Peon, Jan 05 '17 at 09:27
@DainisAbols Objects can be json_encoded ( well the properties can ) — RiggsFolly, Jan 05 '17 at 09:30
Try running `mysqli_store_result ($conn)` before the if that checks the number of rows returned — RiggsFolly, Jan 05 '17 at 09:41
@RiggsFolly I'm sorry, I meant to say resource. A resource cannot be json_encoded — Peon, Jan 05 '17 at 09:41
Ok @RiggsFolly I tried the mysqli_store_result ($conn) before the IF statement, no positive results, my problem is still there. No results are being returned but thanks for the heads up, i didn't know about the mysqli_store_results. I should get updated more often. — Javier Pech, Jan 05 '17 at 12:47
@DainisAbols the information i pass on the $data array are strings and integers retrieved from the database. My point is, this same code works when accessing other tables from the database, multiple tables up to 5, which makes me doubt as to why only saving records from one specific table makes the echo json_encode($data) to completely not work. — Javier Pech, Jan 05 '17 at 12:49

score 1 · Answer 1 · answered Jan 05 '17 at 09:55

1

You say it works on one server, but not on your local server. Something's different between the two, maybe in the PHP configuration, maybe in the MySQL setup. But that may be difficult to diagnose unless you know what changes have been made between when it worked and now.

A simpler approach might be some simple debugging of the PHP script.

Try calling the php file directly from a browser (you'll have to comment out the security check) with some debugging echo statements added.

My suggestion is to add a debug line inside your while loop and echo the $row variable using print_r:

echo "<p>row:<pre>".print_r($row,true)."</pre></p>\n";

Then, at the end of the while loop, echo the $data array:

echo "<p>data:<pre>".print_r($data,true)."</pre></p>\n";

In your SQL statement, you alias one field (StName), but not the email field. Looking at what is returned by the first echo above will confirm the field names returned by the query. Looking at the results of the second echo may reveal some difference in the structure you are expecting. During testing, you may want to add a limit 0,4 statement to the SQL statement to limit the results to a manageable output size.

answered Jan 05 '17 at 09:55

Sloan Thrasher

4,953
3
22
40

OK i've tried running the script dirrectly aswell but no result is produced. As i mentioned before, the problem starts when i push the data from StName and email, it appears that my array wont be returned if i include those 2 records in any instance whatsoever. – Javier Pech Jan 05 '17 at 12:40
On an interesting side note, i did printing what you suggested, and it does save the data on the array. So the problem isnt really, that it wont push the data, the problem is the the echo json_encode($data) doesn't work whenever those two fields are included at any point. – Javier Pech Jan 05 '17 at 12:42
OK so i saw something new, if i limit the query to certain records, it does output the array. Turns out if i limit the query to a maximum of 23 records, the echo json_encode($data) does work! Any number more that that it simply doesnt return results. A few months ago this srcipt returned more that 200 records, and indeed on the online server it does do that. So now what could be causing this problem. Could it be a bad configuration of mysql or apache of my local xampp or even ports of my computer? – Javier Pech Jan 05 '17 at 13:02
Do you have PHP error reporting set so that you will see any issues? – Sloan Thrasher Jan 05 '17 at 21:53

score 0 · Answer 2 · answered Jan 05 '17 at 14:32

After some digging i found out that the problem to my php is that some records returned from the database contained some characters that were not supported by the json_encode, so what i did is i added the following line after establishing the connection to the server:

mysqli_set_charset($conn, "utf8");

and the problem was solved, it returned all of my records.

PHP returns and empty json array when accesing a specific content from a returned mysqli query result

2 Answers2