Learning MySQL & PHP

To learn more about how PHP/MySQL works it's best to follow this tutorial with the below notes in mind:
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html

First you'll need to activate MySQL within your Admin Suite. Then skip right to Lesson 1, step 4 in the tutorial, since PHP and MySQL are already installed on our servers.

Everything else there is very straight-forward except for 2 lines of code. Where the tutorial calls for the following code:

$db = mysql_connect("localhost", "root");
mysql_select_db("mydb",$db);

With your website's information in the proper areas replace the above with the following code. The database name is given in the MySQL area of your Admin Suite, and is probably named "yourdomain_com". Therefore you shouldn't follow the "create database" and "transfer to database" areas since you will already be working in a database that has already been created. And here's the replacement code:

$db = mysql_connect("localhost", "YOUR-USERNAME", "YOUR-PASSWORD");
mysql_select_db("YOUR-DATABASE-NAME",$db);

Note: If you change your password in your administration suite, that does not change your password for you MySQL area.

FAQ - MySQL Databases

FAQ - PHP

Restoring a MySQL Databases from a Backup 
1. Open the control panel for your domain (http://yourdomain/menu)
2. Click the "MySQL" icon.
(If you are denied access it is either because you have changed your domain's password, or because you specified a password different than your domain's password for your database when you created it. Enter the original password for your domain, or enter the password for your database. 
3. Click the name of your database. It will be on the left hand side under the word "Home". By Default it is named yourdomain_com.
4. Open your database backup (yourdomain_com.sql) in notepad (C:\windows\notepad.exe) or other text editor. Copy all the text.
5. In your MySQL control panel scroll down to the text area that says, "·Run SQL query/queries on database yourdatabase_com:". Paste the content of your database backup into this text area, and push "Go".
(If your get an error that says a Table already exists, you will need to first delete the table from the current database and repeat step 5).

Backing Up a MySQL Database 
1. Open the control panel for your domain (http://yourdomain/menu)
2. Click the "MySQL" icon.
(If you are denied access it is either because you have changed your domain's password, or because you specified a password different than your domain's password for your database when you created it. Enter the original password for your domain, or enter the password for your database. 
3. Click the name of your database. It will be on the left hand side under the word "Home". By Default it is named yourdomain_com.
4. Scroll down to where it says, "·View dump (schema) of database" and select "Structure and data" , check the box in front of "Send", and click the "Go" button. Save the file to your hard drive. The file will be named yourdomain_com.sql.

How do I delete a record from a database?
There are 2 ways of deleting a record from your database. The first is that you can write a PHP program to add, delete, and modify records - see the PHP/MySQL tutorial at

http://www.webmonkey.com for that.

Or to just plain delete the file without worrying about PHP you can go to your Admin Suite, click on the MySQL link, then on the link on the left that says something along the lines of "mydomain_com". Under where you clicked a list will pop up with all the different databases you've made. Click on the one you want to delete the record from, then click on the "Browse" link that's near the center of the page. Search through and find the record you want to delete, then just click on the "delete" button next to it. Click the "yes" link to confirm the delete, and that's it.

To be able to delete records you need to make one of the fields the "Primary" field. Just click on the "Primary" link on the right of one of the fields. I'd recommend that you do it on the e-mail field, since you may someday have 2 identical first names and the same with the last names. If you want to know of a good tutorial that will teach you how to access, modify, add, delete, and organize your directory through your browser (rather than through the admin panel), you can follow the below linked tutorial with a couple of modifications. In their tutorial you'll need to replace their lines of:
$db = mysql_connect("localhost", "root");
mysql_select_db("mydb",$db);

with this instead for it to work properly:
$db = mysql_connect("localhost", "USERNAME", "PASSWORD");
mysql_select_db("yourdomain_com",$db);

And here's the link:
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial4.html

Why aren't my table and columns names specified in MySql when using Access 2000?
If you are using Access 2000, you should get and install the newest
Microsoft MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data. This will fix the bug in Access - when you export data to @strong{MySQL}, the table and column names aren't specified.

I'm having problem with the datetime field in MySQL database.

Q: I have a datetime field in the mySQL database. When I add a record through the Administrative Suite, it automatically adds the current date and time.

But, when I INSERT a row and info using PHP script from the web site, it leaves the date and time field filled with the default value of a bunch of zeros.

Any ideas?

A: The solution is to have PHP tell MySQL what the time is, AND have it in the proper format. So to do it in the format you need MySQL to receive it - use the following line:

input type=hidden name="last_modified" value="<?php echo date(HERE) ?

And this webpage will give you the needed info for the formatting part (where I have "HERE", you might need something different):
http://www.php.net/manual/function.date.php3


Do you know how to change the MySQL password by using the SET PASSWORD SQL command?
We have to change your password in telnet as the root user.

Can I change the name of MySQL database?
Q. If I try to create a MySQL database using the control pannel, it appears as if the database must be named "mydomain_com". Is there any way to have the database named differently? I would like to use a canned CGI script that requires the MySQL database to be named "adcycle". Can that be done? Or will I need to try to change the script?

A: You can change the name in your cgi program. It really shouldn't be all that hard to be able to modify it by searching for all occurances of the word that needs to be changed,  making sure it is referring to that database, and changing it to "mydomain_com".
Otherwise, to do what you were asking will require using SecureCRT to access the command prompt via SSH (just click on the "ssh" link in your admin suite for more info), or you can always just modify the cgi program as I mentioned above.

FAQ - PHP

How can I change the default root pages to include .php3 files?
In regards to making PHP files work as the root, you must first make the file index.phtml (which ".phtml" works identical to ".php3" extensions). Then make sure you don't have any other index.html, index.htm, etc. files that might stand in the way of the phtml one.

How do I set up a PHP Program?
A PHP program is embedded directly in the HTML document. It must have a .phtml extension in order for the server to look for PHP code in the document. Here is how you embed the PHP:


?
insert PHP code here
?

Put a < before the ? and a > after the 2nd ?

What is PHP?
PHP is a server-side HTML embedded scripting language that was developed in C and is designed especially for working with relational database systems.

Q: Is it possible to store a image in a MySql table field and display it on a page? how would it be displayed?

A: yes and no. remember that databases store information. In that sense, you could store information about an image. 

a quick example: 

in your database you have an index for all your images, and in another column you have the image source itself: 

ie. 

Table Name: Images 

Field Img_Src 

1 (img src="images/image1.jpg")
2 (img src="images/image2.jpg") 
etc... 

thus, when you query the table now, you can just pull up the HTML image tag information. 

the images themselves would still need to be in the appropriate diretory, etc... 

Security Issues with PHP & MySql

How Secure is MySQL?

1. How safe is our database information MySQL? 

It's safe in terms of general access - the passwords are encrypted and permissions are set to prevent unauthorized access. 

2. How can it be accessed safely?

If you are speaking of a Secure Socket Layer to MySQL through a remote ODBC connection we currently do not have this. Locally a person could use the shared cert to set up an SSL connection to their site and then use php to access their database.

3. Is MySQL secure if I access it through the control panel?

Not secure (no ssl) through the control panel.

4. Can I safely store credit card information in MySQL?

No - credit card information is best stored on a database that is firewall protected. MySQL should not be used to store any sensitive client data like credit card information. The best option is to have it on a database that is not connected to the internet - or if that's not possible to have only an intermittent connection as in a dialup line.

5. Is my password safe when I access MySQL with PHP?

If this is done through https then it would be - the password would be fed securely to php which would then query the database - but this query takes place on the server so there is no network transmission taking place and since the password is initially transmitted via https then it would be protected.

An important thing to remember is that even though one can access their database securely via SSL and PHP or another programming language this is not the same as saying the database is secure from hacking - I think it would be ill advised to store any credit card information on the server ;-). This is not to say it's insecure either but just not secure enough to store this kind of information.