Getting JSON from PHP MySQL connection

Question

There are many examples of this on StackOverflow, but I can't see the error in my code. I followed a few tutorials and copied some code off this forum and it still errors bugs out.

My CREATE TABLE query:

CREATE TABLE `e1_ENGLISH` (
    `entry_id` int(11) NOT NULL AUTO_INCREMENT,
    `words_e1` char(30) NOT NULL,
    `date` date DEFAULT NULL,
    `phonetic_e1` varchar(50) NOT NULL,
    PRIMARY KEY (`entry_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4

My PHP code:

<?php
// establish connection
// mysqli contect (host, user, pass, db)
$conn = mysqli_connect("localhost","androidapp","password","myDB");

// check for connection success
if(!$conn) {
  die("Error, could not connect: " . mysqli_connect_error());
}

// build query
$sql = "SELECT * FROM e1_ENGLISH";
$result = mysqli_query($conn, $sql); // fetch data

// convert result to array
$resArray = array();
while ($row = mysqli_fetch_assoc($result)) {
    //$resArray[] = $row;
    $resArray[] = utf8_encode($row);
}

// display result
echo json_encode($resArray);

// close connection
mysqli_close($conn);
?>

The output I get (with utf8_encode):

[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]

I get a blank screen with just $resArray[] = $row;

I am new to PHP, MySQL data, and webservers. Any mistakes you can spot? Additionally, I dont have a script or anything, this is straight PHP.

Thank you in advance!

instead of fetch_assoc either use fetch_array or $resArray[] = utf8_encode($row['put_column_name_here']);// repeat it in while loop for all column and check — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jul 1, 2016 at 23:14
Ok, will try. What exactly is the difference with fetch_assoc and the other fetch_ methods? I have trouble understanding why some examples use it. — Frank B.
– Frank B., Commented Jul 1, 2016 at 23:17
fetch_assoc gave you associative array so you need to use column names there. while fetch_array() give you both combo (numeric+associative). you can get only numeric too. check on google for that — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Jul 1, 2016 at 23:19
Somebody's downvoting all answers. How about some comments as to the reason(s). — AbraCadaver
– AbraCadaver, Commented Jul 1, 2016 at 23:30
@Anant That's not really practical. Sometimes someone else has made the same comment you were going to make, there's no need to repeat it. — Barmar
– Barmar, Commented Jul 1, 2016 at 23:34

Barmar · Accepted Answer · 2016-07-06 14:26:37Z

2

The argument to utf8_encode() has to be a string, you can't call it on an array. You need to loop over $row calling it on each element.

$resArray = array();
while ($row = mysqli_fetch_assoc($result)) {
    foreach ($row as &$val) {
        $val = utf8_encode($val);
    }
    $resArray[] = $row;
}

edited Jul 6, 2016 at 14:26

answered Jul 1, 2016 at 23:25

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

11 Comments

Frank B. Over a year ago

Thanks! This seems to have shown me the data in JSON format.

Frank B. Over a year ago

Hey @Barmar, do you know I could see this bo? in the browser while I see this boθ in the database? it seems like I still have UTF-8 issues. My database and tables are all utf8mb4.

Barmar Over a year ago

Make sure you have the proper encoding specified in the <head>.

Barmar Over a year ago

Beyond that, I'm not sure. charset issues often confuse me as well.

Frank B. Over a year ago

I don't have HTML wrapping my PHP code. Is there another head you are referring?

|

Raul H · Accepted Answer · 2016-07-01 23:29:48Z

2

You can try to fetch like this by record on an array and json_encode the array where name and last_name are the names of your rows in your database

            $res_array = array();

            while($row = $result->mysqli_fetch_assoc()) {
                $single_record = array(     
                    "NAME_U_WANT1" => $row['NAME'],
                    "NAME_U_WANT2" => $row['LAST_NAME'],
                );
                array_push($res_array, $single_record);
            }

        return json_encode($res_array);

as you see you have to loop their rows

edited Jul 1, 2016 at 23:29

answered Jul 1, 2016 at 23:23

Raul H

3053 silver badges20 bronze badges

Comments

AbraCadaver · Accepted Answer · 2016-07-06 12:10:33Z

2

utf8_encode() takes a string. Encode each array element with array_map():

while ($row = mysqli_fetch_assoc($result)) {
    $resArray[] = array_map('utf8_encode', $row);
}
echo json_encode($resArray);

edited Jul 6, 2016 at 12:10

answered Jul 1, 2016 at 23:27

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

2 Comments

Frank B. Over a year ago

Hey, thanks but I get a 500 Error from the server. What could conflict with that line?

AbraCadaver Over a year ago

Nothing, you must have other issues.

Collectives™ on Stack Overflow

Getting JSON from PHP MySQL connection

3 Answers 3

11 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related