System administration and programming
         | | | | | |      
        

 
PHP + MySQL sample code

Here are two sample scripts which demonstrate how to connect to MySQL database using the PHP scripting language. The first one is an example without using PHPLIB, whereas the second one relies on PHPLIB.

1. Before you start

It is supposed that you have the following:

  • A working system with Apache + MySQL + PHP installed and properly configured. You may want to take a look at my tutorial if you do not have a working system.
  • MySQL: You need to know the location of your database server, the username and password for database connection, and the database and table names. The database should also have the proper permissions set (i.e., allowing you to connect to it from a particular host).

2. Sample One: Without PHPLIB support

<?

/* declare some relevant variables */
$hostname = "localhost";	/* This is the hostname on which your MySQL is running */
$dbName = "sampledb";		
$username = "myusername";
$password = "mypassword";
$table = "mysampletable"; /* MySQL table created to store the data */
/* Make connection to database */
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to dat
abase");

/* Select the database to be processed */
@mysql_select_db( "$dbName") or die( "Unable to select database");

/* Prepare the SQL query statement */
$query = "SELECT * FROM $table";

/* Execute the query */
$result = MYSQL_QUERY($query);
/* Now do something with the query result. e.g. print out the number of selected rows. */
$numberofrow = mysql_num_rows($result);
print "$numberofrow";
/* Close the database connection */
MYSQL_CLOSE();
?>

Please check the PHP reference at  http://www.php.net/manual/html/ref.mysql.html for all the available functions related to MySQL.

3. Sample Two: With PHPLIB support

Step 1: Create a new database class

Create a new database class in the local.inc file of the PHPLIB package you have installed on your system. For example,

class DB_Hanzi extends DB_Sql {
var $Host = "localhost";
var $Database = "mydb";
var $User = "webuser";
var $Password = "whoknows";
}

Step 2: Prepare your .php script

<?php
/* We define two sample variables for the following database query */
$myemail = "nobody@example.com";
$table = "userinfor";
/* Create an instance of the database class */
$db = new DB_Hanzi;
/* Prepare a query statement */ 
$query = "Select * from $table where email=\"$myemail\"";
/* Execute the query */
$db->query($query);
/* Do something with the query result, e.g., printing out the email address */
while ($db->next_record()) {
$db->p("email");
}
?>

For more information about PHPLIB, please read the relevant documents, especially the DB_Sql section (Chapter 3) of the PHPLIB document. More sample codes can be found from www.zend.com, www.php.net and www.phpbuilder.com.