Need help, developed myself into a corner with no time . Allowed memory error on PHP
Need help, developed myself into a corner with no time . Allowed memory error on PHP
crcucb
Posts: 90Questions: 30Answers: 0
I've developed an application for my friend who is running for judge to manage voters, engagement, etc... election is less than a month and the app is being heavily used. However, I need to add more fields on the Residents page and I am running into Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 14684160 bytes) in...\php\lib\Editor.php on line 443.
I am using clientside with AJAX POST on a paid hosted environment & I cannot increase the php memory.
if I set serverside to true, it works. However, they heavily rely on searchbuilder. From what I read, SearchBuilder doesn't natively work with php serverside, but it does with .net. I have never done anything with .net.
There are 19,000 residents for 11,000 addresses.
One of the reasons the residents page has so many fields is for exporting. for example, on the screen, the address is basically 1 field that has [street street # apartment] concatenated together. But when they export for mailers, they need the discrete address fields so I have to include each element.
I am attaching the residents.php below in case I am missing something.
Assuming everything is correct and it's just too much data for residents.php to process, I am unsure the best course.
- I haven't done .NET, is there a simple path to switch one page to .net to get the serverside to work with search builder?
- Exporting is based on whatever states/filters are created by them using the search builder. Residents.php contains fields not really for the CRUD, but only for exporting. Is there a way to separate those fields but somehow pull them in when exporting?
- any other ideas?
<?php
if (session_status() == PHP_SESSION_NONE) {
session_start();
};
error_reporting(E_ALL);
ini_set('display_errors', 'On');
include_once ( "lib/DataTables.php" );
include_once 'php_functions.php';
include_once ( "lib/config.php" );
//$backtrace = debug_backtrace();
$instanceDateTime = new DateTime();
$instanceDateTime->setTimezone(new DateTimeZone('America/New_York'));
$pageAID = 0;
$IsProd =1;
if ( isset($_POST['pageAID']) ) {
$pageAID = $_POST['pageAID'];
};
$_SESSION['jyorkejudge']['IsTestPage']=FALSE;
$addqualifier = ">=";
if (str_contains($_SERVER['PHP_SELF'], 'test')) {
$IsProd=0;
$_SESSION['jyorkejudge']['IsTestPage']=TRUE;
$addqualifier = "<";
};
if ( isset($_POST['action']) ) {
sqlphpupdate( "Residents", $_POST, $_SESSION['jyorkejudge']['useraid'] ,$sql_details, $instanceDateTime, $_SERVER['PHP_SELF']);
}
/*
* Editor server script for DB table Residents
* Created by http://editor.datatables.net/generator
*/
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'Residents', 'ResidentAID' )
->fields(
Field::inst( 'Residents.ResidentAID' )->set(false),
Field::inst( 'Residents.AddressAID' )->set(false),
Field::inst( 'Residents.phone' ),
Field::inst( 'Residents.email' )
->validator( Validate::email() ),
Field::inst( 'Residents.residentnotes' ),
Field::inst( 'Residents.Gender' ),
Field::inst( 'Residents.Party' ),
// Field::inst( 'Residents.Birth_Year' ),
Field::inst( 'Residents.LastName' ),
Field::inst( 'Residents.FirstName' ),
Field::inst( 'Residents.Middle' ),
// Field::inst( 'Residents.Suffix' ),
Field::inst( 'Residents.Voter_ID' ),
Field::inst('Residents.ResidentAge')->set(false),
// Field::inst('Residents.calc_party')->set(false),
Field::inst('view_Addresses.Full_Address')->set(false)
->options( Options::inst()
->order( 'view_Addresses.Full_Address' )
),
Field::inst('view_Addresses.Hung_Hanger')->set(false),
Field::inst('view_Addresses.Left_Card')->set(false),
Field::inst('view_Addresses.Inactive')->set(false),
Field::inst('view_Addresses.Ignore')->set(false),
// Field::inst('view_Addresses.Cannot_not_find_Address')->set(false),
// Field::inst('view_Addresses.Vacant')->set(false),
// Field::inst('view_Addresses.No_Tresspassing')->set(false),
// Field::inst('view_Addresses.Cannot_access')->set(false),
Field::inst('view_Addresses.Avoid')->set(false),
Field::inst('view_Addresses.Place_Sign')->set(false),
Field::inst('view_Addresses.Placed_Sign')->set(false),
Field::inst('view_Addresses.Knocked')->set(false),
Field::inst( '(view_Addresses.Place_Sign-view_Addresses.Placed_Sign)' , 'Need_Sign' )
->set( false ), // Prevent Editor from writing to this field
Field::inst('view_Residents.FullNameParty')->set(false)
->options( Options::inst()
->order( 'view_Residents.FullNameParty' )
),
Field::inst('view_ResidentFamiliarUserNames.FamiliarUserNames')->set(false)
->options( Options::inst()),
// Field::inst('view_Residents.FullName')->set(false),
Field::inst('view_Residents.Inactive')->set(false),
Field::inst('view_Residents.Ignore')->set(false),
Field::inst('view_Residents.Spoken_With')->set(false),
Field::inst('view_Residents.Confident')->set(false),
Field::inst('view_Residents.Jordan_Followup')->set(false),
Field::inst('view_Residents.Target')->set(false),
Field::inst('view_Residents.Will_Volunteer')->set(false),
Field::inst('view_Residents.Familiar')->set(false),
Field::inst('view_Residents.Okay_to_Text_or_Email')->set(false),
Field::inst('view_Residents.Party_Code')->set(false),
Field::inst('view_Residents.Deactivate_Resident')->set(false),
Field::inst('view_Residents.Voting_for_Bob')->set(false),
Field::inst('view_Residents.Voting_for_Dan')->set(false),
Field::inst('view_Residents.Dont_have_Vote')->set(false),
Field::inst('view_Residents.Dont_Call')->set(false),
Field::inst('view_Residents.Voted')->set(false),
Field::inst('view_Residents.Absentee_Mailer')->set(false),
Field::inst('view_Residents.Oct11_Mailer')->set(false),
Field::inst('view_Residents.Reposted_Mailer')->set(false),
Field::inst('view_Residents.Mailer')->set(false),
Field::inst('view_Residents.PartyCategory')->set(false),
Field::inst('Residents.VotersVotingHistory')->set(false),
Field::inst('Residents.SuperVoter')->set(false),
// Field::inst( "'0'", 'editorform') ->set( false )
Field::inst('Addresses.AddressAID')->set(false),
Field::inst('Addresses.StreetName')->set(false),
Field::inst('Addresses.AddressNumber')->set(false),
Field::inst('Addresses.Address2')->set(false),
Field::inst('Addresses.Apartment')->set(false),
Field::inst('Addresses.Ward')->set(false),
Field::inst('Addresses.District')->set(false),
Field::inst('Addresses.Zip')->set(false),
Field::inst('Addresses.AddressIgnore')->set(false),
)
->leftJoin('Addresses Addresses', 'Addresses.AddressAID', '=', 'Residents.AddressAID')
->leftJoin('v_Addresses_FromCache view_Addresses', 'view_Addresses.AddressAID', '=', 'Residents.AddressAID')
->leftJoin('v_Residents_FromCache view_Residents', 'view_Residents.ResidentAID', '=', 'Residents.ResidentAID')
->leftJoin('v_ResidentFamiliarUserNames_FromCache view_ResidentFamiliarUserNames', 'view_ResidentFamiliarUserNames.ResidentAID', '=', 'Residents.ResidentAID')
->leftJoin('view_ActLog view_ActLog', 'view_ActLog.ActLogAID', '=', 'view_Residents.LastActLogAID')
->where( 'Residents.IsProd', $IsProd, '=' )
->where( 'view_Residents.Inactive', 0, "=" )
->on( 'postCreate', function ( $editor, $id, &$values, &$row ) {
global $instanceDateTime;
logChange( $editor->db(), 'create', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF'] );
} )
->on( 'postEdit', function ( $editor, $id, &$values, &$row ) {
global $instanceDateTime;
logChange( $editor->db(), 'edit', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF'] );
} )
->on( 'postRemove', function ( $editor, &$id, &$values ) {
global $instanceDateTime;
logChange( $editor->db(), 'delete', $id, $instanceDateTime, $values, $_SERVER['PHP_SELF']);
} )
->debug(true) // Add this line
->process( $_POST )
->json();
Answers
Do you get this error on page load or when trying to export? If during export then you might want to use a server side method for exporting. See this FAQ regarding exporting with server side processing.
I don't use the Datatables supplied libraries but I'm not sure this is true. See this example. Click the
Server scripttab below the table to see the PHP script. Have you tried Search Builder with your PHP script? Is there something that isn't working properly?If you use server side processing you will need to review the above FAQ for exporting.
Allan can comment on your above PHP script.
Kevin
SearchBuilder works with the Editor PHP libraries. There is an example here.
Ah - that's a problem then. Server-side processing won't work with the export - or rather it will, but it will only export the current page of data.
This is tricky - if you need all of those fields and all of those rows, then you need all of that memory. Ideally it would be steamed out to save memory, but that's not how the Editor libraries work since it needs to construct the full JSON object.
Options I can see:
At the moment no, sorry. That would still hit the memory problem if requesting the full data.
Personally I'd look at option 4. I know file creation is a bit of a pain, although if it is a CSV export, then it shouldn't be too bad.
Allan
Thank you for the quick feedack.
When they export, it's by default honoring any states / filtering applied. So if they filter on residents who are supervoters then export, they will get all supervoters in the exports.
How would I go about doing a server-side export that is utilizing the state/filtering applied at the time?
Easiest option would be to get the id for all the rows in the current data set (
search: 'applied') and send that to the server to be used as the "WHERE" list (or empty if there is no selection).Then build the file and send to the client-side. That bit I don't publish software for I'm afraid, but I'm sure there are plenty of options out there if you want something more advanced than just CSV.
Allan
I am not sure why I thought searching wasn't working, maybe I had another issue and did some research and saw some older comments. But I just tested using serverside and searching along with paging and it seems fine
Now I need to make sure when they export, it gets all of the data.
I found posts referring to button.download.js Is this still the proper way ? I have been reviewing it and as soon as I add:
dom: 'Bfrtip',
to my datatable definition, the view changes and some of my buttons go away: search builder, column visibility, create and saved states along with a custom button that I made.
You just need to add it in with your other buttons. Sounds like you are adding the sample config provided in the button.download.js file and it's overwriting your other buttons config.
I don't think the button.download.js library supports Search Builder searches so it might not do what you want. To add to Allan's comment you can add a custom button, like this example, and use something like
rows().ids()to get the filtered row ID's. Send the IDs to the server via jQuery ajax() to execute a server script to fetch and export the filtered rows. Something like this would get the filtered row ID's:Kevin
I am using the below to try to get the ResidentAID for what's filtered. It's someewhat working but since I am using paging, it's only giving me IDs from the current page. Remember I am doing this because I need to switch to serverside and need to grab all of the IDs to setup a server side export.
var filteredRowsData = maintable.rows({ search: 'applied' }).data().map(function(row) {
return row.Residents.ResidentAID;
}).toArray();
The
rows()API will only have access to the client side rows. You will need to use client side processing for this solution to work as Allan mentioned in option 4.If you need to have server side processing enabled then you can use
searchBuilder.getDetails()to get the current SearchBuilder search terms and send that to the server. You will then need to create a query in the called script to get the appropriate data to export.Kevin
Is it possible in the serverside php to capture the query constructed by editor based on the global search and search builder? With ->debug(true) in the php, I can see the queries via the browser console.
Possibly this would work. In the custom button use
ajax.json()to grab the result of the last server side processing response. Get the SQL query string to send to the server. To get all the data you will need to remove theLIMITandOFFSETportions of the SQL query.Kevin