PHP JSON_ENCODE doesn't work to show more than 1000 fields of MySQL table [duplicate]

Question

I have a problem to echo more than 1000 fields of MySQL records in PHP. I want them to be encoded with JSON. I have 2 table, each of them has 4 columns. The first table named 'contact_anggablokj6', it has 243 rows (number of fields: 4x243=972 fields). The second table, named 'contact_luthfir272' has 448 rows (number of fields: 4x448=1792 fields). At first I got a warning like the one in this post: Warning: a form on this page has more than 1000 fields PHP MySql. I tried the solution in that post, I got no warning anymore, but still I can not echo more than 1000 fields. I made 2 PHP codes, the first one looks like this:

<?php
include ('db_connect.php');
ini_set('max_input_vars', 5000);

$user_sxyzID='[email protected]';
$j = strpos($user_sxyzID,"@");
$trimmed_sxyzid = substr($user_sxyzID, 0, $j);

$arr_retrieve = array();

$sql_retrieve="SELECT * FROM contacts_$trimmed_sxyzid";

$retrieved = mysql_query($sql_retrieve);

$i=0;

while($row=mysql_fetch_array($retrieved)){
    $arr_retrieve[$i] = array(
      "contact_id" => $row['contact_ID'],
      "contact_name" => $row['contact_name'],
      "contact_phone" => $row['contact_phone'],
      "contact_email" => $row['contact_email']
    );
    $i++;
}
echo json_encode(array("result"=>$arr_retrieve));

mysql_close($con);
?>

The result is correct, all the JSON objects are inside a JSON Array.

But if the value of variable '$user_sxyzID' is changed to '[email protected]' which relates to table 'contact_luthfir272' that has 1700's fields, the result shows nothing (there is no error or warning, too). And then I made the second PHP code, it looks like this:

<?php
include ('db_connect.php');
ini_set('max_input_vars', 5000);

$user_sxyzID='[email protected]';
$j = strpos($user_sxyzID,"@");
$trimmed_sxyzid = substr($user_sxyzID, 0, $j);

$arr_retrieve = array();

$sql_retrieve="SELECT * FROM contacts_$trimmed_sxyzid";

$retrieved = mysql_query($sql_retrieve);

$i=0;

while($row=mysql_fetch_array($retrieved)){
    $arr_retrieve[$i] = array(
      "contact_id" => $row['contact_ID'],
      "contact_name" => $row['contact_name'],
      "contact_phone" => $row['contact_phone'],
      "contact_email" => $row['contact_email']
    );
    echo json_encode(array("result"=>$arr_retrieve[$i]));
    $i++;
}

mysql_close($con);
?>

It works for both tables, but the result is only JSON Objects, that's the problem.

What should I do to get the result like '[email protected]' user ID (all the JSON objects are inside a JSON Array) for '[email protected]' user ID?

------------------UPDATE--------------------------------------------------------

I modified the PHP code to be like this:

<?php
header('Content-type: application/json');
include ('db_connect.php');
ini_set('max_input_vars', 5000);
ini_set('post_max_size', '16M');

$user_sxyzID='[email protected]';
$j = strpos($user_sxyzID,"@");
$trimmed_sxyzid = substr($user_sxyzID, 0, $j);

$sql_retrieve="SELECT * FROM contacts_$trimmed_sxyzid";

$retrieved = mysql_query($sql_retrieve);

$i=0;

$arr_retrieve = array();

if(mysql_num_rows($retrieved)>0){
 while($row=mysql_fetch_assoc($retrieved)){
    $arr_retrieve[$i] = array(
      "contact_id" => $row['contact_ID'],
      "contact_name" => $row['contact_name'],
      "contact_phone" => $row['contact_phone'],
      "contact_email" => $row['contact_email']
    );         
    $i++;
 }

 echo json_encode($arr_retrieve);

 switch (json_last_error()) {
    case JSON_ERROR_NONE:
        echo ' - No errors';
    break;
    case JSON_ERROR_DEPTH:
        echo ' - Maximum stack depth exceeded';
    break;
    case JSON_ERROR_STATE_MISMATCH:
        echo ' - Underflow or the modes mismatch';
    break;
    case JSON_ERROR_CTRL_CHAR:
        echo ' - Unexpected control character found';
    break;
    case JSON_ERROR_SYNTAX:
        echo ' - Syntax error, malformed JSON';
    break;
    case JSON_ERROR_UTF8:
        echo ' - Malformed UTF-8 characters, possibly incorrectly encoded';
    break;
    default:
        echo ' - Unknown error';
    break;
  }
}else{
echo "There's no data found.";
}

The result shows a line of the last row in MySQL table and returns JSON_ERROR_NONE value.(A little progress)

You have a new table per user? Why?! Why couldn't you just have 1 table and a field called 'owner', which is the username? — Farkie
– Farkie, Commented Feb 22, 2016 at 8:36
Hello Sir! In this restaurant tonight we got 60 tables and 60 guests, so I'm afraid we are fully booked. — isnisn
– isnisn, Commented Feb 22, 2016 at 8:38
json_encode breaks if there are characters which are not encoded in utf-8 — undefined_variable
– undefined_variable, Commented Feb 22, 2016 at 8:41
Not sure if this is the total solution but move the echo json_encode(array("result"=>$arr_retrieve[$i])); outside the while loop and code it as echo json_encode(array("result"=>$arr_retrieve)); this will send one JSON String to the browser rather that 1700 little strings — RiggsFolly
– RiggsFolly, Commented Feb 22, 2016 at 8:46
In the updated code you have to put the $arr_retrieve = array(); outside the loop, otherwise you will always end up with just the last row. — Aioros
– Aioros, Commented Feb 22, 2016 at 11:39

Luthfi Rahman · Accepted Answer · 2016-04-06 07:05:40Z

The main problem is my PHP code didn't verify the MySQL records to be encoded in UTF-8. The json_encode() will break down if there's a data that doesn't encoded in UTF-8. So as suggested by Undefined_variable and Aioros, make sure all the data encoded in UTF-8 to use json_encode(). This is the correct script that will tell you if there is something wrong when using json_encode().

<?php
//check if there's something wrong with json_last_error():
switch (json_last_error()) {
case JSON_ERROR_NONE:
    echo ' - No errors';
break;
case JSON_ERROR_DEPTH:
    echo ' - Maximum stack depth exceeded';
break;
case JSON_ERROR_STATE_MISMATCH:
    echo ' - Underflow or the modes mismatch';
break;
case JSON_ERROR_CTRL_CHAR:
    echo ' - Unexpected control character found';
break;
case JSON_ERROR_SYNTAX:
    echo ' - Syntax error, malformed JSON';
break;
case JSON_ERROR_UTF8:
    echo ' - Malformed UTF-8 characters, possibly incorrectly encoded';
break;
default:
    echo ' - Unknown error';
break;
}
}else{
  echo "There's no data found.";
}

mysql_close($con);
?>

Hope it is useful for others. Thank you everyone for helping me out.

Collectives™ on Stack Overflow

PHP JSON_ENCODE doesn't work to show more than 1000 fields of MySQL table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related