Dynamic CRUD Stored Procedures using PHP

Tired of writing Stored Procedures for regular CRUD operations? Here is useful PHP script, which generates the script for the required stored procedures. This code studies the table structure and creates the stored procedures accordingly.

<?php

$tableName = "Your Desired Table";

// Connection String
$conn = mysql_connect('localhost', 'root','');

// Select database
mysql_select_db('<DBNAME>', $conn);

$result = mysql_query('select * from `'.$tableName.'`');

$paramList = '(<br/>';

$tableIdField = mysql_field_name($result, 0);

$insertFields = "(";
$insertValues = "(";

$updateList = "";

for($i=0;$i< mysql_num_fields($result);$i++){
     $fieldName = mysql_field_name($result, $i);
     $fieldType = mysql_field_type($result, $i);

     if($i>0){
          $insertFields = $insertFields.$fieldName.',';
          $insertValues = $insertValues.' _'.$fieldName.',';
     }

     $updateList = $updateList.' '.$fieldName.'='.' _'.$fieldName.',';

     $fieldFormat = '';
     $fieldFormat = $fieldFormat.' _'.$fieldName;
     $fieldFormat = $fieldFormat.' '.$fieldType;
     $fieldFormat = $fieldFormat.' '.',<br/>';

     $paramList = $paramList.' '.$fieldFormat;
}

$insertFields = $insertFields.')';
$insertValues = $insertValues.')';

$updateList = $updateList.')';

$paramList = $paramList.')<br/>';
$paramList = str_replace('int','int(11)',$paramList);
$paramList = str_replace('string','text',$paramList);
$paramList = str_replace(',)',')',$paramList);

$insertFields = str_replace(',)',')',$insertFields);
$insertValues = str_replace(',)',')',$insertValues);

$updateList = str_replace(',)','',$updateList);

$spText = '<br/><br/>DELIMITER $<br/>';
$spText .= ' <br/>DROP PROCEDURE IF EXISTS Manage'.$tableName.' $<br/>';
$spText .= ' <br/>CREATE PROCEDURE Manage'.$tableName.'<br/>';
$spText .= $paramList;
$spText .= ' <br/>BEGIN<br/>';
$spText .= ' <br/>IF _'.mysql_field_name($result, 0).' = 0 THEN<br/>';
$spText .= ' INSERT INTO '.$tableName.' '.$insertFields;
$spText .= ' VALUES '.$insertValues.';';
$spText .= '<br/> ELSE<br/>';
$spText .= ' UPDATE '.$tableName.' SET '.$updateList;
$spText .= ' WHERE '.$tableIdField.'=_'.$tableIdField.';';
$spText .= ' <br/>END IF;<br/>';
$spText .= ' <br/>END $<br/>';
$spText .= ' <br/>DELIMITER ;<br/>';

echo '<br/><strong>Insert/Update stored Procedure: </strong><br/>';

echo $spText;
$spText='';

echo '<br/><br/><br/><br/>';

$spText = '<br/><br/>DELIMITER $<br/>';
$spText .= ' <br/>DROP PROCEDURE IF EXISTS Read'.$tableName.' $<br/>';
$spText .= ' <br/>CREATE PROCEDURE Read'.$tableName.'<br/>';
$spText .= '(_id int(11))';
$spText .= ' <br/>BEGIN<br/>';
$spText .= ' <br/>IF _id = 0 THEN<br/>';
$spText .= ' SELECT * FROM '.$tableName.';';
$spText .= '<br/> ELSE<br/>';
$spText .= ' SELECT * FROM '.$tableName.' WHERE ID = _ID;';
$spText .= ' <br/>END IF;<br/>';
$spText .= ' <br/>END $<br/>';
$spText .= ' <br/>DELIMITER ;<br/>';

echo '<br/><strong>Select All/specific record stored Procedure: </strong><br/>';

echo $spText;

?>

Comments