php_mysql_create.asp

W3Schools tutorial..web designing

<h2>Create a Table</h2>
<p>The CREATE TABLE statement is used to create a database table in MySQL.</p>
<h3>Syntax</h3>
<table class="ex" cellspacing="0" width="100%" border="1" id="table7">
	<tr>
		<td>
		<pre>CREATE TABLE table_name<i>
</i>(
column_name1 data_type,<i>
</i>column_name2 data_type,<i>
</i>column_name3 data_type,<i>
.......</i>
)</pre>
		</td>
	</tr>
</table>
<p>We must add the CREATE TABLE statement to the mysql_query() function to 
execute the command.</p>
<h3>Example</h3>
<p>The following example shows how you can create a table named &quot;person&quot;, with 
three columns. The column names will be &quot;FirstName&quot;, &quot;LastName&quot; and 
&quot;Age&quot;:</p>
<table class="ex" cellspacing="0" border="1" width="100%">
	<tr>
		<td>
    <pre>&lt;?php
$con = mysql_connect(&quot;localhost&quot;,&quot;peter&quot;,&quot;abc123&quot;);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }</pre>
	<pre>// Create database
if (mysql_query(&quot;CREATE DATABASE my_db&quot;,$con))
  {
  echo &quot;Database created&quot;;
  }
else
  {
  echo &quot;Error creating database: &quot; . mysql_error();
  }</pre>
		<pre>// Create table in my_db database
mysql_select_db(&quot;my_db&quot;, $con);
$sql = &quot;CREATE TABLE person&nbsp;
(
FirstName varchar(15),
LastName varchar(15),
Age int
)&quot;;
mysql_query($sql,$con);</pre>
		<pre>mysql_close($con);
?&gt;</pre>
		</td>
	</tr>
</table>
<p><b>Important:</b> A database must be selected before a table can be created. 
The database is selected with the mysql_select_db() function.</p>
<p><b>Note:</b> When you create a database field of type varchar, you must 
specify the maximum length 
of the field, e.g. varchar(15).</p>
<hr />

<h2>MySQL Data Types</h2>
<p>Below are the different MySQL data types that can be used:</p>
<table class="ex" cellspacing="0" border="1" width="100%" id="table15">
  <tr>
    <th width="40%" align="left" valign="top">Numeric Data Types</th>
    <th width="60%" align="left" valign="top">Description</th>
  </tr>
  <tr>
    <td valign="top">int(size)<br />
		smallint(size)<br />
		tinyint(size)<br />
		mediumint(size)<br />
		bigint(size)</td>
    <td valign="top">Hold integers only. The maximum number of digits can be 
	specified in the size parameter</td>
  </tr>
  <tr>
    <td valign="top">decimal(size,d)<br />
		double(size,d)<br />
		float(size,d)</td>
    <td valign="top">Hold numbers with fractions. The maximum number of digits 
	can be specified in the size parameter. The maximum number of digits to the right of the 
	decimal is specified in the d parameter</td>
  </tr>
  </table>
<br />
<table class="ex" cellspacing="0" border="1" width="100%" id="table16">
  <tr>
    <th width="40%" align="left" valign="top">Textual Data Types</th>
    <th width="60%" align="left" valign="top">Description</th>
  </tr>
  <tr>
    <td valign="top">char(size)</td>
    <td valign="top">Holds a fixed length string (can contain letters, numbers, 
	and special characters). The fixed size is specified in parenthesis</td>
  </tr>
  <tr>
    <td valign="top">varchar(size)</td>
    <td valign="top">Holds a variable length string (can contain letters, 
	numbers, and special characters). The maximum size is specified in 
	parenthesis</td>
  </tr>
  <tr>
    <td valign="top">tinytext</td>
    <td valign="top">Holds a variable string with a maximum length of 255 
	characters</td>
  </tr>
  <tr>
    <td valign="top">text<br />
		blob</td>
    <td valign="top">Holds a variable string with a maximum length of 65535 
	characters</td>
  </tr>
  <tr>
    <td valign="top">mediumtext<br />
		mediumblob</td>
    <td valign="top">Holds a variable string with a maximum length of 16777215 
	characters</td>
  </tr>
  <tr>
    <td valign="top">longtext<br />
		longblob</td>
    <td valign="top">Holds a variable string with a maximum length of 4294967295 
	characters</td>
  </tr>
  </table>
<br />
<table class="ex" cellspacing="0" border="1" width="100%" id="table17">
  <tr>
    <th width="40%" align="left" valign="top">Date Data Types</th>
    <th width="60%" align="left" valign="top">Description</th>
  </tr>
  <tr>
    <td valign="top">date(yyyy-mm-dd)<br />
		datetime(yyyy-mm-dd hh:mm:ss)<br />
		timestamp(yyyymmddhhmmss)<br />
		time(hh:mm:ss)</td>
    <td valign="top">Holds date and/or time</td>
  </tr>
  </table>
<br />
<table class="ex" cellspacing="0" border="1" width="100%" id="table19">
  <tr>
    <th width="40%" align="left" valign="top">Misc. Data Types</th>
    <th width="60%" align="left" valign="top">Description</th>
  </tr>
  <tr>
    <td valign="top">enum(value1,value2,ect)</td>
    <td valign="top">ENUM is short for ENUMERATED list. Can store one of up to 
	65535 values listed within the ( ) brackets. If a value is inserted that is 
	not in the list, a blank value will be inserted</td>
  </tr>
  <tr>
    <td valign="top">set</td>
    <td valign="top">SET is similar to ENUM. However, SET can have up to 64 list 
	items and can store more than one choice</td>
  </tr>
  </table>
<br />
<hr />

<h2>Primary Keys and Auto Increment Fields</h2>
<p>Each table should have a  
primary key field.</p>
<p>A primary key is used to uniquely identify the rows in a table. Each primary 
key value must be unique within the table. Furthermore, the primary key field 
cannot be null because the database engine requires a value to locate the 
record.</p>
<p>The primary key field is always indexed. There is no exception to this rule! 
You must index the primary key field so the database engine can quickly locate 
rows based on the key's value.</p>
<p>The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the 
AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field 
by 1 each time a new 
record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL 
setting to the field.</p>
<h3>Example</h3>
<table class="ex" cellspacing="0" border="1" width="100%">
	<tr>
		<td>
		<pre>$sql = &quot;CREATE TABLE person&nbsp;
(
personID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)&quot;;</pre>
		<pre>mysql_query($sql,$con);</pre>
		</td>
	</tr>
</table>
<br />
<hr />

<a href="php_mysql_connect.asp"><img alt="previous" border="0" src="../images/btn_previous.gif" width="100" height="20" /></a>
<a href="php_mysql_insert.asp"><img alt="next" border="0" src="../images/btn_next.gif" width="100" height="20" /></a>

<br />
<hr />

<!-- **** SPOTLIGHTS 1 **** -->

<iframe src="../banners/aspallframe.asp" height="110" width="485"
marginwidth="0" marginheight="0" frameborder="0" scrolling="no">
Your browser does not support inline frames or is currently configured not to display inline frames.
</iframe>
<hr />
<!-- **** SPOTLIGHTS 2 **** -->

<h2><a target="_blank" href="../../www.altova.com/ref/@s=w3s_spotlight&q=xmlspy">
Altova