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”