TecBrat
02-05-2009, 11:33 PM
I am not sure if I should post this here, or in the PHP forum.
I need to point out that I am still very inexperienced with MySQL, so if I do something stupid, politly let me know. :D
I wrote this function to help me create tables, hoping to save myself time and trouble down the line. Would someone care to look it over and see if there are any glaring problems with it? I tested it a little bit and it does as I expected, but that doesn't mean I havn't missed some important point where it would come back and bite me later.
<?php
//fn_maketable.php
//**********************************************************
// cw_maketable depends on cw_decode_cols
// db_credentials [array, host,db_user,db_name,pass]
// $new_table [string the name of the new table]
// $cols [array each element is a pipe "|" separated list, column name | data type.] Decoded by function decode_cols
// primary[string]) If null, a column is added and set to primary like PRIMARY KEY(${new_table}_id),
// otherwise this string should be a col name to be set as primary
// auto[string] If null, ${new_table}_id INT NOT NULL AUTO_INCREMENT, is added to the query, otherwise this string
// should be a col name to auto increment. Will be set to int not null even if the listing in $cols says something else.
// if either primary or auto is set, both defaults will be disabled. They are dependant on each other.
//***********************************************************
function cw_maketable ($db_credentials,$new_table,$cols,$primary=null,$auto=null)
{
if (is_array($db_credentials))
{
foreach($db_credentials as $key=>$value)
{
$$key=$value;
}
}
else
{
return false;
}
$dbh=mysql_connect ($host,$db_user,$pass) or
die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$db_name");
if(!isset($primary) && !isset($auto))
{
$set_auto_inc="${new_table}_id INT NOT NULL AUTO_INCREMENT,";
$set_primary=", PRIMARY KEY(${new_table}_id)";
}
else
{
foreach($cols as $key=>$column)
{
if($primary)
{
if (ereg($primary,$column))
{ //not perfect, but a good start
$set_primary=", PRIMARY KEY($primary)";
}
}
if($auto)
{
if (ereg($auto,$column))
{ //not perfect, but a good start
$set_auto_inc="$auto INT NOT NULL AUTO_INCREMENT,";
$myunset=$key;
}
}
if($myunset){unset($cols[$myunset]);}
}
} // this is the end of else. Run when either or both $primary and $auto are set.
$columns=cw_decode_cols($cols);
$query="CREATE TABLE $new_table($set_auto_inc $columns $set_primary)";
$result=mysql_query($query);
if($result)
{
return true;
}
else
{
return false;
}
} //end cw_maketable function
function cw_decode_cols($cols){
foreach ($cols as $string){
$string=str_replace(',','',$string);
$this_column=str_replace('|',' ',$string).',';
$columns.=$this_column;
}
$columns=rtrim($columns,',');
return $columns;
}
?>
and
<?php
include('fn_maketable.php');
$db_credentials[host]='localhost';
$db_credentials[db_user]='someone';
$db_credentials[db_name]='mydatabase';
$db_credentials[pass]='letmein';
$new_table='people';
$cols[0]='firstname|VARCHAR(20)';
$cols[1]='lastname|VARCHAR(20)';
//$cols[2]='peoplecounter|VARCHAR(20)';
$cols[2]='address|VARCHAR(40)';
$cols[3]='city|VARCHAR(20)';
$cols[4]='state|VARCHAR(20)';
$cols[5]='zip|INT';
$people_made=cw_maketable ($db_credentials,$new_table,$cols);
//or un-comment peoplecounter above and re-number the array and use this:
//$people_made=cw_maketable ($db_credentials,$new_table,$cols,'peoplecounter','peoplecounter');
if($people_made){echo("The table was made.");}else{echo ("There was an error. The table was not made.");}
?>
Thanks for looking it over and I'd appreciate any feedback, even if it is being told I've done something stupid. (as long as you explain why it's stupid and point me in a more intelligent direction.:p)
I need to point out that I am still very inexperienced with MySQL, so if I do something stupid, politly let me know. :D
I wrote this function to help me create tables, hoping to save myself time and trouble down the line. Would someone care to look it over and see if there are any glaring problems with it? I tested it a little bit and it does as I expected, but that doesn't mean I havn't missed some important point where it would come back and bite me later.
<?php
//fn_maketable.php
//**********************************************************
// cw_maketable depends on cw_decode_cols
// db_credentials [array, host,db_user,db_name,pass]
// $new_table [string the name of the new table]
// $cols [array each element is a pipe "|" separated list, column name | data type.] Decoded by function decode_cols
// primary[string]) If null, a column is added and set to primary like PRIMARY KEY(${new_table}_id),
// otherwise this string should be a col name to be set as primary
// auto[string] If null, ${new_table}_id INT NOT NULL AUTO_INCREMENT, is added to the query, otherwise this string
// should be a col name to auto increment. Will be set to int not null even if the listing in $cols says something else.
// if either primary or auto is set, both defaults will be disabled. They are dependant on each other.
//***********************************************************
function cw_maketable ($db_credentials,$new_table,$cols,$primary=null,$auto=null)
{
if (is_array($db_credentials))
{
foreach($db_credentials as $key=>$value)
{
$$key=$value;
}
}
else
{
return false;
}
$dbh=mysql_connect ($host,$db_user,$pass) or
die('Cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$db_name");
if(!isset($primary) && !isset($auto))
{
$set_auto_inc="${new_table}_id INT NOT NULL AUTO_INCREMENT,";
$set_primary=", PRIMARY KEY(${new_table}_id)";
}
else
{
foreach($cols as $key=>$column)
{
if($primary)
{
if (ereg($primary,$column))
{ //not perfect, but a good start
$set_primary=", PRIMARY KEY($primary)";
}
}
if($auto)
{
if (ereg($auto,$column))
{ //not perfect, but a good start
$set_auto_inc="$auto INT NOT NULL AUTO_INCREMENT,";
$myunset=$key;
}
}
if($myunset){unset($cols[$myunset]);}
}
} // this is the end of else. Run when either or both $primary and $auto are set.
$columns=cw_decode_cols($cols);
$query="CREATE TABLE $new_table($set_auto_inc $columns $set_primary)";
$result=mysql_query($query);
if($result)
{
return true;
}
else
{
return false;
}
} //end cw_maketable function
function cw_decode_cols($cols){
foreach ($cols as $string){
$string=str_replace(',','',$string);
$this_column=str_replace('|',' ',$string).',';
$columns.=$this_column;
}
$columns=rtrim($columns,',');
return $columns;
}
?>
and
<?php
include('fn_maketable.php');
$db_credentials[host]='localhost';
$db_credentials[db_user]='someone';
$db_credentials[db_name]='mydatabase';
$db_credentials[pass]='letmein';
$new_table='people';
$cols[0]='firstname|VARCHAR(20)';
$cols[1]='lastname|VARCHAR(20)';
//$cols[2]='peoplecounter|VARCHAR(20)';
$cols[2]='address|VARCHAR(40)';
$cols[3]='city|VARCHAR(20)';
$cols[4]='state|VARCHAR(20)';
$cols[5]='zip|INT';
$people_made=cw_maketable ($db_credentials,$new_table,$cols);
//or un-comment peoplecounter above and re-number the array and use this:
//$people_made=cw_maketable ($db_credentials,$new_table,$cols,'peoplecounter','peoplecounter');
if($people_made){echo("The table was made.");}else{echo ("There was an error. The table was not made.");}
?>
Thanks for looking it over and I'd appreciate any feedback, even if it is being told I've done something stupid. (as long as you explain why it's stupid and point me in a more intelligent direction.:p)