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 ?>