MySQL Table Creating Script!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Sequalit
Forum Commoner
Posts: 75
Joined: Wed Oct 12, 2005 9:57 pm
Location: Texas

MySQL Table Creating Script!

Post by Sequalit »

Ever get tired of having to create seperate tables for every website you do!? It can get really boring and a chore to do sometimes...

Well I designed a script that will do it for you!!!!!!

This is currently version one, all you can do is create a table... just the basics...
I will be coding more and more features into the script as time passes...
including a secure login system, modifying columns, inserting and removing data from tables... and lots of other stuff.
I also plan on reading documentation on PHP Security and adding security as I move along, also make it more of a portal system.

As of right now the script consists of four pages.
Database information + table information
table specifics
verification
Creation.

Any suggestions and or revisions/changes would be gladly accepted and appreciated!!!!!!!!

I will continue make the changes to the script and update this post when i make a major change!....

MySQL Table Creation V.1.00.0

Code: Select all

<?php
/*
This script creates MySQL Tables
Copyright (C) 2005 Thadeus Burgess

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

Contact: Email me at admin@n00bl33t.com
*/
?>
index.php (database info + table info)

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>MySQL - Index</title>
</head>

<body>
	<form method="post" action="values.php" autocomplete="off">
		<input name="dbu" type="text" value="MySQL Username"><br />
		<input name="dbp" type="text" value="MySQL Password"><br />
		<input name="db" type="text" value="MySQL Database"><br />
		<input name="tn" type="text" value="MySQL Table Name"><br />
		<input name="tc" type="text" value="MySQL Columns"><br />
		<input name="au" type="password" value="auth?"><br />
		<input name="su" type="submit" value="Submit">
	</form>
</body>
</html>
values.php (table column info)

Code: Select all

<?php
	$p="q1w2e3r4t5y6b7o8a9r0d";
	if(isset($_POST['su'])){
		$sdbu = cleanTaint($_POST['dbu'], "/^[A-Z a-z 0-9]+$/");
		$sdbp = cleanTaint($_POST['dbp'], "/^[A-Z a-z 0-9]+$/");
		$sdb = cleanTaint($_POST['db'], "/^[A-Z a-z 0-9]+$/");
		$stn = cleanTaint($_POST['tn'], "/^[A-Z a-z]+$/");
		$stc = cleanTaint($_POST['tc'], "/^[0-9]+$/");
		$sau = cleanTaint($_POST['au'], "/^[A-Z a-z 0-9]+$/");
		if($sau != $p){
			die("Incorrect Authentication");
		}
	}
function cleanTaint($str, $match){
	$str = strip_tags($str);
	$str = str_replace("_"," ",$str);
	if(!preg_match($match, $str)){
		die("Incorrect Format $str");
	}
	$str = str_replace(" ","_",$str);
	return $str;
}
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>MySQL - Values</title>
</head>

<body>
<?php
	echo "<form method='post' action='verify.php' autocomplete='off'>
		<input name='hdbu' type='hidden' value='$sdbu'>
		<input name='hdbp' type='hidden' value='$sdbp'>
		<input name='hdb' type='hidden' value='$sdb'>
		<input name='htn' type='hidden' value='$stn'>
		<input name='htc' type='hidden' value='$stc'>
		<input name='hau' type='hidden' value='$sau'>
		";
	echo "Username: ".$sdbu."<br />";
	echo "Password: ".$sdbp."<br />";
	echo "Database: ".$sdb."<br />";
	echo "Table Name: ".$stn."<br />";
	echo "Table Columns: ".$stc."<br />";
	echo "Auth: ".$sau;
	echo "<table border='1'>";
	echo "<tr><th>Num</th><th>Column Name</th><th>Column Type</th><th>Null?(yes/no)</th><th>autoincremented?(yes/no)</th></tr>";
	for($i=0;$i<$stc;$i++){
		echo "
		<tr>
			<th>
				$i
			</th>
			<th>
				<input name='hname$i' type='text' value='Column $i Name'>
			</th>
			<th>
				<input name='htype$i' type='text' value='Column $i Type'>
			</th>
			<th>
				<input name='hnull$i' type='text' value='Column $i Null'>
			</th>
			<th>
				<input name='hauto$i' type='text' value='Column $i Auto'>
			</th>
			
		</tr>
		";
	}
	echo "</table>";
	echo "<input name='hprimary' type='text' value='Column Primary'><br />";
	echo "<input name='hsu' type='submit' value='Accept'>";
	echo "</form>";
?>
</body>
</html>
verify.php (verification)

Code: Select all

<?php
	$p="q1w2e3r4t5y6b7o8a9r0d";
	if(isset($_POST['hsu'])){
		$sdbu = cleanTaint($_POST['hdbu'], "/^[A-Z a-z 0-9]+$/");
		$sdbp = cleanTaint($_POST['hdbp'], "/^[A-Z a-z 0-9]+$/");
		$sdb = cleanTaint($_POST['hdb'], "/^[A-Z a-z 0-9]+$/");
		$stn = cleanTaint($_POST['htn'], "/^[A-Z a-z]+$/");
		$stc = cleanTaint($_POST['htc'], "/^[0-9]+$/");
		$sau = cleanTaint($_POST['hau'], "/^[A-Z a-z 0-9]+$/");
		if($sau != $p){
			die("Incorrect Authentication");
		}
		$primaryKey = cleanTaint($_POST['hprimary'], "/^[A-Z a-z]+$/");
		$array = array();
		for($i=0;$i<$stc;$i++){
			$array[$i]['name'] = cleanTaint($_POST['hname'.$i], "/^[A-Z a-z]+$/");
			$array[$i]['type'] = cleanTaint($_POST['htype'.$i], "/^[A-Z a-z 0-9 ( )]+$/");
			$array[$i]['null'] = cleanTaint($_POST['hnull'.$i], "/^[A-Z a-z]+$/");
			$array[$i]['auto'] = cleanTaint($_POST['hauto'.$i], "/^[A-Z a-z]+$/");
		}
	}
function cleanTaint($str, $match){
	$str = strip_tags($str);
	$str = str_replace("_"," ",$str);
	if(!preg_match($match, $str)){
		die("Incorrect Format $str");
	}
	$str = str_replace(" ","_",$str);
	return $str;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>MySQL - Verify</title>
</head>

<body>
<?php
	echo "<form method='post' action='create.php' autocomplete='off'>
		<input name='vdbu' type='hidden' value='$sdbu'>
		<input name='vdbp' type='hidden' value='$sdbp'>
		<input name='vdb' type='hidden' value='$sdb'>
		<input name='vtn' type='hidden' value='$stn'>
		<input name='vtc' type='hidden' value='$stc'>
		<input name='vau' type='hidden' value='$sau'>
		";
	echo "Username: ".$sdbu."<br />";
	echo "Password: ".$sdbp."<br />";
	echo "Database: ".$sdb."<br />";
	echo "Table Name: ".$stn."<br />";
	echo "Table Columns: ".$stc."<br />";
	echo "Auth: ".$sau;
	echo "<br />";
	echo "<table border='1'>";
	echo "<tr><th>Name?</th><th>Type?</th><th>Null?</th><th>Auto?</th></tr>";
	$i=0;
	foreach($array as $column){
		echo "
			<tr>
				<th>
					".$column['name']."<input name='vname$i' type='hidden' value='".$column['name']."'>
				</th>
				<th>
					".$column['type']."<input name='vtype$i' type='hidden' value='".$column['type']."'>
				</th>
				<th>
					".$column['null']."<input name='vnull$i' type='hidden' value='".$column['null']."'>
				</th>
				<th>
					".$column['auto']."<input name='vauto$i' type='hidden' value='".$column['auto']."'>
				</th>
			</tr>
			";
		$i++;
	}
	echo "</table>";
	echo "Primary Key: $primaryKey";
	echo "<input name='vprimary' type='hidden' value='$primaryKey'><br />";
	echo "<input name='vsu' type='submit' value='Create Table'>";
	echo "</form>";
?>
</body>
</html>
create.php (creates the table)

Code: Select all

<?php
	$p="q1w2e3r4t5y6b7o8a9r0d";
	if(isset($_POST['vsu'])){
		$sdbu = cleanTaint($_POST['vdbu'], "/^[A-Z a-z 0-9]+$/");
		$sdbp = cleanTaint($_POST['vdbp'], "/^[A-Z a-z 0-9]+$/");
		$sdb = cleanTaint($_POST['vdb'], "/^[A-Z a-z 0-9]+$/");
		$stn = cleanTaint($_POST['vtn'], "/^[A-Z a-z]+$/");
		$stc = cleanTaint($_POST['vtc'], "/^[0-9]+$/");
		$sau = cleanTaint($_POST['vau'], "/^[A-Z a-z 0-9]+$/");
		if($sau != $p){
			die("Incorrect Authentication");
		}
		$primaryKey = cleanTaint($_POST['vprimary'], "/^[A-Z a-z]+$/");
		$array = array();
		for($i=0;$i<$stc;$i++){
			$array[$i]['name'] = cleanTaint($_POST['vname'.$i], "/^[A-Z a-z]+$/");
			$array[$i]['type'] = cleanTaint($_POST['vtype'.$i], "/^[A-Z a-z 0-9 ( )]+$/");
			$array[$i]['null'] = cleanTaint($_POST['vnull'.$i], "/^[A-Z a-z]+$/");
			$array[$i]['auto'] = cleanTaint($_POST['vauto'.$i], "/^[A-Z a-z]+$/");
		}
	}
function cleanTaint($str, $match){
	$str = strip_tags($str);
	$str = str_replace("_"," ",$str);
	if(!preg_match($match, $str)){
		die("Incorrect Format $str");
	}
	$str = str_replace(" ","_",$str);
	return $str;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>MySQL - Create</title>
</head>

<body>
<?php
	mysql_connect(localhost,$sdbu,$sdbp);
	@mysql_select_db($sdb) or die("Unable to select Database: $sdb");
	$query="CREATE TABLE ".$stn."(";
	$i=0;
	foreach($array as $info){
		$query .= $info['name']." ".$info['type'];
		if($info['null'] == "yes"){
			$query .= " NOT NULL";
		}if($info['auto'] == "yes"){
			$query .= " auto_increment";
		}
		$query .= ", ";
	}
	$query .= "PRIMARY KEY(".$primaryKey."))";
	//$query = "drop table if exists $stn;";
	$result = mysql_query($query);
	if(!$result){
		$message  = 'Invalid query: ' . mysql_error() . "\n";
		$message .= 'Whole query: ' . $query;
		mysql_close();
		die($message);
	}
	mysql_close();
	echo "<center>
		Table successfully create in $sdb by $sdbu.<br />
		With tablename of $stn having $stc columns.<br />
		The Primary Key of $stn is $primaryKey.<br />
		The table looks like this:<br /><br />
		";
	echo "<table border='1'>
		<tr>";
	foreach($array as $column){
		echo "<th>".$column['name']." - ".$column['type']." - ".$column['null']." - ".$column['auto']."</th>";
	}
	echo "</tr><tr>";
	foreach($array as $thisvariableisnotneeded){
		echo "<th>&nbsp;&nbsp;</th>";
	}
	echo "</tr>
		</table>";
?>
</body>
</html>
Post Reply