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
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Manage'.$tableName.'
lt;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
lt;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
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Read'.$tableName.'
lt;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
lt;br/>';

$spText .= ' <br/>DELIMITER ;<br/>';
echo '<br/><strong>Select All/specific record stored Procedure: </strong><br/>';
echo $spText;
?>
Comments
 comments