[Phplib-users] sys_procedures

[Bob B. <bo...@iN...>]

Hi --

OK, here goes ...

==================================================
"sys_procedures" is a table which contains the actual SQL queries ...

CREATE TABLE sys_procedures (
   id int(11) NOT NULL auto_increment,
   name varchar(20) NOT NULL default '',
   description varchar(40) default NULL,
   sql text,
   dt_create datetime NOT NULL default '0000-00-00 00:00:00',
   dt_modify timestamp(14) NOT NULL,
   argc int(2) unsigned NOT NULL default '0',
   PRIMARY KEY  (id),
   KEY name (name,dt_create,dt_modify)
) TYPE=MyISAM;

==================================================
Within your DB class, add the following:

   /* Store procedure enhancement: configurable*/
   var $procedure_table = "sys_procedures";
   var $proc_name;
   var $sql = array();
   var $argc;
   var $finalQuery;

    function proc($name) {
       if ( !is_array($this->sql) ) {
          $this->sql[$name] = "";
          $this->argc[$name] = 0;
       }
       if ( strlen($name) > 0) {
          ##This should be the only line of embedded Sql in the application
          $qry = "SELECT sql, argc FROM ".$this->procedure_table." WHERE 
name = '".$name."' ";
          if (strlen($this->sql[$name]) == 0  && $this->query($qry)){
             $this->next_record();
             $this->sql[$name] = $this->f("sql");
             $this->argc[$name] = $this->f("argc");
          }
          $this->proc_name = $name;
          return true;
       }
       return false;
    }

    function execproc($argv="") {
       $this->finalQuery = $this->sql[$this->proc_name];
       if (is_array($argv)) {
          reset($argv);
          $c = count($argv);
          for ($i=0 ; $i<$c ; $i++) {
             $value = $argv[key($argv)];
             $this->finalQuery = 
ereg_replace("{".strtoupper(key($argv))."}", $value, $this->finalQuery);
             next($argv);
          }
       }
       $qry = $this->finalQuery;
       if (( count($argv) >= $this->argc[$this->proc_name] ) || 
($this->argc[$this->proc_name] == 0) ) {
          return $this->query($qry);
       } else {
          return false;
       }
    }

==================================================
Let's say you have an SQL query that searches 400,000 records for "Madonna" 
and "DVD" ... the data is normalized across a half-dozen tables, and the 
query uses JOIN and every MySQL trick I know to speed things up.  I'm also 
going to want to search (elsewhere) for "CD", and "Collector", and 
"VHS".  I used to debug the query in DVDs, then cut-and-paste it into the 
CD script, change the variables, and debug it all over again ... ditto for 
Collector, VHS, and so on.

Or, in the CD PHP code, you can do the following:
          $word = "Madonna"; $fmt = "CD";
          $query = $db->proc("get_2_specials");
          $argv = array(FORMAT=>"$fmt",TYPE=>"$word");
          $db->execproc($argv);

Or, in the DVD "department", the following:
          $word = "Madonna"; $fmt = "DVD";
          $query = $db->proc("get_2_specials");
          $argv = array(FORMAT=>"$fmt",TYPE=>"$word");
          $db->execproc($argv);

For clarity, I've deleted a dozen other members of the array which get 
passed to the class function ... but I'm sure you get the idea.  The 
"finalQuery" variable, by the way, is *very* useful for debugging!

==================================================
In practice ...

1. I write the query and test it using hard values and "explain" in 
PhpMyAdmin ...

2. when the tables are optimized and the query is working the way I want, I 
copy the query into my PHP script and substitute variables for the hard 
values ...

3. when that's working the way I want, I re-write the query substituting 
placeholders for the variables and store that in the sys_procedures table ...

4. finally, I substitute the 3 lines (or so) of PHP code for the query 
itself in my script, and I'm done.

The tables are now optimized for the query, and the debugged query is 
available to all other scripts (I can cut-and-paste just the 3 lines of 
code) ... and I may get out of here on time tonight!

==================================================

Please let me know if I've left anything out ... and give a shout if I can 
help ...

Bob.