What can I use to setup a database?
The main database available on our system is mySQL
which is what you use to create and manage your database files.
In order to display them on the Internet you have to use a program
called PHP.
The Manuals for these programs are here:
Okay, what to do...
- If you want to access your database through Telnet, from your
telnet prompt you type the following command line to access
your database:
/usr/local/mysql/bin/mysql dbname -u username -ppassword
(no space between the -p and the password)
- For further instruction on creating tables and inserting data
into those tables consult the mySQL
Manual.
- For instructions on using PHP for displaying your databases
on the Internet, see the PHP Manual.
- If you want you access your MySql db remotely (instead
of from localhost).
What the Manuals DON'T tell you...
There is information you require which is not available in the
mySQL and PHP manuals. Below is this information:
MySQL
This is given to you assuming you have knowledge of mySQL
or have reviewed the manual mentioned above.
- When doing any command in mySQL you first type in the command
and immediately after you hit enter you are presented with another
prompt as if it didn't do anything. In order for your command
to go through you have to type the word, "go". For
example, at the mySQL prompt you type:
CREATE TABLE products (prodid int(10) not null, name
char(200), price decimal(5,2), primary key (int))
[you are then brought to a prompt that looks like:]
->
[at this prompt you must type go for the instructions
to be sent to the mySQL server.]
- If you pull in data from an ASCII delimited text file to populate
your database table. The manual gives you the command,
load
data infile 'file.txt' into table products [more options...]
but you need to give the full path to the text file you want
to import. Which means you first need to ftp the text file to
your directory and then give the command by typing the full
path to the file like:
load data infile '/home/username/path/to/file/file.txt'
into table products [more options...]
Connecting MS Access to a MySQL database
ON LOCAL PC:
1) Download and Install MyODBC 2.50.19 (or most current version)
on local
Win95 machine with MS Access installed
--can download at: http://www.mysql.com/download.html
2) Fill in the following settings:
Windows DNS Name: You can choose the names, must be unique
Server: This is your domain name or IP address
MySQL Database Name: The name of your MySQL database
User: Your MySQL username
Password: Your MySQL password
Port: leave blank for default (3306(
Options; Select "Return Matching Rows"
3) To Link a Table
a) File...Get External Data...Link Tables
b) Under 'Files of Type:', select "ODBC Database"
c) Select Machine Data Source Tab, and select the appropriate
Data Source
Name
d) Select the tables(s) to link
Let us know if you need anything else.
PHP
This is given to you assuming you have knowledge of PHP
or have reviewed the manual mentioned above.
- 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 .php3. Then within the document itself the section
that you'd like to be the PHP code, you begin it with
<?PHP
and end it with ?>. For instance:
These are the products I sell:
<HTML>
<HEAD>
<TITLE>My products</TITLE></HEAD>
<BODY><P>These are the products I sell:</P>
<TABLE BORDER="1">
<?PHP
mysql_connect(localhost, username, password);
$result = mysql(mydatabase, "select * from products");
$num = mysql_numrows($result);
$i = 0;
while($i < $num) {
echo "<TR>\n"; echo "<TD>\n";
echo mysql_result($result,$i,"prodid");
echo "</TD>\n<TD>";
echo mysql_result($result,$i,"name");
echo "</TD>\n<TD>";
echo mysql_result($result,$i,"price");
echo "</TD>\n";
echo "</TR>\n";
$i++;}
?>
</TABLE></BODY> </HTML>
Thus having the loop in the php program create a table with
the products listed. NOTE your username 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.
- 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
