Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - PHP Connections and Query Execution

By: FYIcenter.com

Part:   1  2  3  4   5  6 

(Continued from previous part...)

How To Get a List of Databases from MySQL Servers?

Once you got a MySQL server connection object successfully, you need to know what databases are available on the server and which database you should use to manage your tables and data. To get a list of all available databases on your MySQL server, you can use the mysql_list_dbs() function. Note mysql_list_dbs() will not return an array of database names. It returns a result object, which requires mysql_fetch_object() call to loop through each database object.

The tutorial exercise below shows you a good example of how to get a list of databases as a result object. Then "while" loop is used to fetch each database object from the result object. Finally, an object property "$obj->Database" is used to get the database name:

<?php
  $con = mysql_connect('localhost:8888', 'dev', 'iyf');
  print("List of databases:\n");
  $res = mysql_list_dbs($con);
  while ($obj = mysql_fetch_object($res)) {
     print($obj->Database . "\n");
  }
  mysql_close($con); 
?>

If you run this script, you will get something like this:

List of databases:
information_schema
mysql
test

How To Create a New Database?

A database in a MySQL server is a logical container used to group tables and other data objects together as a unit. If you are a the administrator of the server, you can create a new databases using the CREATE DATABASE statements with MySQL client interface programs.

If you want to create a new database with a PHP script, you can still use deprecated function, mysql_create_db(). But it's better use the mysql_query($sql,$con) function to create new databases. The tutorial exercise below shows you how to create a database called "fyi":

<?php
  $con = mysql_connect('localhost:8888', 'dev', 'iyf');
  $sql = 'CREATE DATABASE fyi';
  if (mysql_query($sql, $con)) {
    print("Database fyi created.\n");
  } else {
    print("Database creation failed.\n");
  }
  mysql_close($con); 
?>

If you run this script, you will get something like this:

Database fyi created.

What Happens If You Do Not Have Privileges to Create Database?

If your MySQL server is provided by your Internet service company, your user account will most likely have no privilege to create new databases on the server. In that case, your CREATE DATABASE statement will fail. Here is an example of using a less privileged user account to create a new database:

<?php
  $con = mysql_connect('localhost:8888', 'guest', 'pub');
  $sql = 'CREATE DATABASE fyicenter';
  if (mysql_query($sql, $con)) {
    print("Database fyi created.\n");
  } else {
    print("Database creation failed.\n");
  }
  mysql_close($con); 
?>

If you run this script, you will get something like this:

Database creation failed.

(Continued on next part...)

Part:   1  2  3  4   5  6