Creating MySQL Databases

phpMyAdmin

phpMyAdmin is the best tool for setting up databases. Most servers that have MySQL come equipped with a version of phpMyAdmin. This is a tool built in php that simplifies all of the operations with MySQL. This is what is normally used for creating Databases and Tables.

SQL

If you do not use phpMyAdmin, another way to create a table is to make a one-time-use php script like the one below.

• <?php
  • $sql = "CREATE TABLE `comments` (
    • `id` INT( 16 ) NOT NULL AUTO_INCREMENT ,
    • `name` VARCHAR( 255 ) NOT NULL ,
    • `email` VARCHAR( 255 ) NOT NULL ,
    • `subject` VARCHAR( 255 ) NOT NULL ,
    • `message` TEXT NOT NULL ,
    • `created` INT( 16 ) NOT NULL ,
    • PRIMARY KEY ( `id` )
    • );";
• ?>

In the above code, we are setting a variable equal to what we want to tell MySQL to do. "CREATE TABLE" is telling MySQL that it wants to create a table and it will be called `email`. After that is where the properties of the table go. In parenthesis we put a list of all of the column names and specification we want. `id` is the column name and after that we say we want it to be an integer with length 16. Most fields will have "NOT NULL" after them, unless you want it to be initialized as NULL. For `id` we want it to be incremented every time something is inserted without having to tell it to. MySQL is awesome enough to figure out the next id and put it there for us. Some other common types of data are VARCHAR and TEXT. TEXT does not need a length specified. We want `id` to be our primary key so we put that line at the end.

Connecting to Databases

So we have the sql we want to use, now we just need to send it to MySQL. One way to do it is through a shell. But some servers don't give access to do that and we can just use php to do the same thing. PHP has built in functions for connecting to a database, selecting tables, executing queries, and more.

• <?php
  • $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
  • mysql_select_db('db_name', $link);
• ?>

The mysql_connect function is passed 3 parameters. The first is the server that has MySQL on it. This is normally localhost because it is on the same machine as the webpage. Next is the username and then the password needed to connect to the database. This function returns an identifier to the connection. This is similar to the file pointer when opening files. Next function is mysql_select_db and it is passed the name of the database and the identifier to the connection.