Return array of mysql database

Question

I have the following code, which is very similar to other working PHP statements. But for some reason, this one does not return JSON. The only difference is that this code calls for an entire database.

<?php

// Create connection
include_once 'functions.php';

// Check connection
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$state = $_POST["state"];

$db = $state."Standards";

// This SQL statement selects ALL from the table $db
$sql = "SELECT * FROM $db";

// Check if there are results
if ($result = mysqli_query($con, $sql)) {
    // If so, then create a results array and a temporary one
    // to hold the data
    $resultArray = array();
    $tempArray = array();

    // Loop through each row in the result set
    while($row = $result->fetch_object()) {
        // Add each row into our results array
        $tempArray = $row;
        array_push($resultArray, $tempArray);
    }
    // Finally, encode the array to JSON and output the results
    echo json_encode($resultArray);
}

// Close connections
mysqli_close($con);
?>

This returns nothing. However, if I put echo json_encode($row); inside the while command, it will return the data, but of course in separate brackets {} for each row (here's a part of it):

{"Grade":"1","1CE":"1CE Identify echo and call\/response.","2CE":"2CE Explore steady beat, rhythm and meter.","3CE":"3CE Listen to and identify music of various and contrasting styles, composers, periods and cultures.","4CE":"4CE Identify elements of music using developmentally appropriate vocabulary (e.g., rhythm syllables and solfege). ","5CE":"5CE Explore selected musical instruments aurally and visually.","6CE":"6CE Attend live music performances with emphasis on concert etiquette.","7CE":"","8CE":"","1PR":"1PR Demonstrate echo and call\/response. ","2PR":"2PR Sing (using head voice and appropriate posture) and move to music of various styles, composers and cultures with accurate pitch and rhythm.","3PR":"3PR Read, write and perform using eighth notes, quarter notes and quarter rests. ","4PR":"4PR Improvise new lyrics to known songs and experiment with digital technology.","5PR":"5PR Read, write and perform la-sol-mi melodies on the treble staff in Gdo, F-do and C-do using a system (e.g., solfege, numbers or letters). ","6PR":"6PR Play a variety of classroom instruments, alone and with others, and demonstrate proper technique.","7PR":"7PR Demonstrate audience behavior appropriate for the context and style of music performed.","8PR":"","9PR":"","1RE":"1RE Recognize how music is used for a variety of occasions.","2RE":"2RE Describe how music communicates feelings, moods, images and meaning.","3RE":"3RE Communicate a response to music using dance, drama or visual art.","4RE":"4RE Connect concepts shared between music, other art forms and other curricular subjects.","5RE":"5RE Form and express personal opinions about a m...

The DB info all fits on one screen, so it's not huge. I can't figure out why the final step of putting it into an array is failing.

if ($result = mysqli_query($con, $sql) or die(mysqli_error($con))) { and check what error happening — Alive to die - Anant, May 30 '15 at 03:32
According to PHP docs: _If you use array_push() to add one element to the array it's better to use $array[] = because in that way there is no overhead of calling a function._ So try changing to `$resultArray[] = $tempArray;` instead. Or straight up `$resultArray[] = $row;` — martynasma, May 30 '15 at 03:34
@martynasma, thanks for the clearer code. However, I still don't see a response. — Tim, May 30 '15 at 03:37
@anant that doesn't work, as mentioned the if statement is definitely working, as the $row has data. — Tim, May 30 '15 at 03:39
Is that a very large DB? In that case json_encode might be hitting a memory limit set in PHP config. If you have error output disabled, you get blank screen. Try putting this before your code: `error_reporting(E_ALL);` to see if that is the case. — martynasma, May 30 '15 at 03:43
@martynasma where exactly would I put that line? And to answer your question, it IS larger than other DB I've been working with, but I don't know how large is too large. I can look into that problem as a possibility, thanks! — Tim, May 30 '15 at 03:46
please add this:- if ($result = mysqli_query($con, $sql) or die(mysqli_error($con))) {. definatily you will get some problem. because your code work for me by adding this line and removing error. thanks. — Alive to die - Anant, May 30 '15 at 03:52
I have added both error methods, but I get no result from either. — Tim, May 30 '15 at 03:53
I updated the question with a sample of what I get with `echo json_encode($row)` in the `while` loop. All the data is there, so I don't understand why I can't get a single array. — Tim, May 30 '15 at 03:59
Just wanted to make a note. It looks like this query is vulnerable to SQL injection. Seems like the POST values are being injected directly into the SQL request. I _think_ you should parameterize this. — jeremysawesome, May 30 '15 at 04:03
`$result` is true whether or not there are results. It will be false only in case of error. — Sebas, May 30 '15 at 04:05
@jeremysawesome I'll look into that. I do know how to do prepared statements, but I didn't know they were necessary for SELECT statements. — Tim, May 30 '15 at 04:08
your code work fine for me by doing some adjustment like this:-http://prntscr.com/7b0bg0 and i got the result like this:- http://prntscr.com/7b0bkt. so it's weared that you are not getting any problem and also no data. — Alive to die - Anant, May 30 '15 at 04:23
Thanks all for your help. I managed to make it work with new code and a prepared statement. I'm still not sure what was wrong here but I think it was actually my html test code. — Tim, May 30 '15 at 18:25

score 0 · Answer 1 · answered May 30 '15 at 16:13

You might try debugging with print_r(). That should show you what is in your array.

If $row is being successfully json_encoded, then the following code should work. But.. let's see what your debugging leads you to discover.

// ... code

$resultArray = array();
while($row = $result->fetch_object()) {
    // Add each row into our results array
    $resultArray[] = $row;
}
// debugging output
print_r($resultArray); // <-- is the result of this output what you expect?

echo json_encode($resultArray);

// ... code

I do get a result from `print_r()` such as `Array ( [0] => stdClass Object ( [state] => Ohio [grade] => Kindergarten [category] =>`...It doesn't look the same as what I need from json_encode, but it's there. — Tim, Jun 01 '15 at 01:52

score 0 · Accepted Answer · edited May 23 '17 at 11:43

Apparently json_encode() can silently fail when trying to encode an array with text elements that are incorrectly encoded. The solution, which I found here, was to encode each messy string element in utf8_encode before adding to the array.

while($row = $result->fetch_object())
{
    // Add each row into our results array (and encode in utf8)
    $tempArray['grade'] = $row->grade;
    $tempArray['category'] = utf8_encode($row->category);
    $tempArray['standard'] = utf8_encode($row->standard);
    $resultArray[] = $tempArray;
}
// Finally, encode the array to JSON and output the results
echo json_encode($resultArray);

Return array of mysql database

2 Answers2