November 25, 2010

PHP - Generate classes for all tables from Mysql database:


$db_list = mysql_list_dbs($connection);
while ($row = mysql_fetch_object($db_list)) {
if ($row->Database==$db){
//===== Tables
$db = mysql_list_tables($row->Database);
$table_list = mysql_num_rows($db);
for ($i = 0; $i < $table_list; $i++) {
$str = "<?php\n";
$table_name = mysql_tablename($db, $i);
$table_temp = explode("_",$table_name);
if(count($table_temp)>0 && $table_temp[0]=='tbl'){
$str .= "class ".ucfirst($table_name)."{\n";
//===== Fields
$recordset = mysql_query("SELECT * FROM " . $table_name);
if($recordset){
$num_field = mysql_num_fields($recordset);
//echo $num_field.", \n";
$str_set = "";
$str_get = "";
$str_field = "";
$str_field2 = "";
$str_variable = "";
$str_field_update = "";
for($j = 0; $j < $num_field; $j++){
$field_name = mysql_field_name($recordset,$j);

// temporary fields and arguments
$str_field1 .= $field_name.", ";
$str_argument .= "$".$field_name.", ";
if(mysql_field_type($recordset, $j)== 'int'){
$str_field2 .= "$".$field_name.", ";
$str_field_update .= $field_name."= $".$field_name.", ";
}else{
$str_field2 .= "'$".$field_name."', ";
$str_field_update .= $field_name."= '$".$field_name."', ";
}
$str_variable .= '$this->'.$field_name."= $".$field_name.";\n";
// properties
$str_field .= "private $".$field_name.";\n";//." (". mysql_field_type($recordset,$j)."),\n ";
// method set
$str_set .= "public function set_".$field_name.'($value)'."{\n";
$str_set .= '$this->'.$field_name.' = $value;'."\n}\n";
// method get
$str_get .= "public function get_".$field_name."(){\n";
$str_get .= 'return $this->'.$field_name.";\n}\n";
}
$str .= "//Properties";
$str .= "\n".$str_field;
$str .= "\n//set value";
$str .= "\n".$str_set;
$str .= "\n//get value";
$str .= "\n".$str_get;
$str_field1 = substr($str_field1,0,strlen($str_field1)-2);
$str_field2 = substr($str_field2,0,strlen($str_field2)-2);
$str_argument = substr($str_argument,0,strlen($str_argument)-2);
// insert
$str_insert = "public function insert(".$str_argument."){";
$str_insert .= "\n".'$str = '."\"INSERT INTO ".$table_name."(".$str_field1.") VALUES(".$str_field2.")\";";
$str_insert .= "\n".'$rst = mysql_query($str) or die(mysql_error());';
$str_insert .= "\n".'if($rst){'."\n".substr($str_variable,0,strlen($str_variable)-1)."\n}\n}\n";
// update
$str_update = "public function update(".$str_argument."){";
$str_update .= "\n".'$str = '."\"UPDATE ".$table_name." SET ".substr($str_field_update,0,strlen($str_field_update)-2).' WHERE id=$id";';
$str_update .= '$rst = mysql_query($str) or die(mysql_error());';
$str_update .= "\n".'if($rst){'."\n".substr($str_variable,0,strlen($str_variable)-1)."\n}\n}\n";
// select
$str_select_one = 'public function get_one($id){';
$str_select_one .= "\n".'$str = "SELECT * FROM '.$table_name.' WHERE id=$id";';
$str_select_one .= "\n".'$rst = mysql_query($str);';
$str_select_one .= "\n".'if($rst) return mysql_fetch_object($rst);'."\n}\n";
$str_select_paging = 'public function get_all_paging($page, $per_page){';
$str_select_paging .= "\n".'$str = "SELECT * FROM '.$table_name.' limit $page, $per_page";';
$str_select_paging .= "\n".'$rst = mysql_query($str);';
$str_select_paging .= "\n".'if($rst)return $rst;'."\n}\n";
$str_select_all = 'public function get_all(){';
$str_select_all .= "\n".'$str = "SELECT * FROM '.$table_name.'";';
$str_select_all .= "\n".'$rst = mysql_query($str);';
$str_select_all .= "\n".'if($rst)return $rst;'."\n}\n";
//delete
$str_delete = 'public function delete($id){';
$str_delete .= "\n".'$str = "DELETE FROM '.$table_name.' WHERE id=$id";';
$str_delete .= "\n".'if(mysql_query($str)) return 1;'."\n}\n";


$str .= "\n//insert";
$str .= "\n".$str_insert;
$str .= "\n//update";
$str .= "\n".$str_update;
$str .= "\n//select";
$str .= "\n".$str_select_one;
$str .= "\n".$str_select_paging;
$str .= "\n".$str_select_all;
$str .= "\n//delete";
$str .= "\n".$str_delete;
$str .= "\n}\n?>";

file_put_contents("class/$table_name.php",$str);
}
// break;
}
}
}
}
echo "\nDone!";

Note: It is my first version, so make sure that all table's names are started by "tbl_" and all tables must have field "id" as primary key.