MySQL Database - Management and Testing

MySQL Database - Management and Testing

Database Management and Access

MySQL databases on your ISP platform are supplied with a 10mb space limit, with customers being able to purchase more database space should they need it.

The standard MySQL Server installation software download provides a command line management tool mysql. This will not work in conjunction with your ISP's MySQL platform which is secured behind a firewall, and as such does not permit such insecure command line access.

Customers can either use their own scripting to modify their MySQL database, or install some third party software in the Unix hosting to provide graphical administrative functions, which is installed by them at their own risk. Example software performing this would be such as 'phpmyadmin'. If customers decide to install 3rd party software for database access in their web hosting, they are advised to consider the security of it. If using 'phpmyadmin' for instance, they would sensibly not consider putting their username and password in the configuration file of the software (To prevent possibility of access to the configuration file directly via http), but should definitely consider the 'http-authentication' (Thus not storing database access details on the hosting, but instead being prompted for them every time they use the software.).

Using PHP and MySQL

When PHP is used to access a MySQL database, the connection details including database name, user name and password are typically held within a connection PHP file that is referred to by PHP pages within the Web hosting.

It is important to secure such connection information; therefore it is advisable for the PHP file containing the database connection information to be stored outside of the web publishing root (E.g. above public_html) and referred to or included by PHP files within the web publishing root (E.g. those PHP files with public_html).

The following is an example connection file conntesting.php, create the file in wordpad / notepad and paste the code below in to the file and make sure the file is called conntesting.php, this should be placed in Unix hosting in the ftp root, above the public_html directory:

<?php

//conntesting.php

//This file must be placed in root of ftp space on Unix, above and

// definitely not in public_html directory. define('DB_NAME','MyDatabaseDame');

// Between the last '' put the database name define('DB_USER','MyUsername');

// Between the last '' put the username define('DB_PASSWORD','MyPassword');

// Between the last '' put the password define('DB_HOST','62.69.64.179'); $conn = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not open connection to MySQL Cluster :' .mysql_error());

mysql_select_db(DB_NAME) OR die ('Was not able to do a select on database: ' .mysql_error()); ?>

And below is an example PHP file testingtablelist.php (again create this file in wordpad / notepad and paste the code below in to the file) from within the public_html web publishing directory that displays a list of tables if any are present within the database: <?php

$page_title = 'Check read of database';
echo '<html><head><title>Check read of database</title></head><body> ';

require_once ('../conntesting.php');

printf("MySQL server version: %sn", mysql_get_server_info());
echo ' ';

$thequery = "SHOW TABLES";
$result = @mysql_query($thequery);

if($result)
{
echo 'List of Tables in Database';
while( $row = mysql_fetch_array( $result ) ) {
echo "$row[0] ";
}

echo '</table>';
mysql_free_result ($result);

}
else
{
echo 'Could not run query, there may be an issue with this database';
}

mysql_close();

echo '</body></html>';
?>Thus with files uploaded, putting http://yourdomain.com/testingtablelist.php into a web browser will display tables names within the database if present. Please Note:

Your ISP does not provide support for customer's own scripting - as in advice, it is for the customer to design and create their own PHP and Perl scripts.

Support Documentation

MySQL Database - Management and Testing