Create MySQL Database on hostgator

The following link walks through how to use Hostgator's CPanel Database Wizard to create a MySQL database. It also walks through how to create a user account and password for the database (each database will have its own user and it is NOT the same to your linux account. Create database with cpanel database wizard

Run MySQL client from command line

The following link walks through how to setup putty to access your Hostgator account with SSH.

Connect to Hostgator server with Putty

Start mysql client from the Putty command line

Hostgator has started the MySQL server program and you as a shared user can't start/stop the server (as far as I know). But you can start the mysql client from the command line. Eventually you probably won't used the mysql command line and you'll probably do what you need from php.

Start mysql client. -u is the your new username that you created when you setup the database. -p just causes the mysql to ask for your password.

  mysql -u jwrr_dummy -p

You should see the mysql welcome screen and the mysql prompt.

Show MySQL Databases

Use the show databases command to see what databases are available. You should see your new database. In this example you should see jwrr_dummy. Important Note: The semicolon ; is required at the end of EVERY mysql command. If you see the -> prompt, just enter ; .

mysql> show databases;



+--------------------
 Database           |
+--------------------
 information_schema 
 jwrr_dummy         |
+--------------------+
2 rows in set (0.08 sec)

Select Your MySQL Database

The use command selects the database you want to use

use jwrr_dummy;

Add MySQL Table to Database

Use the Create Table command to add a table to the database.

In this example a table is create that has two fields (columns). The first field is 'id' and it is a unique identifying key. I made it of type bigint but mysql has many integer types. The second field is the name and is a 32-character string.

  create table users (id bigint not null auto_increment primary key, name char(32));

Add Column Field to Existing MySQL Table

The Alter Table lets you add or delete columns from your MySQL table.

mysql> alter table users add (strength tinyint);


Current database: jwrr_dummy


Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Show MySQL Table Columns, Fields, Schema

The Show Columns

mysql> show columns from users;
Current database: jwrr_dummy

+----------+------------+------+-----+---------+----------------
 Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------
 id       | bigint(20) | NO   | PRI | NULL    | auto_increment 
 name     | char(32)   | YES  |     | NULL    |                
 strength | tinyint(4) | YES  |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Insert Entry to MySQL Table

Use the MySQL Insert Command to add entries into a table. The followng example adds one entry into the table, and then adds multiple entries into the table.

  insert into users (name,strength) values ('george',5);

  insert into users (name,strength) values ('abe',6), ('teddy',7);

Search MySQL Table and Select Columns and Rows

The MySQL Select Command is the main way to read the database and select the entries and fields of interest. The following example selects all the columns and rows.

 Select all columns and rows

mysql> select  from users;
+----+--------+----------
 id | name   | strength |
+----+--------+----------
  1 | george |        6 
  2 | abe    |        8 
  3 | teddy  |       13 |
+----+--------+----------+
3 rows in set (0.00 sec)

mysql> select name,strength from users;
+--------+----------
 name   | strength |
+--------+----------
 george |        6 
 abe    |        8 
 teddy  |       13 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select name from users where strength<=8;
+--------
 name   |
+--------
 george 
 abe    |
+--------+
2 rows in set (0.00 sec)

mysql> create table teams (id bigint not null auto_increment primary key, name char(32), color char(32));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into teams (name,color) values ('orcs','blue'), ('elves','green'), ('dwarves','red'), ('wizards','blue');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select  from teams;

+----+---------+-------
 id | name    | color |
+----+---------+-------
  1 | orcs    | blue  
  2 | elves   | green 
  3 | dwarves | red   
  4 | wizards | blue  |
+----+---------+-------+
4 rows in set (0.00 sec)

The following uses alter to modify the users table to add a pointer from the 'users' table to the 'teams' table

mysql> alter table users add (team integer);


Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Let's take a look to see that the pointer was added.

mysql> select * from users;
+----+--------+----------+------
 id | name   | strength | team |
+----+--------+----------+------
  1 | george |        6 | NULL 
  2 | abe    |        8 | NULL 
  3 | teddy  |       13 | NULL |
+----+--------+----------+------+
3 rows in set (0.00 sec)

The following example uses update assigns a team to each user.

mysql> update users set team=1 where name='george';
mysql> update users set team=2 where name='abe';
mysql> update users set team=3 where name='teddy';

The following uses insert to add more users.

mysql> insert into users (name,strength,team) values('gandolf',2,4),('legalos',4,2),('gimli',16,3);

Now we%apos;re ready for some interesting stuff. First list every userd and her/his associated team. Note that the user name is from the users table and the team is from the teams table.

mysql> select users.name,teams.name from users,teams where users.team=teams.id;

+---------+---------
 name    | name    |
+---------+---------
 george  | orcs    
 abe     | elves   
 legalos | elves   
 teddy   | dwarves 
 gimli   | dwarves 
 gandolf | wizards |
+---------+---------+
6 rows in set (0.00 sec)

Now find all users that have a team color of blue. Note, the orcs and wizards were both assigned the color blue so both george the orc and gandolf the wizard

mysql> select users.name,teams.name from users,teams where users.team=teams.id and teams.color='blue';

+---------+---------
 name    | name    |
+---------+---------
 george  | orcs    
 gandolf | wizards |
+---------+---------+
2 rows in set (0.00 sec)

Using PHP to Access MySQL

Using PHP to access MySQL is similar to what we just did using the mysql client. The basic steps are:

• Connect and Login to the MySQL Server (mysql_connect)
• Check for errors after each step (mysql_error)
• Select the database to USE (mysql_select_db)
• Build a SEARCH string and send it to the MySQL server (mysql_query)
• Get the results from MySQL (mysql_fetch_array)
• mysql_free_result
• mysql_close

Here is a PHP-MySQL example