Creating XML from MySQL as easy as PI

ANALYSIS

Unfortunately, importing XML into MySQL is more like chocolate cheesecake, but there are solutions available. Just follow this guide and you'll be on your way to integrating Web services with your database in no time. Starting from scratch
With the growing popularity of XML, developers have found an easy method to present data sets in a standardised way. What else does that sound like? A database! It's only natural that it should be simple to convert your information without a lot of fuss -- and you can. Some proprietary database manufacturers, such as Microsoft and IBM, have taken steps to integrate XML into their systems. This comes as no surprise since these two companies are both heavily involved in the XML standardisation project. Not wanting to be left behind, the creators of MySQL database incorporated a means for generating an XML data file. It's supported in version 3.23.48 and up. You can use the command line or facilitate the process with the programming language of your choice. To get started, you can download MySQL database  for free from MySQL.com. The current release is sufficient to support this feature, and you don't need to compile it with any special parameters. Fruit filling
Once you're installed, created, and populated your database, execute the following command to generate an XML file:
mysqldump --xml databasename [tables]
If you'd like to save this to a file, simply use the standard *NIX method of outputting to a file:
mysqldump --xml databasename > filename.xml
This produces a well formed XML document. Because XML is datacentric, if you dump your entire database and it contains no information, your file will result in a series of empty tags based on the table names. Your output should look something like this:

Now you're free to use this data file with any application you desire. This method is useful in a number of ways. Not only will it create a standardised representation of your data, but it can also take a snapshot of your database (or portion of your database) for display. Rather than making repeated calls to the database server, just generate an XML document when your database changes and reference that from Web pages or whatever you're using. This can localize calls for data, reduce the overhead of frequent calls to a database, and easily present a subset of your information for improved performance, security, or localization. Ice cream on the side
It's really easy to get XML from MySQL, but how about the other direction? That's a little trickier. MySQL itself doesn't support this function, and with good reason. The database currently has no way to validate the XML file. This could result in a number of scenarios, ranging from a partial load to ignoring malformed tags and statements to simply forcing the entire load to fail. MySQL supports only cascading back-outs in current development versions. While it's not pretty from a native standpoint, you do have some options. One solution is Perl's DBIx::XML_RDB module. You can use this method to both import and export XML, though understandably the import is heavily dependent upon a correctly structured XML file. To get the data, the module essentially runs a query and formats the results in an XML file. Conversely, you can use the module to read an XML file, create a SQL query, and execute it. There is a simpler option as well. The DBIx::XML_RDB module ships with two utility scripts to facilitate the process: xml2sql.pl and sql2xml.pl. I found agreat tutorial  on using this Perl module at O'Reilly's XML.com Web site. It will walk you through the process. Another, more generalized effort comes from Ron Bourret's XML-DBMS project . This is an ongoing effort to support XML imports and exports with relational databases using Perl and Java. There's also some very interesting work that supports mapping one database to another using XML as facilitating middleware. This is a community-oriented open source project being managed on SourceForge . Other languages, particularly Web scripting languages, haven't ignored the need to import XML into SQL databases either. There are similar efforts for Python, such as the xml2sql and dtd2sql modules, outlined in detail in this article from IBM , and a couple of projects in the works for PHP, such as the "XML MySQL class" project. Scrumptious
With these utilities, importing and exporting XML into and from MySQL is easy! Since MySQL is popular and free, it's been the test bed for integrating many scripting languages in XML, and as a result there are a number of tutorials and scripts specific for this database. With the power of a relational database and the ability to easily create XML files, MySQL can be an integral part of your Web services solution.

Learning PHP

PHP is the world's most popular web development language. Started by Danish-Greenlandic programmer Rasmus Lerdorf in 1995 it is now installed on more than 20 million websites and 1 million web servers and counting. 


It is estimated that for every 100 PHP developers, there are 42 Perl developers, 12 Python developers and 4 Ruby developers - PHPs popularity is the central reason why you should consider learning it above all others. 


PHP is the basis of Content Management Systems such as Drupal, Joomla and WordPress so gaining a knowledge of PHP would help you in using these scripts. 


Presumably your are already proficient with CSS and HTML and want to take your web creativity to another level. If you aren't, then stop right here. It's unthinkable to tackle PHP without a firm grounding in HTML and a good knowledge of CSS would be extremely useful. 


You don't have to have a complete knowledge of HTML in order to learn PHP but you certainly need to know the basics - the rest you will pick up in tandem with PHP. For instance, if you use Content Management Systems all the time you'll unlikely to be that familiar with coding forms, but HTML forms are an essential part of PHP and you'll need to be able to create them quickly and without fuss. 


Learning PHP is as hard as you can imagine it to be. You need time and lots of patience and preferably a reality you need to escape from for an inordinate amount of time. It's a good idea to pace yourself and set a two year framework in order to become familiar with the core of the language.


An interesting article here.

Evolving Algorithms

Once upon a time, search engines may have used keywords and a few basic bits of code in order to calculate their rankings. This evolved to include other key elements, such as the number of links other people made to your page, and then evolved still further to blacklist certain links in order to avoid specious link farms or exchanges.

Now, search engine algorithms are beginning to take both user behavior and dynamic aspects of the site into account. This was largely predicted by the SEO community as a natural progression of the discipline, and its day seems to have arrived.

First and foremost, bounce rate should be kept under fifty percent. If a user returns directly from your site main page to the search results, chances are they are not finding what they are looking for. This degrades the search engine's performance for its users, so it will respond by reducing your site's rankings for those search keys. Degrading to a 70 percent or 80 percent bounce back rate will likely decrease your rankings, whereas getting up to a 20 or 30 percent bounce rate can help ensure that you consistently make top rankings.

Site performance is another key element in the new paradigm of search engine algorithms. High performance rates, very low down time, speedy searches, working links, and anything else you can do to improve site speed and reliability will prove that your site can handle the traffic the search engine drives to you.

Linking   is an old standard way to increase rankings, but diversity and quality are becoming increasingly critical. It is far better to have a handful of high quality links from respected sources than to have mountains of suspicious or useless links. Making an effort to promote your site only with well-respected sites and web masters, and avoiding link exchanges like the plague, should give results that are well worth the effort.

An excellent way to promote good quality linking and interactivity with the rest of the web is to utilize the most popular social networking and bookmaking sites. Twitter, and especially its blog-broadcasting counterpart Loud Twitter, can be a great way to get exponential linking. Social linking and news sites such as Digg and Stumble Upon can also help you capitalize on Web 2.0 viral growth rates. Sites like Facebook, Myspace, Livejournal, and other massive blog and social networking sites are becoming crawlable; you want to already have a significant foothold in those arenas when other web masters are scrambling to catch up with the newest search algorithm tweaks. And do not overlook the power of RSS. The number of subscribers to your RSS feed is already becoming a key measure of your site relevance and thus your site ranking.

Search engines exist not to count links, but to give users what they want, and content is still what users are looking for. You do still need to do the “real work” of web sites: getting respected authorities to review your products, providing excellent multimedia offerings, and writing articles and posts.

XML & PHP

The SimpleXML extension provides a very simple and easily usable toolset to convert XML to an object that can be processed with normal property selectors and array iterators.

<?php

$xmlstr = <<<XML

<?xml version='1.0' standalone='yes'?>

<movies>

 <movie>

  <title>PHP: Behind the Parser</title>

  <characters>

   <character>

    <name>Ms. Coder</name>

    <actor>Onlivia Actora</actor>

   </character>

   <character>

    <name>Mr. Coder</name>

    <actor>El ActÓr</actor>

   </character>

  </characters>

  <plot>

   So, this language. It's like, a programming language. Or is it a

   scripting language? All is revealed in this thrilling horror spoof

   of a documentary.

  </plot>

  <great-lines>

   <line>PHP solves all my web problems</line>

  </great-lines>

  <rating type="thumbs">7</rating>

  <rating type="stars">5</rating>

 </movie>

</movies>

XML;

?>




You can find more, here: http://www.php.net/manual/en/simplexml.examples-basic.php




Learn How to Install Joomla on Your Website in Less Than 5 Minutes

You can learn how to install Joomla on your website in a number of ways. However, the simplest and by far, the quickest is through Fantastico. This usually takes about 1-2 minutes once you know how. In this article, we are going to cover just that.

So, here is the step by step process for installing Joomla on your own website.

1. Log in to your hosting account's Control Panel. This should usually be http://www.mydomainname.com/controlpanel, where 'mydomainnane' is replaced your own website that is hosted on that hosting account.

2. Look for the smiley icon that says "Fantastico" and click on it. This is where you will be installing the Joomla package from. Note that Sometimes, it may say "Fantastico De Luxe." Don't worry it is the same thing. Much like saying "my room" and "my bedroom" ;)

3. Look for "Joomla" under the "Content Management" category of scripts in Fantastico and click on it. Also, note that the title 'Joomla' may sometimes have some numerical suffixes showing which version it is. For example, the title may read "Joomla 1.5" for version 1.5 of the CMS. Just click on the most recent version and go to the next step.

4. Look for the hyperlinked blue "New Installation" link and make sure you have at least as much as the minimum disk space required for you to install Joomla on your website. This is usually about 25 MB of disk space.

This is not usually
a problem, with most people's disk space running into the tens of gigabytes. But it is still good to make sure you have enough to hold a new Joomla installation.

5. Choose where on your website you want to install Joomla. Will it be on your main domain, an existing sub-domain or a new not-yet-existing sub-domain or directory? Indicate it in this part of the installation process.

6. Fill out other required fields that are required to smoothly install Joomla on the site. This includes creating an Admin username and password. Don't choose 'Admin' and 'password' as your username and password. Many people do this and have been hacked. Also, fill out the site name or main keyword, email address and other details here.

Then click the "Install Joomla" button.

7. On the next page, make sure that all the needed information is correct. Also, note down your Admin username, password and backend page (the one that ends with /administrator).

Finally, hit that "Finish Installation" of a button.

Voila! You have just learned how to install Joomla on your website. This should usually take only 1 or 2 minutes at most. After all, all you are doing is just hitting buttons, except for when choosing a username, etc.

If it still sounds a little like "nerd talk" (with all due respect to all the nerds out there ;) ), you can watch the video version of this article where you see the Joomla installation done live on the screen and see if you can learn how to install Joomla after that.

PHP & FTP

You can put files on the server through ftp.

Before we begin with the tutorial, you may want to download my PHP FTP class. This class is a simple wrapper around the native PHP FTP functions.

Most of the PHP applications store and read their settings from a configuration file. Normally an installer script is used to write the settings to the config file and then an admin section is provided through which the administrator can manage these settings. Normal PHP file functions like fopen and fputs can be used to create and write to the configuration file.

But there is a major drawback with this approach. If you want to create a file/directory through a PHP script in another directory, say, includes, then the directory includes need to be writable by the PHP script. Most of the times this means that you will need to chmod the directory to 777. This is a big security hole.

Another problem is that, more often than not, the owner/group of the newly created file is set to that of the PHP process. PHP process normally run as “nobody” or “www”. This means that the newly created file will be owned by “nobody” or “www” and you might not be able to delete the file through FTP. You will have to delete the file through a PHP script.

A simple workaround to the above problems would be to use the chmod and chown functions. Unfortunately both these functions don’t work on most shared hosting plans.

//Make the file writable by all
chmod('somefile',0777);

//Open the file and write to it
$fp = fopen('somefile', 'w');
fputs($fp,$data); fclose($fp);

//Make the file writable only by the owner
chmod('somefile',0644);

Therefore we will use PHP’s FTP functions to create files, directories and to change the mode of files. The functions that are of importance to use here are: ftp_mkdir, ftp_put and ftp_site.

Connecting to FTP server

Use ftp_connect function to connect to FTP server and use ftp_login function to login to FTP server.

//Connect to the FTP server
$ftpstream = @ftp_connect('localhost');

//Login to the FTP server
$login = @ftp_login($ftpstream, 'user', 'secret');
if($login) {
 //We are now connected to FTP server.
}

//Close FTP connection
ftp_close($ftpstream);

You can read whole tutorial here.

CSS vs. tables in HTML

An Interesting article about css with css vs. table:
http://esitesecrets.com/articles/1667/1/CSS-Layouts-Vs-Table-Layouts-Which-One-is-Better/Page1.html

In last 2 years I have used HTML with css table-less because more people which asking me for HTML slicing to NOT use tables. And I do  that....

Mysql - Modify an existing MySQL column

The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database — always make a backup first.

After a week of using the contacts table created inCreate a basic MySQL table, we may find that 40 characters for the column name doesn’t cut it. To increase the size of the name column to 80 characters:

ALTER TABLE contacts CHANGE name name VARCHAR(80);

The first part of this statement (ALTER TABLE contacts CHANGE name) identifies that we want to change the column name in the table contacts. The second part of this statement (name VARCHAR(80)) redefines the column name. We could further define this column as NOT NULL, for example, with

ALTER TABLE contacts CHANGE name name VARCHAR(80) NOT NULL;

PHP - IF statement

Introduction

Over the past two parts I have shown you the basics of text in PHP and how to store it as variables. In this part of the tutorial I will show you how to use IF statements to make decisions in your scripts.

The Basics Of IF

If statements are used to compare two values and carry out different actions based on the results of the test. If statements take the form IF, THEN, ELSE. Basically the IF part checks for a condition. If it is true, the then statement is executed. If not, the else statement is executed.

IF Strucure

The structure of an IF statement is as follows:

IF (something == something else)
{
THEN Statement
} else {
ELSE Statement
}
Variables

The most common use of an IF statement is to compare a variable to another piece of text, a number, or another variable. For example:

if ($username == "webmaster")
which would compare the contents of the variable to the text string. The THEN section of code will only be executed if the variable is exactly the same as the contents of the quotation marks so if the variable contained 'Webmaster' or 'WEBMASTER' it will be false.

Constructing The THEN Statment

To add to your script, you can now add a THEN statement:

if ($username == "webmaster") {
echo "Please enter your password below";
}
This will only display this text if the username is webmaster. If not, nothing will be displayed. You can actually leave an IF statement like this, as there is no actual requirement to have an ELSE part. This is especially useful if you are using multiple IF statements.

Constructing The ELSE Statement

Adding The ELSE statement is as easy as the THEN statement. Just add some extra code:

if ($username == "webmaster") {
echo "Please enter your password below";
} else {
echo "We are sorry but you are not a recognised user";
}
Of course, you are not limited to just one line of code. You can add any PHP commands in between the curly brackets. You can even include other IF statments (nested statements).

Other Comparisons

There are other ways you can use your IF statement to compare values. Firstly, you can compare two different variables to see if their values match e.g.

if ($enteredpass == $password)
You can also use the standard comparision symbols to check to see if one variable is greater than or less than another:

if ($age < "13")
Or :

if ($date > $finished)
You can also check for multiple tests in one IF statement. For instance, if you have a form and you want to check if any of the fields were left blank you could use:

if ($name == "" || $email == "" || $password == "") {
echo "Please fill in all the fields";
}
Part 4

In part four I will show you some other ways of using your PHP script to do other types of checks and loops.

PHP - POST & GET

Recall from the PHP Forms Lesson where we used an HTML form and sent it to a PHP web page for processing. In that lesson we opted to use the the post method for submitting, but we could have also chosen the get method. This lesson will review both transferring methods.

POST - Review

In our PHP Forms Lesson we used the post method. This is what the pertinent line of HTML code looked like:

HTML Code Excerpt:

This HTML code specifies that the form data will be submitted to the "process.php" web page using the POST method. The way that PHP does this is to store all the "posted" values into an associative array called "$_POST". Be sure to take notice the names of the form data names, as they represent the keys in the "$_POST" associative array.
Now that you know about associative arrays, the PHP code from "process.php" should make a litte more sense.

PHP Code Excerpt:

$quantity = $_POST['quantity'];
$item = $_POST['item'];

The form names are used as the keys in the associative array, so be sure that you never have two input items in your HTML form that have the same name. If you do, then you might see some problems arise.

PHP - GET

As we mentioned before, the alternative to the post method is get. If we were to change our HTML form to the get method, it would look like this:

HTML Code Excerpt:

The get method is different in that it passes the variables along to the "process.php" web page by appending them onto the end of the URL. The URL, after clicking submit, would have this added on to the end of it:
"?item=##&quantity=##"
The question mark "?" tells the browser that the following items are variables. Now that we changed the method of sending information on "order.html", we must change the "process.php" code to use the "$_GET" associative array.

PHP Code Excerpt:

$quantity = $_GET['quantity'];
$item = $_GET['item'];

After changing the array name the script will function properly. Using the get method displays the variable information to your visitor, so be sure you are not sending password information or other sensitive items with the get method. You would not want your visitors seeing something they are not supposed to!

Security Precautions

Whenever you are taking user input and using you need to be sure that the input is safe. If you are going to insert the data into a MySQL database, then you should be sure you have thought about preventing MySQL Injection. If you are going to make a user's input available to the public, then you should think about PHP htmlentities.

PHP functions - eregi

eregi() -   became deprecated starting PHP 5.3.0

hmmm... I've used this function many times.

Time and Date - PHP

PHP has the ability to dynamically generate the time and date. Using a simple line of code we are able to include this on our site, however it is important to know how the formatting works.



You can use the date function in conjunction with the time function to display this in the format of date ( format , time ) In our case we want the start time to be now, so we will call the time first. We will demonstrate many different types of formatting


 $b = time (); 
 print date("m/d/y",$b) . "
"; 
 print date("D, F jS",$b) . "
"; 
 print date("l, F jS Y",$b) . "
"; 
 print date("g:i A",$b) . "
"; 
 print date("r",$b) . "
"; 
 print date("g:i:s A D, F jS Y",$b) . "
"; 
 ?> 


There is different formats that can be used in the date feature. Below is a summary of the variable used in date, and what each does. They ARE CaSe sEnsItIVe:


DAYS 
d - day of the month 2 digits (01-31) 
j - day of the month (1-31) 
D - 3 letter day (Mon - Sun) 
l - full name of day (Monday - Sunday) 
N - 1=Monday, 2=Tuesday, etc (1-7) 
S - suffix for date (st, nd, rd) 
w - 0=Sunday, 1=Monday (0-6) 
z - day of the year (1=365)


WEEK 
W - week of the year (1-52)


MONTH 
F - Full name of month (January - December)
m - 2 digit month number (01-12) 
n - month number (1-12) 
M - 3 letter month (Jan - Dec) 
t - Days in the month (28-31)


YEAR
L - leap year (0 no, 1 yes)
o - ISO-8601 year number (Ex. 1979, 2006)
Y - four digit year (Ex. 1979, 2006)
y - two digit year (Ex. 79, 06)


TIME
a - am or pm
A - AM or PM
B - Swatch Internet time (000 - 999)
g - 12 hour (1-12)
G - 24 hour c (0-23)
h - 2 digit 12 hour (01-12)
H - 2 digit 24 hour (00-23)
i - 2 digit minutes (00-59)
s 0 2 digit seconds (00-59)


OTHER e - timezone (Ex: GMT, CST)
I - daylight savings (1=yes, 0=no)
O - offset GMT (Ex: 0200)
Z - offset in seconds (-43200 - 43200)
r - full RFC 2822 formatted date

Basic PHP Syntax video

http://video.about.com/php/Basic-PHP-Syntax.htm

Mysql Temporary tables

he temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.

Temporary tables where added in MySQL version 3.23. If you use an older version of MySQL than 3.23 you can't use temporary tables, but you can use heap tables.

As stated earlier temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQl database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Example

Here is an example showing you usage of temporary table. Same code can be used in PHP scripts using mysql_query() function.

mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SalesSummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec)

When you issue a SHOW TABLES command then your temporary table would not be listed out in the list. Now if you will log out of the MySQL session and then you will issue a SELECT command then you will find no data available in the database. Even your temporary table would also not exist.

Dropping Temporary Tables:

By default all the temporary tables are deleted by MySQL when your database connection gets terminated. Still you want to delete them in between then you do so by issuing DROP TABLE command.

Following is the example on dropping a temproary table.

mysql> CREATE TEMPORARY TABLE SalesSummary ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SalesSummary -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SalesSummary; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SalesSummary; mysql> SELECT * FROM SalesSummary; ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

MYSQL index

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

While creating index it should be considered that what are the columns which will be used to make SQL queries and create one or more indexes on those columns.

Practically, Indexes are also type of tables which keeps primary key or index field and a pointer to each record in to the actual table.

The users cannot see the indexes, they are just used to speed up queries and will be used by Database Search Engine to locate records very fast.

INSERT and UPDATE statements takes more time on tables having indexes where as SELECT statements become fast on those tables. The reason is that while doing insert or update, database need to inert or update index values as well.

Simple and Unique Index:

You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

You can use one or more columns to create an index. For example we can create an index on tutorials_tbl using tutorial_author

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

You can creates a simple index on a table. Just omit UNIQUE keyword from the query to create simple index. Simple index allows duplicate values in a table.

If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

ALTER command to add and drop INDEX:

There are four types of statements for adding indexes to a table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) : This statement adds a PRIMARY KEY, which means that indexed values must be unique and cannot be NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):This statement creates an index for which values must be unique (with the exception of NULL values, which may appear multiple times).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list):This adds an ordinary index in which any value may appear more than once.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):This creates a special FULLTEXT index that is used for text-searching purposes.

Here is the example to add index in an existing table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can drop any INDEX by using DROP clause along with ALTER command. Try out following example to drop above created index.

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

You can drop any INDEX by using DROP clause along with ALTER command. Try out following example to drop above created inde x.

ALTER Command to add and drop PRIMARY KEY:

You can add primary key as well in the same way. But make sure Primary Key works on columns which are NOT NULL.

Here is the example to add primary key in an existing table. This will make a column NOT NULL first and then add it as a primary key.

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can use ALTER command to drop a primary key as follows:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

To drop an index that is not a PRIMARY KEY, you must specify the index name.

Displaying INDEX Information:

You can use SHOW INDEX command to list out all the indexes associated with a table. Vertical-format output (specified by \G) often is useful with this statement, to avoid long line wraparound:

Try out following example:

mysql> SHOW INDEX FROM table_name\G ........