PHP

Creating a Table

A database can consist of multiple tables. To create a table in the database, you'll use SQL.

CREATE TABLE the_tablename(column1, column2, etc.)

As an example, create a blog_entries TABLE.

Column Name	Column Type
blog_id	Positive, non-null, automatically incrementing integer
title	Text up to 100 characters in length
entry	Text of any length
date_entered	A timestamp including both the date and the time the row was added

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="EN" lang="EN">
<head>
<title>Creating a Table</title>
<meta Name="Author" Content="Hann So">
</head>
<body>
<p>
<?php

if (isset($_POST['submit'])) {
		process_form();
	}
	else {
		display_form();// display form for the first time
	}

function display_form() {
	echo <<<HTML
	<h2>Creating a Table</h2>
	<form action = "$_SERVER[SCRIPT_NAME]" method="post">
	Username:
	<input type="text" name="username" size="50" value="hann" />
	<br />
	Password:
	<input type="password" name="password" size="50" />
	<br />
	Database name:
	<input type="text" name="db" size="50" value="hann_db" />
	<br />
	Table name:
	<input type="text" name="table" size="50" value="blog_entries" />
	<br />
	<input type="submit" name="submit" value="Submit" />
	</form>
HTML;
}

function process_form() {

	DEFINE ('DB_HOST', "localhost");
	DEFINE ('DB_USER', "$_POST[username]");
	DEFINE ('DB_PASSWORD', "$_POST[password]");
	DEFINE ('DB_NAME', "$_POST[db]");
	DEFINE ('TABLE_NAME', "$_POST[table]");

	echo "<p>Opening the connection to the database server.</p>";
	if ($link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)) {
		echo "<p>The connection worked. The link is $link</p>";

		// select a database
		if (@mysql_select_db(DB_NAME)) {
			echo "<P>The database ", DB_NAME, " has been selected.</p>";
		}
		else {
			die ("<p>Could not select the database because: ". mysql_error(). "</p>");
		}

		// define the query
		$query = "CREATE TABLE " . TABLE_NAME . " (
		blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
		title VARCHAR(100) NOT NULL,
		entry TEXT NOT NULL,
		date_entered DATETIME NOT NULL
		)";
		// run the query
		if (@mysql_query($query)) {
			echo "<p>The table has been created.</p>";
		// close the connection
		mysql_close($link);
		}
		else {
			die ("<p>Could not create the table because: ". mysql_error(). "</p>");
		}
	}
	else {
		die ("<p>Could not connect to MySQL because: ". mysql_error(). "</p>");
	}
	echo "<p><a href=\"$_SERVER[SCRIPT_NAME]\">Try again?</a></p>\n";

}

?>
</p>
</body>
</html>

View the effect