Template assign block vars - memory exhausted

[Slechtvalk]

Well its a little bit weird,

php memory limit was 256M

my sql select

Code: Select all

SELECT ucase(u.username) AS username, u.user_id AS uid, p.name AS platoon, p.pid AS pid, c.name AS company, c.id AS cid, m.cmdr AS cmdr, m.in_charge AS rank FROM phpbb3_users u INNER JOIN phpbb3_user_group g ON u.user_id=g.user_id LEFT OUTER JOIN phpbb3_drop_platoon_members m ON u.user_id=m.uid LEFT JOIN phpbb3_drop_platoon p ON p.pid=m.pid LEFT JOIN phpbb3_drop_company c ON p.cid=c.id WHERE g.group_id=145

result is 30 rows within 0.0018 seconds directly with phpmyadmin.

Code: Select all

function get_pilots_and_units() {
  global $template;
  $sql="SELECT ucase(u.username) AS username, u.user_id AS uid, p.name AS platoon, p.pid AS pid, c.name AS company, c.id AS cid, m.cmdr AS cmdr, m.in_charge AS rank FROM phpbb3_users u INNER JOIN phpbb3_user_group g ON u.user_id=g.user_id LEFT OUTER JOIN phpbb3_drop_platoon_members m ON u.user_id=m.uid LEFT JOIN phpbb3_drop_platoon p ON p.pid=m.pid LEFT JOIN phpbb3_drop_company c ON p.cid=c.id WHERE g.group_id=145";
  while($row=mysql_fetch_array(mysql_query($sql), MYSQL_ASSOC)) {
  $template->assign_block_vars('changepilot', array(
        'USERNAME'        => $row["username"],
        'UID'        => $row["uid"],
        'COMPANY'        => $row["company"],
        'PLATOON'        => $row["platoon"],
        'CID'        => $row["cid"],
        'PID'        => $row["pid"],
        'CMDR'        => $row["cmdr"],
        'RANK'        => $row["rank"],
  ));
  }
}

this function caused the memory limit, it says on the line where " ));" is.
So i adjusted php memory step by step to a value of 2560M and now its reaching the time limit....
i dont see the problem, any idea how i can optimize or change the code to a fast and running function?

with 1024 memory limit:

( ! ) Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 80 bytes) in /var/www/blutigetempler/forum/phpbb/template/context.php on line 224
Call Stack
# Time Memory Function Location
1 0.0002 243488 {main}( ) ../rooster.php:0
2 0.3190 10752056 get_pilots_and_units( ) ../rooster.php:82
3 193.5808 1073419448 phpbb\template\base->assign_block_vars( ) ../mechfunctions.php:16
4 193.5808 1073419448 phpbb\template\context->assign_block_vars( ) ../base.php:112

Thank you!

[Slechtvalk]

LOL

changed code to

Code: Select all

function get_pilots_and_units() {
  global $template;
  $sql="SELECT ucase(u.username) AS username, u.user_id AS uid, p.name AS platoon, p.pid AS pid, c.name AS company, c.id AS cid, m.cmdr AS cmdr, m.in_charge AS rank FROM phpbb3_users u INNER JOIN phpbb3_user_group g ON u.user_id=g.user_id LEFT OUTER JOIN phpbb3_drop_platoon_members m ON u.user_id=m.uid LEFT JOIN phpbb3_drop_platoon p ON p.pid=m.pid LEFT JOIN phpbb3_drop_company c ON p.cid=c.id WHERE g.group_id=145";
  $changerooster=mysql_query($sql);
  while($row=mysql_fetch_array($changerooster, MYSQL_ASSOC)) {
  $template->assign_block_vars('changepilot', array(
        'USERNAME'        => $row["username"],
        'UID'        => $row["uid"],
        'COMPANY'        => $row["company"],
        'PLATOON'        => $row["platoon"],
        'CID'        => $row["cid"],
        'PID'        => $row["pid"],
        'CMDR'        => $row["cmdr"],
        'RANK'        => $row["rank"],
  ));
  }
}

works??!?

[rxu]

while($row=mysql_fetch_array(mysql_query($sql), MYSQL_ASSOC))

LOL

Yeah, you've run into infinite loop with the mysql_query($sql), so this is logical that the script has exausted all reasonable memory limits.

[Slechtvalk]

Thank you, after a while i do understand. But amazing is, i am doing it the wrong way long time, and nver got so stucked, but on the other side, this is one of my largest joins.
Now its clear, changed every function. *blushes*

[brunoais]

Sounds like, in the past, you only made DB queries that returned 1 or 0 results.

[Paul]

Sounds like, in the past, you only made DB queries that returned 1 or 0 results.

Nope. The complete statement is executed every loop. If the result from that statement is true, it loops again. As you have the actual code to run the query withing the statement for the loop, the query ran every time the loop ran, causing a infinite loop.

[brunoais]

Oh right... good point, my bad.