1

I've read for larger result sets I must use the MYSQL_USE_RESULT option when querying. This I do. However, the below PHP page is accessed via ajax and I receive 0 results once the known number of results reaches ~800. Before I reach this threshold, queries execute splendidly.

All queries work fine in phpmyAdmin.

What am I doing wrong?

<?php

$servername = "localhost";
$username = "user";
$password = "password";
$database = "mydb";

$mypassword = "expectedPassword";

$receivedPassword =$_POST["pwd"];


if ($receivedPassword != $mypassword) {
    print "credential failure";
} else {
    
// Create connection
    $conn = new mysqli($servername, $username, $password, $database);
    $myquery =$_POST["query"];
   
// Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } else {
     
    }
    $res = $conn->query($myquery, MYSQLI_USE_RESULT);
    $rows = array();
    while ($r = mysqli_fetch_assoc($res)) {
        $rows[] = $r;   
    }
    $conn->close();
    print(json_encode($rows));       
}    
?>
Dharman
  • 30,962
  • 25
  • 85
  • 135
Paul
  • 552
  • 5
  • 11
  • 1
    Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code, checking for errors if any. – Funk Forty Niner Feb 28 '16 at 20:16
  • 1
    It seems like you are using a user defined query on your code. Perhaps your problem is related to not properly escaping you query. Can you check that? – Jorge Campos Feb 28 '16 at 20:25
  • I've confirmed the generated query has no errors. I simply increase the bounds on my WHERE criterion and at a certain point I get back 0 results. The exact same queries execute fine from phpmyAdmin. I dial back the criterion and I get results again. The WHERE clause is selecting entries within certain dates. When I expand this gap (regardless of specific interval) is when the problem happens. – Paul Feb 28 '16 at 20:39
  • "I've confirmed the generated query has no errors" - then there's nothing to fear when adding something like `if ( !($res instanceof mysqli_result) ) { trigger_error('ouch! ouch! ouch!', E_USER_ERROR); }` ;-) – VolkerK Feb 28 '16 at 20:41
  • @VolkerK, I added your suggested code. The trigger_error does not happen. I just get nothing as a response. :( . To clarify, I do not get even an empty result ( [] ), but nothing. – Paul Feb 28 '16 at 21:21
  • Are you running php with [error_reporting=E_ALL|E_STRICT](http://docs.php.net/manual/en/errorfunc.configuration.php#ini.error-reporting) and having an eye on the file specified in [error_log](http://docs.php.net/manual/en/errorfunc.configuration.php#ini.error-log)? Also, since the script might print values that are not strictly valid JSON (e.g. "credential failure"), how do you check what is _actually_ sent to the client? – VolkerK Feb 28 '16 at 21:23

4 Answers4

1

This had nothing to do with memory. Turns out the relation between larger query results and failing was purely statistical.

The real problem was that there were occasionally special characters in the data stored in the database. For some reason (perhaps someone can explain) PHP just stopped--no errors, no nothing when a special character was encountered. The field with the special character has collation: utf8_general_ci. I would have never thought this would be an issue... Perhaps someone can explain this as well.

Adding:mysqli_set_charset($conn,'utf8'); before the query fixed my problem entirely.

Paul
  • 552
  • 5
  • 11
-2

Edit your php.ini to let your server use more memory. Change memory_limit =128M to your desire value or add ini_set('memory_limit', '-1'); to your php code but this will tell the server to use all the memory it wants.

  • Each row result is < 500 bytes of data. At 800 results, I'm asking for 400kB of info. The query is simply asking for entries between 2 datetimes. Do you think the overhead of that is really more than 128M? I can give it a try. But why would phpMyAdmin still work okay? – Paul Feb 28 '16 at 21:01
  • You're using MYSQLI_USE_RESULT exactly for not having the complete result set in your php instance's memory. On the other hand you're still fetching all the records _and_ store them in the array $rows - which makes MYSQLI_USE_RESULT quite superfluous..... – VolkerK Feb 28 '16 at 21:10
  • @VolkerK I added the MYSQLI_USE_RESULT in response to the behavior of getting nothing back when the query result grew in size. Maybe this has nothing to do with MYSQLI_USE_RESULT. Is there a way to determine how much memory was used for a query? – Paul Feb 28 '16 at 21:24
  • If memory_limit was/is the problem, it will show up in the php [error log](http://docs.php.net/manual/en/errorfunc.configuration.php#ini.error-log) as something like `Fatal error: Allowed memory size of ... bytes exhausted (tried to allocate ... bytes)`. When using [mysqlnd](http://docs.php.net/mysqlnd) the "raw" memory for the records will count against memory_limit, if using libmysql they will not (but $rows[]=$r still does). – VolkerK Feb 28 '16 at 21:26
-2

Try to use this:

<?php

$servername = "localhost";
$username = "user";
$password = "password";
$database = "mydb";

$mypassword = "expectedPassword";

$receivedPassword =$_POST["pwd"];


if ($receivedPassword != $mypassword) {
    print "credential failure";
} else {

// Create connection
    $conn = new mysqli($servername, $username, $password, $database);
    $myquery =$_POST["query"];

// Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } else {
      $res = $conn->query($myquery, MYSQLI_USE_RESULT);
      $rows = array();
      while ($r = mysqli_fetch_assoc($res)) {
        $rows[] = $r;   
      }
      $conn->close();
      print(json_encode($rows));       

    }

}    
?>

In //Check connection you put if function. First statement is die() if $conn is error. In else statement of same if function you've puted nothing. Bottom part of code need to be inside else statement to work.

Try it.

  • I've tried that too--putting the code in the else. It makes no difference. Upon connection success the else clause and whatever else is left gets executed. Upon connection failure the die functions acts like a return and nothing else gets executed. – Paul Feb 28 '16 at 21:03
-2

try replace this

while ($r = mysqli_fetch_assoc($res)) {
    $rows[] = $r;   
}

on this

if ($result = $conn->store_result()) {
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row; 
    }
    $result->free();
}

You use MYSQL_USE_RESULT to recive all rows from table?

BOLVERIN
  • 103
  • 6