Create a MySQL Database With PHP

http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/create-a-mysql-database-with-php.aspx

To create a database use the mysql_query() function to execute an SQL query like this

$query = "CREATE DATABASE phpcake";
$result = mysql_query($query);

include 'closedb.php';
?>

Please note that the query should not end with a semicolon.

PHP also provide a function to create MySQL database, mysql_create_db(). This function is deprecated though. It is better to use mysql_query() to execute an SQL CREATE DATABASE statement instead like the above example.

If you want to create MySQL database using PHP mysql_create_db() function you can do it like this :

mysql_create_db('phpcake');

include 'closedb.php';
?>

If you want to create tables in the database you just created don't forget to call mysql_select_db() to access the new database.

Note: some webhosts require you to create a MySQL database and user through your website control panel (such as CPanel). If you get an error when trying to create database this might be the case.

Creating the Tables

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

Example : contact.php
Source code : contact.phps

$query = 'CREATE DATABASE phpcake';
$result = mysql_query($query);

mysql_select_db('phpcake') or die('Cannot select database');

$query = 'CREATE TABLE contact( '.
'cid INT NOT NULL AUTO_INCREMENT, '.
'cname VARCHAR(20) NOT NULL, '.
'cemail VARCHAR(50) NOT NULL, '.
'csubject VARCHAR(30) NOT NULL, '.
'cmessage TEXT NOT NULL, '.
'PRIMARY KEY(cid))';

$result = mysql_query($query);

include 'closedb.php';
?>

Of course when you need to create lots of tables it's a good idea to read the query from a file then save in $query variable instead of writing the query in your script.

$queryFile = 'myquery.txt';

$fp = fopen($queryFile, 'r');
$query = fread($fp, filesize($queryFile));
fclose($fp);
$result = mysql_query($query);

include 'closedb.php';
?>

Deleting a Database

As with creating a database, it is also preferable to use mysql_query() and to execute the SQL DROP DATABASE statement instead of using mysql_drop_db()

// ... do something here

$query = 'DROP DATABASE phpcake';
$result = mysql_query($query);

// ... probably do something here too

include 'closedb.php';
?>

After the database and the tables are ready it's time to put something into the database.

Mike Stone Web Developer

Sunday, September 11, 2011