MySQL

How do I Get Started?

Create mysql database by using the Control Panel [CP] Web Based User Interface.

This will generate an database with the following names:

1. Database name: domain_com, my_domain_net, g123_com, etc.

[Notice that '-' and '.' are replaced by underscores, '_']
[a 'g' is placed in front of database name, if domain, that the database name is derived from, begins with a numeral.]

2.Database user name: This will be your CP log in name.

3.Database password: This will be your CP log in pass.

You can access your MySQL Database from the Web using programming languages such as Perl and PHP and by your Control Panel, which uses phpmyadmin.

MySQL is very "plain English" in its commands, which can be confusing when you are used to other systems.

Here are tips:

1) Every command must end with ; or /g

2) When you have a command like:

mysql> update user set Password=password('xxxx')where User='$db_user';

All you replace is the password you want for the xxxxx (keep the quotes) and the mySQL user name ($db_user) it's for, like 'jrogers'. Other than that, enter the whole string as written.

This SQL online tutorial with built-in interpreter can be extremely useful whether novice or experienced. http://www.sqlcourse.com 

Problem:
No edit/delete in MySQL Admin.

Cause:
This occurs when the table, which you have created, does not contain a unique identifying field.

Prevention:
In most cases, this can be avoided by always specifying a primary key for the table that you are creating. 

Solution:
Unfortunately, there is no way of adding a new field to an existing table as a primary key. This is because the primary key cannot contain a NULL value, which would be the case for every record in the table.
The solution...create another table, but include a field that will be a primary key. [The easiest way of doing this is making it an auto_increment number...from the options menu]

phpMyAdmin does not stop the user from entering incorrect syntax, therefore it will accept a line like the one below even though it is incorrect:

Bad:

ALTER TABLE bmt_userinfo ADD first_name TEXT (25) not null , ADD last_name TEXT (25) not null , ADD address1 TEXT (100) not null , ADD address2 TEXT (100) not null , ADD city TEXT (30) not null;

However the same line minus the text length does work:

Good:

ALTER TABLE bmt_userinfo ADD first_name TEXT not null , ADD last_name TEXT not null , ADD address1 TEXT not null , ADD address2 TEXT not null , ADD city TEXT not null;

This is because "TEXT" is a set length. 


[The thing to remember is that any syntax that will not work on the command line will not work in phpMyAdmin either.]

A good place to find out correct syntax for a command that you want to perform is:
http://www.mysql.com/documentation/mysql/commented/

This is a sample perl script that performs mysql connections. This script requires DBI perl module. Please change the following variables inside the script:
$DBuser = "";
$DBpass = ""; 
$DBName = "";


/*************cut script below***************/
#!/usr/bin/perl use DBI; $DBhost = "localhost"; $DBuser = ""; $DBpass = "";  $DBName = ""; print("Content-type: text/htmlnn"); #connect to databases $dbh = DBI->connect("DBI:mysql:$DBName:$DBhost",$DBuser, $DBpass); # pretend query that list tables in the databases @tables = $dbh->func( '_ListTables' ); foreach $table ( @tables ) { print "Table: $tablen"; }  

How do I work with a MySQL database using PHP?

1.To merely display the information in your database without the use of a form to call a php script you simply create your HTML document as you would any other web page but instead of the extension of .htm or .html you need to name the file with the extension .phtml

Then within the document itself the section that you'd like to be the PHP code, you begin it with <? and end it with ?>

NOTE: Your database user name and password for the database are not written in the file when it's displayed on the Internet so users viewing the source of your webpage will not see your password. [note that database_user_name and password are replaced with your db user name and password; localhost is not replaced]

2.When using a CGI script to pull information from a form which has been submitted by a browser you must have the first line of the script have this command on it (Much like perl scripts):

#!/usr/local/bin/php