Php Programming Code Examples

Php > Database Related Code Examples

MySQL table DB PHP code generator



MySQL table DB PHP code generator

<? 

//control code 
if(!isset($_POST['submit'])){ 
  show_form(); 
}else{ 
  generate_scripts(); 
}//end if 

//
//                              show form function 
//

function show_form() 
{ 
echo " 
  <html><body> 
  <form action=".$_SERVER['PHP_SELF']." method=post> 
  <table> 
    <tr><td>Table Name:</td><td><input type='text' name='tablename' size='25'></td></tr> 
    <tr><td>DB Name:</td><td><input type='text' name='dbname' size='25'></td></tr> 
    <tr><td>User Name:</td><td><input type='text' name='uname' size='25'></td></tr> 
    <tr><td>Password:</td><td><input type='text' name='pass' size='25'></td></tr> 
    <tr><td>Host:</td><td><input type='text' name='host' size='25'></td></tr> 
    <tr><td>Get / Post:</td><td><select name='gp_type'> 
                                <option value='_POST'>Post 
                                <option value='_GET'>Get 
                              </select>      
    </td></tr> 
    <tr><td>Require addslashes / stripslashes:</td><td><input type='checkbox' alt='click to add' name='slashes' value='yes'> 
    <tr><td colspan=2><input type='submit' name='submit' value='generate scripts'></td></tr> 
    </table> 
    </form> 
  </body> 
  </html>"; 


} 

//
//                              generate code function 
//

function generate_scripts() 
{ 
  global $dbname; 

  //initialize variables 
  $table_name     = ''; 
  $dbname         = ''; 
  $uname          = ''; 
  $pass           = ''; 
  $host           = ''; 
  $type           = ''; 
  $slashes        = ''; 
  $pk_id          = 0; 
  $pk_num         = 0; 
  $sql            = ''; 
  $update_query   = ''; 
  $insert_query   = ''; 
  $cnt            = 0; 
  $my_global      = 'global '; 

  //get form data 
  $table_name = $_POST['tablename']; 
  $dbname     = $_POST['dbname']; 
  $uname      = $_POST['uname']; 
  $pass       = $_POST['pass']; 
  $host       = $_POST['host']; 
  $type       = $_POST['gp_type']; 
  $slashes    = $_POST['slashes']; 

  $numeric_field_types_array = array('int','tin','flo','dec','big, dou','sma','med'); 


  //sql statement 
  $sql = "show columns from $dbname.$table_name"; 

  //connection info 
  if (!($conn=mysql_connect($host, $uname, $pass)))  { 
      printf("error connecting to DB by user = $uname and pwd=$pass"); 
      exit; 
  } 

  $db=mysql_select_db($dbname,$conn) or die("Unable to connect to database1"); 

  //run query 
  $result = mysql_query($sql, $conn)or die("Unable to query local database <b>". mysql_error()."</b><br>$sql"); 

  if (!$result){ 
    echo "database query failed. try again"; 
    show_form(); 
    die(); 
  }// end if 

  //do the results and generate the code 
  while ($rows = mysql_fetch_array($result)){ 

    //get the data set and stick into a set of arrays 
    $fields[]   = $rows[0]; 
    $types[]    = $rows[1]; 
    $keys[]     = "". $rows[3]; 
    $nulls[]    = "". $rows[2]; 
    $defaults[] = "". $rows[4]; 
    $extras[]   = "". $rows[5]; 
  } 
  $cnt = count($fields); 
  //get the primary key for the table 
  foreach($keys as $key => $value){ 
    if ($value=="PRI"){ 
      $pk_id = $key; 
      if (strtolower(substr($types[$pk_id], 0, 6)) != "varcha"){ 
         $pk_num = true; 
      }else{ 
         $pk_num = false; 
      }// end if 
    } // endfor 
  }// end foreach 


  //get the initial variabales 
  echo "\n\n<p><h3>setting initial variables</h3><br>\n"; 

    for ($x=0; $x < $cnt; $x++){ 
      echo "\$$fields[$x]                = \"$defaults[$x]\";<br>\n"; 
    } 

  echo "\n\n<p><h3>setting post/get values</h3><br>\n"; 
  for ($x=0; $x < $cnt; $x++){ 
    if ($slashes=="yes"){ 
      echo "\$$fields[$x]                 = addslashes(@\$".$type."['$fields[$x]']);<br>\n"; 
    }else{ 
      echo "\$$fields[$x]                 = @\$".$type."['$fields[$x]'];<br>\n"; 
    }// end if 
  } // end for 


  //get the insert statement 
  echo "\n\n<br><p><h3>setting insert statement</h3><br>\n"; 

  $insert_query = " insert into $table_name ("; 

  for ($x=0; $x < $cnt; $x++){ 

    $insert_query .= "$fields[$x], "; 

  }// end for 
  $insert_query .= ") values ("; 

  for ($x=0; $x < $cnt; $x++){ 

    if (in_array(substr($types[$x],0,3), $numeric_field_types_array)){ 
       $insert_query .= "\$$fields[$x], "; 
    }else{ 
       $insert_query .= "'\$$fields[$x]', "; 
    }// end if 

  }// end for 
  $insert_query = substr($insert_query, 0, strlen($insert_query)-2) . ");"; 
  echo $insert_query; 


  //get the update statement 
  echo "\n\n<br><p><h3>setting update</h3><br>\n"; 

  $update_query = "update $table_name set "; 

  for ($x=0; $x < $cnt; $x++){ 

      if (in_array(substr($types[$x],0,3), $numeric_field_types_array)){ 
       $update_query .= "$fields[$x]=\$$fields[$x], "; 
    }else{ 
       $update_query .= "$fields[$x]='\$$fields[$x]', "; 
    }// end if 

  }// end for 

  $update_query = substr($update_query, 0, strlen($update_query)-2); 

  //rows id'd by pprimary key 
  if ($pk_num == true){ 
    $update_query .= " where $fields[$pk_id] = \$$fields[$pk_id]"; 
  }else{ 
    $update_query .= " where $fields[$pk_id] = '\$$fields[$pk_id]'"; 
  }//end if 

  echo $update_query; 

  //get the primary key for the table 
  echo "\n\n<br><p><h3>setting global variables</h3><br>\n"; 

  for ($x=0; $x < $cnt; $x++){ 

    $my_global .= "\$$fields[$x], "; 

  } // end for 
  $my_global =  substr($my_global,0,strlen($my_global) - 2); 
  echo "$my_global;"; 

  //get the editable values from the db 
  echo "\n\n<br><p><h3>getting edit variables</h3><br>\n "; 

  for ($x=0; $x < $cnt; $x++){ 

    if ($slashes=="yes"){ 
       echo "\$$fields[$x] \t\t= stripslashes(\$row['$$fields[$x]']);<br>\n "; 
    }else{ 
       echo "\$$fields[$x] \t\t= \$row['$$fields[$x]'];<br>\n "; 
    }// end if 

  }// end for 

}// end function 
?>