Knowledgebase: MySQL/Database
How
do I add or remove a MySQL database from my account?
The default name for the database will be the domain name, and the default
user name will be the domain's user name. You can set a separate password
or use the same one. Just follow the instructions when you click on the
mySQL icon on your control panel.
Example:
domain: foo-bar.com
user: foo
pass: foospass
Resultant database:
db name: foo_bar_com
db user: foo
db pass: foospass
[To add/remove additional databases, or to use another user name, follow
other KBs in the MySQL category for directions]
What
are the advantages of using MySQL and PHP over ASP?
Here are some of the reasons to switch:
1 - UNIX is more reliable. Here are some articles backing this up. UNIX
vs. NT 4.0 Service Pack 3 from an experienced user: http://www.zdnet.com/sr/columns/sjvn/980528.html
UNIX vs. NT 5.0 with detailed comparison: http://www.zdnet.com/zdnn/content/inwk/0513/305389.html
2 - MySQL is a very fast Database System. This page will allow someone
to compare MySQL with a bunch of other database systems. http://www.mysql.com/benchmark.html
3 - MySQL is very functional. This page allows comparisons of the different
functions between MySQL and other Database Systems. http://www.mysql.com/crash-me-choose.htmy
4 - PHP 4.0 is designed to be fast and was written to do database work.
PHP 4.0 is a server side HTML embeded scripting language, meaning that
PHP is embeded in the HTML files and the server does the work of translating
the PHP 4.0. This means that it is totally platform and browser independent.
[it is possible to make things platform and browser independent with Microsoft
ASP (Active Server Pages) having the server do all the compiling, but
PHP 4.0 is backed up with UNIX reliability and Database speed; ASP is
not quicker than PHP 4.0]. PHP 4.0 is reliable and fast. You can look
here for further information on PHP 4.0: http://www.php.net
How
do I connect MS Access or MySQL databases?
You will first need to open a support ticket and request that we give
you access to MySQL remotely, e.g., ODBC.
WARNING!!! By openning up ODBC access, there are two security risks:
1 - IP ACCESS - We strongly encourage clients to use a static IP. REASON
- When opening access for a given SET of IPs - 67.135.23., Access is openned
wide for someone to access the database unauthorized FROM MULTIPLE IPs.
A possible HACK attempt may be made.
2 - NO ENCRYPION - Any information sent via ODBC access is unencrypted.
While compressing data does not encrypt it, we do encourage clients to
compress any data sent through ODBC access
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 SourceName
d. Select the tables(s) to link
Test! Done!
What
should I know to get started with MySQL database?
1 - Create mysql database by using the Control Panel [CP] Web Based User
Interface. This will generate an database with the following names: .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.
4 - Once it's created, from your SSH prompt, type the following command
line to access your database:
~$ /usr/local/mysql/bin/mysql $dbname -u $username -p$password
[no space between the -p and the password]
You can also access your MySQL Database from the Web using programming
languages such as Perl and PHP and by your Control Panel, which uses phpmyadmin.
What
commands do I use to debug MySQL?
printf("MYSQL DEBUG: %s %s: <BR>\n", mysql_errno(), mysql_error());
Where
can I find advanced instructions for using MySQL database?
For detailed information regarding MySQL, you can go to the MySQL Online
Manual:
http://www.mysql.com/Manual/manual_toc.html
When
browsing a table using MySQLadmin only one page displays. Whats wrong?
The table name contains the word "database" in it. MySQLAdmin
checks for the word "DATABASE" before it executes any query.
If the query contains "DATABASE", "ALTER TABLE", "DROP
+TABLE", or "DELETE FROM" MySQLAdmin will verify that you
want to perform the query since these normally alter the table/database.
Using MySQLAdmin, change the name of the table so that it no longer contains
the word "database." You may want to change it from "database"
to "databas" to fix the problem.
Technical:
For those of you that want the technicalities, here is the line that does
this in sql.php3 (line 22 - 31):
----------------------------------------------------------------------
$is_drop_sql_query = eregi("DROP +TABLE|DATABASE|ALTER TABLE +[[:alnum:]]*
+DROP|DELETE FROM", $sql_query); // Get word "drop"
if (!$cfgConfirm)
{
$btnDrop = $strYes;
}
if ($is_drop_sql_query && !isset($btnDrop))
{
@include("header.inc.php3");
echo $strDoYouReally.urldecode(stripslashes($sql_query))."?<br>";
----------------------------------------------------------------------
I
cannot edit/delete in MySQLadmin. Whats wrong?
This occurs when the table, which you have created, does not contain a
unique identifying field.
In most cases, this can be avoided by always specifying a primary key
for the table that you are creating.
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]
Can
I enter information or code using phpMyadmin?
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/"
How
do I work with 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 ?>. For instance:
<P>These are the products I sell:</P>
<TABLE BORDER="1">
<?
mysql_connect("localhost", "database_user_name", "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>
Thus having the loop in the php program create a table with the products
listed. 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 above 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
I
get a "write conflict" error when I try to modify a field in
MS Access. Whats wrong?
The reason for this is that Access needs to have a field that is a timestamp
in order for it to update fields. You should just create a field in your
table that is type TIMESTAMP, and you will then be able to update the
table.
Go to "http://www.mysql.com/documentation/mysql/commented/manual.php?section=ODBC"
for more information.
© 2003 Burningbulb.net |
