h1

Simple Stored Procedure Emulation with PHP/mySQL

April 10th, 2002

My “third time, automate” rule came in handy again recently. I’m doing all sorts of PHP/mySQL work and I found myself hacking together the same SQL queries over and over with just some different filter or sort order. Being used to stored procedures, I was missing the easier syntax.

Here’s what I came up with….

rather than:

sql = "select height, name from students where height>$minHeight order by $orderCol";

I wanted to say something more along the lines of:

getStudentsByHeight( minHeight, order );

Of course, I could wrap every such query in a PHP function, but I wanted to make it feel more like a stored procedure.

So, I created a procs table in my database:

CREATE TABLE procs (
  name varchar(50),
  description text,
  sqltext text,
)

Then I created a record:

name: 
  getStudentsByHeight
description: 
  getStudentsByHeight( minHeight [in cm] , order [by 1=height or 2=name])
sqltext: 
  select height, name from students where height>$1 order by $2

And then I created a function I called sproc(). This function takes the name of a procedure, and its parameters in an array, so you can call it like this:

sql = sproc( 'getStudentsByHeight', array( 150, 2 ) );

sproc() iterates through the parameters and does a string replacement on the sql text for each. Then it returns the sql.

function sproc($proc,$parms){
  $sql="select sqltext from procs where name='$proc'";
  $rs=mysql_query($sql);
  if (mysql_num_rows($rs)>0){
    $row = mysql_fetch_row($rs);
    $sqlText = $row[0];
    if(isset($parms)){
      if(is_array($parms)){
        for($i=0;$i<count($parms);$i++){
          $sqlText=str_replace('$'.($i+1),$parms[$i],$sqlText);
        }
      }else{
        $sqlText=str_replace('$1',$parms,$sqlText);
      }    
    }
    return $sqlText;
  }else{
    print "<div>procedure [$proc] does not exist</div>";
    exit();
  }
}

Now when I want to change the sql to include more fields and therefore more ordering choices, I only have to change it in the database, without ever touching the code!

select height, name, shoesize from students where height>$1 order by $2

Of course, since you’d have to redo your display for the extra field, it’s not complete without a generic table building function. That brings the entire table generation code to this line:

print tblFromRS(mysql_query(sproc('getStudentsByHeight',array(150,2))));
function tblFromRS($rs){
  $s="<table border="1"><tr>";
  while($fld = mysql_fetch_field($rs))$s.="<th>$fld->name</th>";
  $s.="</tr>\n";
  while($row = mysql_fetch_row($rs)){
    $s.="<tr>";
    for($i=0;$i<mysql_num_fields($rs);$i++)$s.="<td>$row[$i] </td>";
    $s.="</tr>\n";  
  }
  $s.="</table>\n";
  return $s;
}

Now I can manage my sql queries all in the database. On any new project, I just bring across the generic functions and the procs table and off I go.

It’s no replacement for real stored procedures, but it’s a good stopgap.

7 comments to “Simple Stored Procedure Emulation with PHP/mySQL”

  1. cool idea man. One thing: it looks like you’d be doing twice the queries this way. Maybe you could have some sort of caching function where it dumps the procs table to a .php file as some multi-dimensional array:

    $res = mysql_query(“SELECT * FROM procs;”);
    while($row = mysql_fetch_array($res)) {
      $name = $row[name];
      $procs[$name] = array(
        ‘description’=>$row[description],
        ‘sqltext’=>$row[sqltext]
      );
    }

    Then, you could change the first line of the sproc() function:

    function sproc($proc,$parms){
      $sql=”select sqltext from procs where name=’$proc'”;

    TO

    function sproc($proc,$parms) {
      $sql=$GLOBALS[‘procs’][$proc][sql];

    maybe i’m a bit anal about caching and all that…


  2. That’s a good idea. I wasn’t thinking of efficiency as much as my code maintainability. It’s all me, me, me, isn’t it!

    There’s a few places one could go with this. It’s just a germ of an idea, really.


  3. So Brent.. when are you going to update this to make use of caching, etc? It would be quite handy 🙂


  4. Or I could just use the one outlined above 🙂 It would just be interesting to see how far we could take this.. or just wait for stored procedures in MySQL 🙂


  5. Brent….why be such a slacker?


  6. Evan – slacking is one of the prime motives of the hacker ethic!


  7. Hi…
    Question.
    I just don’t get the Caching stuff.

    i’d like to do this

    a stored procedure doing:

    “Select * from TableX”

    All of it’s information would be saved in Cache, And, calling it, i would pass some
    constrains like:

    “where id = @User_id”