Search code examples
phpmysqltheoryapplication-design

Reusing MySQL results

I'm having somewhat theoretical question: I'm designing my own CMS/app-framework (as many PHP programmers on various levels did before... and always will) to either make production-ready solution or develop various modules/plugins that I'll use later.

Anyway, I'm thinking on gathering SQL connections from whole app and then run them on one place:

index.php:
<?php
  include ('latestposts.php');
  include ('sidebar.php');
?>

latestposts.php:
<?php
  function gather_data ($arg){ $sql= ""; }
  function draw ($data) {...}
?>

sidebar.php:
<?php
  function gather_data ($arg){ $sql= ""; }
  function draw ($data) {...}
?>

Now, while whole module system application is yet-to-be-figured, it's idea is already floating somewhere in my brain. However, I'm thinking, if I'm able to first load all gather_data functions, then run sql and then run draw functions - and if I'm able to reuse results!

If, in example, $sql is SELECT * FROM POSTS LIMIT 10 and $sql2 is SELECT * FROM POSTS LIMIT 5, is it possible to program PHP to see: "ah, it's the same SQL, I'll call it just once and reuse the first 5 rows"?

Or is it possible to add this behavior to some DRM?

However, as tags say, this is still just an idea in progress. If it proves to be easy to accomplish, then I will post more question how :)

So, basically: Is it possible, does it make sense? If both are yes, then... any ideas how?

Solution

  • Don't get me wrong, that sounds like a plausible idea and you can probably get it running. But I wonder if it is really going to be beneficial. Will it cause a system to be faster? Give you more control? Make development easier?

    I would just look into using (or building) a system using well practiced MVC style coding standards, build a good DB structure, and tweak the heck out of Apache (or use something like Lighttpd). You will have a lot more widespread acceptance of your code if you ever decide to make it open source, and if you ever need a hand with it another developer could step right in and pick up the keyboard.

    Also, check out query caching in MySQL--you will see a similar (though not one-to-one) benefit from caching your query results server side with regard to your query example. Even better that is stored in server memory so PHP/MySQL overhead is dropped AND you don't have to code it.

    All of that aside, I do think it is possible. =)