Creating MySQL Databases with PHP

You can if you wish, actually create your databases with PHP rather than using MySQL client tool. This practice has potential advantages – you can use an attractive front end that may appeal to those who find the MySQL command-line client horribly plain or finicky to use-counterbalanced by one big disadvantage, which is security.

To create a database from PHP, the user of your scripts will need to have full CREATE/DROP priveleges on MySQL. That means anyone who can get hold of your scripts can potentially blow away all your databases and their contents with the greatest of ease. This is not such a great idea from a security standpoint.

If you are even considering creating databases with PHP, do yourself a big favour and at least don’t store the database username and password in a text file. Make yourself type your database username and password into a form and pass the variables to the inserting handler each and every time you use this script. This is one case where keeping the variables in an include files outside your web tree is not sufficient precaution. Better yet, run the scripts manually from the command line through SSH:
mysql-u <username> -p <databasename> <sql-script.sql for those times when you need to create database programmatically, the relevant functions are: mysql_create_db():create a database on the designated host, with name specified arguments
mysql_drop_db(): Delete the specified database
mysql_query():passes table definitions and drops in this function

A bare-bones database-generation script might look like this:

<?php
    $linkID=mysql_connect('localhost','root', 'sesame'); mysql_create_db('new_db',$linkID); 
    mysql_select_db('new_db'); 
    $query="CREATE TABLE new_table 
    ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, new_col VARCHAR(25) )"; 
    $result=mysql_query($query); 
    $axe=mysql_drop_db('new_db');
?>

Several other GUI tools are available that are not database-specific but will probably work with MySQL. As MySQL has become more and more popular, a number of applications for both windows and Linux have come into play that allow you to administer MySQL databases in the graphical fashion you may have become accustomed to. Like their web counterparts, these applications offer full administrative control, but without the headache of exposing yourself to the security risk of web based interface .

Leave a Reply

Your email address will not be published. Required fields are marked *