PHP MySQL
Interactive Website Design

Altering Tables

Existing tables can be altered using the mysql_query() function.

You can add or modify fields or columns in an existing table. You can delete fields. You can even delete an entire table and start over.

Note: If you have accessed this web page via a search engine, you should go back and start on our home page. This tutorial is designed to be viewed and executed in sequence. Learn to build your database right on your PC and Export it to your website.

Add a Column or Field to a Table

To add a column to an existing table the syntax would be:
mysql_query("ALTER TABLE birthdays ADD street CHAR(30)");

You can also specify where you want to add the field.
mysql_query("ALTER TABLE birthdays ADD street CHAR(30) AFTER birthday");

The simple bit of code shown above would add a new column to the birthdays table named street after birthday. Type and size are also set in the statement.

You can also add multiple fields:

mysql_query("ALTER TABLE birthdays
ADD street CHAR(30) AFTER birthday,
Add city CHAR(30) AFTER street,
ADD state CHAR(4) AFTER city,
ADD zipcode CHAR(20) AFTER state,
ADD phone CHAR(20) AFTER zipcode");

Modify a Column or Field

Column definitions can be modified using the ALTER method. The following code would change the existing birthday column from 7 to 15 characters.

mysql_query("ALTER TABLE birthdays CHANGE birthday birthday VARCHAR(15)");

In the example the column to alter is first named and then the new definition is supplied which includes the column name

Remove a Column or Field

Columns can be removed from an existing table. The next example of code would remove the lastname column.
mysql_query("ALTER TABLE birthdays DROP lastname");

Remove a Table

Be careful with this code. It will remove an entire table and all of its contents from your database.
mysql_query("DROP TABLE table_name");

This script is included in the download zip file. If you run it, you will need to add the new fields to all of the existing scripts.

birthdays_add_fields.php

<HTML><BODY>

<?php
/* Use this script to add new fields or columns to your database.
    *To change script change names to the fields you want to add
    *You can also change VARCHAR to the type you need and add the size.
    *DO NOT Preview this script unless you want to add these fields
    *or make changes and then run it
    *If you use AFTER specify where you want the field to fall
    */
$db="newdb";
$link = mysql_connect('localhost', 'root', '');
if (! $link)
die(mysql_error());
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());

$result=mysql_query("ALTER TABLE birthdays
ADD street VARCHAR(30) AFTER birthday,
Add city VARCHAR(30) AFTER street,
ADD state VARCHAR(4) AFTER city,
ADD zipcode VARCHAR(20) AFTER state,
ADD phone VARCHAR(20) AFTER zipcode") or die("Alter Error: ".mysql_error());
mysql_close($link);
print "Field added";
?>
<form method="POST" action="birthdays_insert_form.php">
<input type="submit" value="Insert Another Record">
</form>
<br>

<form method="POST" action="birthdays_dbase_interface.php">
<input type="submit" value="Dbase Interface">
</form>


</BODY>
</HTML>

Note: All ofhe operations covered above can be performed from the phpMyAdmin panel.

Building Your Database on Your PC

In order to build your database on your PC and later Export it to your website requires setting up a localhost server on your PC and finding an HTML editor that will sync up with the server.

If you use Windows, the process of setting up and running a localhost so that you can test and edit scripts and build databases is very easy.

I recommend the IndigoAMPP web server and the HTMLPad 2010 HTML editor.

I recently installed IndigoAMPP for Windows on my Vista system and it ran on the first try after installation. Setting up HTMLPad 2010 to work with it is also an easy process.

I created a little tutorial that shows the whole process including how to start the server after you install it. Go to Tutorial
Note: The server is FREE and you can try the HTML editor for 30 sessions or 30 days free. If you can't learn to build your database in that amount of time, don't buy the editor. NO RISK!!