0

I'm trying to fetch the results of a query into a PHP array, the query is really simple and returns exactly 94 rows, the process runs perfectly until it hits a certain number of results, I discovered this by trial and error, I thought it was a data problem but it's actually a PHP problem, because if I run the query the array empties but if I put a LIMIT in the query the process runs ok, the magic number is 37 after that the array won't fetch more rows and displays nothing, this is what I'm doing:

PD. the problem seems to be with the Name field because if I use only the id field I can fetch the 94 rows without problem.

$query = "SELECT Name AS DisplayText, id AS Value FROM branches ORDER BY DisplayText ASC LIMIT 37;";
$conn = new mysqli($host, $username,   $password, $database);
$res = $conn->query($query);
$rows= array();
while ($row = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$array['DisplayText'] = $row['DisplayText'];
$array['Value'] = $row['Value'];
array_push($rows,$array);
}
print json_encode($rows);

this way works but I'm limited to 37 results because of the query limit, I need the 94 results, but if I remove the limit I get nothing

other way I tried with the same result:

$query = "SELECT Name AS DisplayText, id AS Value FROM branches ORDER BY DisplayText ASC LIMIT 37;";
$conn = new mysqli($host, $username,   $password, $database);
$res = $conn->query($query);
$rows= array();//rows
while ($row= mysqli_fetch_array($res, MYSQLI_ASSOC)) {
$rows[] = $row;
}    
print json_encode($rows);
CMP
  • 65
  • 6
  • Problem must be in your SQL, removing the LIMIT should never stop you returning rows. If you put a `echo mysqli_error($conn);` at the end, does it say anything about any errors? Additionally put `error_reporting(E_ALL);` at the top of your file to get all warnings and errors output to the webpage for debugging. – Kalkran Feb 17 '16 at 15:02
  • @Kalkran yes, I've tried all the debugging methods I know, at first I thought it was a DB problem and I checked for errors everywhere, I mean if I try any other query in that table or other tables excluding the **Name** field everything works without problems – CMP Feb 17 '16 at 15:09
  • So you're not getting _any_ errors, but are not receiving _any_ data? Do queries with a `LIMIT` of 38 work? 39? 50, 60? 94? – Kalkran Feb 17 '16 at 15:12
  • @Kalkran if I leave the query without LIMIT I get a white screen if i put a LIMIT of 37 or below I get results LIMIT of 38 or above I get a white screen, but only when the **Name** field is present on the query, if I leave the Name field out of the query I can fetch the 94 rows without problems, could be a memory problem? I mean the results in the Name field aren't that long the longest one is probably 30 characters long, I even placed a `ini_set('memory_limit', '2048M');` in my code just in case and I still get nothing – CMP Feb 17 '16 at 15:17
  • 1
    What does `var_dump($rows);` do? – Kalkran Feb 17 '16 at 15:20
  • @Kalkran this, actually kind of works :O it prints everything, I get all the results – CMP Feb 17 '16 at 15:25
  • @RyanVincent that was one the first things I checked, the HTML is empty, this only happens when I put the field Name on the query – CMP Feb 17 '16 at 18:28
  • Are you `escaping` the `name` values with `htmlentities` ? Do you have `error` notification set to `E_ALL`? Is the `json_encode` failing? Check the [json_last_error — Returns the last error occurred](http://php.net/manual/en/function.json-last-error.php)? – Ryan Vincent Feb 17 '16 at 18:30
  • I think it fails way before encoding the json probably around the `while`, bacause like I said if I put a `LIMIT` in the query and a value below 37 the everything works fine – CMP Feb 17 '16 at 18:53

2 Answers2

0

Might you be exceeding the max allowed memory for php or for mysql packet? How big is that "Name" text for each row?

Nadir
  • 1,799
  • 12
  • 20
0

Must be some odd character that json_encode() doesn't handle too well. Make sure you're feeding either UTF-8 or ISO-8859-1 to it. Suggestions made here might be helpful to you.

Community
  • 1
  • 1
Sergey Vidusov
  • 1,342
  • 1
  • 7
  • 10