Memory issues - Custom plugin / custom table

Dear All,

I have a custom set of mysql tables which contain all in all around 1,000,000 records and I wanted to create some plugins to sit on top of this data within Expression Engine.

The idea is to return data in an EE kind of format so that the UX guys I’m working with can create their own journeys on the site without having to get their hands dirty with PHP code;

{exp:handset:all}
{id} {name}
{/exp:handset:all}

This is the plugin code I have written so far;

<?php

if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Handset {
    
    public function Handset(){
        $this->EE =& get_instance(); 
    }
    
    function all(){
        $query = $this->EE->db->query("SELECT * FROM tablename LIMIT 0, 10");
        if ($query->num_rows() > 0){
            foreach($query->result_array() as $row){
                
                $this->pageVars[] = array(
                    'id' => $row['id'], 
                    'name' => $row['name']
                );
                
            }
        }
        return $this->EE->TMPL->parse_variables($this->EE->TMPL->tagdata, $this->pageVars);
    }
}

/* End of file pi.handset.php */ 
/* Location: ./system/expressionengine/third_party/handsets/pi.handset.php */

Whilst using this plugin within an EE template I am getting the below error; Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 5965323 bytes) in /system/codeigniter/system/database/DB_cache.php on line 144

PhpMyAdmin returns that query in 0.0002 sec on the same server and the table I am trying to access has around 1800 records in.

I have tried doubling the memory limit for php to 64MB (have also tried 256MB to see if that helped), this resulted in the server dropping the connection. I have also doubled the MySql query cache and have increased the amount of connections too.

Using vanilla PHP embeded within the template works fine with no errors;

<?
mysql_connect('...', '...', '...');
mysql_select_db('tablename');

$oResult = mysql_query("SELECT * FROM tablename LIMIT 0, 50");

while($row = mysql_fetch_array($oResult)){ ?>

     <div>    
        <?=$row[
        <a href="/index.php/handsets/detail/<?=$rowid">;?>"]<?=$row['name'];?></a>
    </div>
 <? } ?>

This is one of the more simple queries that I wanted to perform within a plugin. Most of them will have inner joins in order to get the data out I need to display.

Any insights into getting this kind of thing working would be greatly appreciated! 😊

Thanks in advance, Ben

Moved to Development and Programming by Moderator