PHP MySQL
Create & Test MySQL Database

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 at the Beginning.
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.

Copy & Save as: birthdays_add_fields.php

You should only run this script if you want to increase your knowledge by getting a little practice in updating the existing scripts.

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

MySQL Tutorial

To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com

PHP MySQLCreate & Test MySQL Database