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

crcucbcrcucb Posts: 89Questions: 30Answers: 0
edited October 8 in Free community support

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.

  1. 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?
  2. 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?
  3. 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

  • kthorngrenkthorngren Posts: 22,293Questions: 26Answers: 5,125
    edited October 8

    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

    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.

    From what I read, SearchBuilder doesn't natively work with php serverside, but it does with .net.

    I don't use the Datatables supplied libraries but I'm not sure this is true. See this example. Click the Server script tab 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

  • allanallan Posts: 65,206Questions: 1Answers: 10,804 Site admin

    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.

    SearchBuilder works with the Editor PHP libraries. There is an example here.

    One of the reasons the residents page has so many fields is for exporting

    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:

    1. Find some way of getting your hosting provider to increase the memory limit or change host (although you will just run into this again in future if the records continue to grow).
    2. Use server-side processing, and provide some other mechanism for exporting the data.
    3. Reduce the number of fields to reduce the memory requirements.
    4. Do server-side export and thus reduce the number of fields, but maintain client-side processing.

    Is there a way to separate those fields but somehow pull them in when exporting?

    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

  • crcucbcrcucb Posts: 89Questions: 30Answers: 0

    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?

  • allanallan Posts: 65,206Questions: 1Answers: 10,804 Site admin

    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

  • crcucbcrcucb Posts: 89Questions: 30Answers: 0

    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.

  • kthorngrenkthorngren Posts: 22,293Questions: 26Answers: 5,125
    edited October 8

    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:

    table.rows( {search: 'applied'} ).ids();
    

    Kevin

  • crcucbcrcucb Posts: 89Questions: 30Answers: 0

    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();

  • kthorngrenkthorngren Posts: 22,293Questions: 26Answers: 5,125
    edited 10:39AM

    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

Sign In or Register to comment.