Let’s Get Started – Part I – setting up the MySQL database and relevant SQL commands
You must have a MySQL server set up by your host or on your local machine. It is assumed that yourself or your server administrator has already created a database that you can use. Make sure to read all the documentation your host has available on using MySQL on their servers. I’ll attempt to explain the easiest way to set up a table with your shell account. If you server doesn’t allow the use of telnet they will have other methods available to you to create your table. You can also just use PHP to create your table once your database is set up.
Once your database has been set up. Open up your shell account. From the telnet command prompt type in “mysql -p YourDatabaseName“. Then you will be prompted for a password. Type in your database password. You’ll see some text appear after this, then the command prompt changes to read mysql>; (then Type) use Database Name. We will name the table “saveMovie” for this example. Then type in the following create table syntax as it appears below:
You can then use the describe table command to see what the table looks like.
Here are a couple other MySQL commands you may find useful from the shell.
Change the Column Name:
mysql>alter table saveMovie change Name SomeotherName varchar(30);
This just changes the name; notice the syntax — after change enter the old Column Name followed directly after by the new column name and definition.
Change the Column Type:
mysql>alter table saveMovie change Name Name varchar(70);
This will allow users with Names longer then 30 characters to store their name properly. Otherwise their limited to 30 characters.
Add Timestamp:
mysql>alter table saveMovie add lastAccessed timestamp;
This just adds a column to your database; in this case it’s a timestamp, you can use this to see when the user last logged in and changed their settings.
Delete one of the Columns:
mysql>alter table saveMovie drop Name;
This just deletes the column that you had previously named “Name”
Select and view everything in the Table:
mysql>Select * from TableName;
Delete the whole table and start over:
mysql>drop saveMovie;
