Setting Up a Database In MySQL with PHP

PHP functions for working with MySQL databases include:

Mysql_create_db() attempts to create a new database on the server associated with the specified link identifier. It returns TRUE on success, FALSE on failure. You must include the name of the new database as a parameter. The specified link identifier is an optional parameter (the computer will assume the last link opened). For instance, to create a database named "my_db" on the mysql_host server:

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or die("Could not connect"); if (mysql_create_db ("my_db",$link)) { echo ("Database created successfully"); } else { echo ("Error creating database"); } ?>

$link is the specified link identifier, as you have seen previously, which will call the die function if the connection fails. Then mysql_create_db ("my_db") function is called within the if statement. Recall the mysql_create_db function returns a Boolean value (true or false). If "true", the database was created and the if statement directs the screen to display "Database created successfully". If "false", the else statement directs the screen to display "Error creating database"

Mysql_list_dbs() will return a result pointer containing the databases available from the current mysql connection. The only parameter is the specified link identifier ($link in our examples). You can then use the results in your script, or as we do here, display it on the page:

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or die("Could not connect"); $db_list = @mysql_list_dbs($link) or die("Could not retrieve database list"); $numrows = @mysql_num_rows($db_list); for ($i=0; $i<$numrows; $i++) { echo mysql_db_name($db_list, $i) . "<br>"; } ?>

As seen in the example above, mysql_db_name() takes as its first parameter the result pointer from a call to mysql_list_dbs() ($db_list in the example above). The second parameter is an index into the result set. If an error occurs, FALSE is returned. This function is always used in conjunction with mysql_list_dbs.

Mysql_select_db() sets the current active database on the server that's associated with the specified link identifier. If no link identifier is specified, the last opened link is assumed. If no link is open, the function will try to establish a link as if mysql_connect() was called without arguments, and use it. It returns TRUE on success, FALSE on failure.

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or die("Could not connect"); $db = @mysql_select_db("database_name",$link) or die("Could not select database"); ?>

Mysql_drop_db() attempts to drop (delete) an entire database from the server associated with the specified link identifier. It also returns TRUE or FALSE. Note, however, if the named database does not exist, an error results, so be sure the database you want to drop is there. For instance, to drop the database you just created (my_db):

<?php $link = @mysql_connect("mysql_host", "userid", "userpassword") or die("Could not connect"); if (mysql_drop_db ("my_db",$link)) { echo ("Database dropped successfully"); } else { echo ("Error dropping database"); } ?>

Needless to say, YOU MUST BE VERY CAREFUL WITH THIS FUNCTION. Once you delete a database, there is no getting it back.

Using PHP to Access MySQL Databases