Simple Stored Procedure Emulation with PHP/mySQL
April 10th, 2002My “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.
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…
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.
So Brent.. when are you going to update this to make use of caching, etc? It would be quite handy
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 
Brent….why be such a slacker?
Evan – slacking is one of the prime motives of the hacker ethic!
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”