Hello everyone before anything I want to set important facts that cannot be changed:
We have a webapi (similar to a restful service) where with an url, the users can be shown in a json way. But we have 85k+ records, when we try to brig them all, the php crashes (that doesn't happen when we look just for 1 record), we have 2 codes to work with, but none of them bring all the users
Version 1
<?php
//security variables
$variableName = 'name';
$variableValue = 'value';
//system variables
ob_end_flush();
ob_start();
if(isset($_GET[$variableName]) && $_GET[$variableName]== $variableValue){
$dbVars = array();
$query = 'SELECT * FROM users_data ';
foreach ($_GET as $key => $value) {
if( $key != $variableName && $key != 'sized' && $key != 'devmode'){
array_push($dbVars, $key."='".$value."'");
}
}
if( count($dbVars) > 0 ){
$query .= 'WHERE '.implode(' AND ', $dbVars);
}
$result = mysql($w['database'], $query);
$results = array();
while($row = mysql_fetch_assoc($result)){
array_push($results, $row);
}
echo json_encode($results);
}
?>
Version 2 (using flush)
<?php
//security variables
$variableName = 'name';
$variableValue = 'value';
//system variables
ob_end_flush();
ob_start();
if(isset($_GET[$variableName]) && $_GET[$variableName]== $variableValue){
$dbVars = array();
$query = 'SELECT * FROM users_data ';
foreach ($_GET as $key => $value) {
if( $key != $variableName && $key != 'sized' && $key != 'devmode'){
array_push($dbVars, $key."='".$value."'");
}
}
if( count($dbVars) > 0 ){
$query .= 'WHERE '.implode(' AND ', $dbVars);
}
$result = mysql($w['database'], $query);
echo "[";
while($row = mysql_fetch_assoc($result)){
$results = array();
foreach($row as $key => $value){
array_push($results, '"'.$key.'":"'.$value.'"');
}
echo '{'.implode(',',$results).'}';
ob_flush();
}
echo "]";
}
?>
When we want to get all the users in database are 85k, the result is none because memory limit How can we made this work for all the users?
First of all, your code is filled to the brim with SQL injection vulnerabilities. Anyone can own your database. This is your problem.
Now, the reason PHP runs out of memory is that you are using buffered queries.
http://php.net/manual/en/mysqlinfo.concepts.buffering.php
Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process.
Read the link I provided (which uses mysqli, for chrissakes) and use unbuffered mode to stream your result set to the client.
Also, don't array_push() the entire result set into an array.
Also, obviously,
array_push($dbVars, $key."='".$value."'");
will generate invalid JSON if any value contains a " or other escape chars.