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;

?>