Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - PHP Connections and Query Execution

By: FYIcenter.com

Part:   1  2  3  4  5   6 

(Continued from previous part...)

How To Get MySQL Statement Execution Errors?

When you execute a MySQL statement with mysql_query(), and the statement failed, mysql_query() will return the Boolean value FALSE. This is good enough to tell that there is something wrong with that statement. But if you want to know more about the cause of the failure, you can use mysql_errno() and mysql_error() get the error number and error message. The tutorial exercise below shows you an improved version of the previous PHP script:

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

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

Database creation failed with error:
1044: Access denied for user 'guest'@'%' to database 'fyicenter'

How To Drop an Existing Database?

If want to drop an existing database from the MySQL server, you can use the DROP DATABASE statement. Here is a good example of dropping an existing database:

  $con = mysql_connect('localhost:8888', 'dev', 'iyf');
  $sql = 'DROP DATABASE fyi';
  if (mysql_query($sql, $con)) {
    print("Database fyi dropped.\n");
  } else {
    print("Database drop failed with error:\n");
    print(mysql_errno($con).": ".mysql_error($con)."\n");
  }
  mysql_close($con); 
?>

If you run this script, you will get something like this, if "dev" does not have privilege to drop database:

Database drop failed with error:
1044: Access denied for user 'dev'@'%' to database 'fyi'

How To Select an Exiting Database?

The first thing after you have created a connection object to the MySQL server is to select the database where your tables are located, by using the mysql_select_db() function. If your MySQL server is offered by your Web hosting company, they will assign a empty database to you and provide you the database name. You should use this name to select this empty database as your current database. The following script shows you how to select a database called "fyi". It also shows you how to put all the database connection statements in a single include file, and re-use it in all of your PHP pages.

Create the include file, connect.php, with the following statements:

<?php
  $server = "localhost";
  $username = "dev";
  $password = "iyf";
  $database = "fyi";
  $con = mysql_connect($server, $username, $password);
  mysql_select_db($database, $con);
?>

To test this database connection and selection include file, try the following script:

<?php
  include "mysql_connection.php";
  $sql = 'SHOW TABLES';
  if ($rs = mysql_query($sql, $con)) {
    print(mysql_num_rows($rs) . " tables in the database.\n");
  } else {
    print("SHOW TABLES failed.\n");
  }
  mysql_close($con); 
?>

You will get something like this:

0 tables in the database.

(Continued on next part...)

Part:   1  2  3  4  5   6