Intro to Databases - Connecting to the database and selecting the database
(Page 5 of 8 )
When working with databases the first thing you have to do is create a connection to the database. It is pretty simple so have no fear - your trusty tutorial is here.
Natively
To set up a connection using the native PHP functions you would use mysql_connect() or mysql_pconnect() for a persistant connection. The input parameters are the address of the database, the username to gain access and the user's password. Returned will ba a link to the database. With your link you can now select the database you will be working with. This is done with the mysql_select_db() function.
A note about database security - you should NEVER have the database access information located in the script itself. It is a HUGE security hole for your site that someone will be happy to exploit. There are many opinions about where to keep the database information and how to access it. I suggest you read the section discussing this in Chris Shiflett's Security Workbook which is provided free on the web in .pdf format.
<?php /* access your database information where ever it is */
$db = mysql_connect('localhost', '$mysql_user', '$mysql_password'); /* if there is a problem connecting to the DB - tell me and display any error messages given then stop the program gracefully */ if (!$db) { echo 'Error connecting to the datebase. Message returned: ' . mysql_error(); die; } $link = mysql_select_db('$db_name'); /* if there is a problem selecting the DB - tell me and display any error messages given then stop the program gracefully */ if (!$link) { echo 'Error selecting the datebase. Message returned: ' . mysql_error(); die; } ?>
PEAR DB
Some people when they look at how to do the same thing using the PEAR DB are a little bit intimidated. The whole DSN (Data Source Name) thing can kind of throw them off. All those variables... all the different variations of it. But have no fear - I will show you in some code the basics needed and explain what each part is. As you become more comfortable with PEAR DB - please look more into what the various parts of the DSN are resposible for.
A note about database security worth repeating - you should NEVER have the database access information located in the script itself. It is a HUGE security hole for your site that someone will be happy to exploit. There are many opinions about where to keep the database information and how to access it. I suggest you read the section discussing this in Chris Shiflett's Security Workbook which is provided free on the web in .pdf format.
DSN - Below is the code and explainations for the basic DSN requirements. Please note that all the data (except for phptype) given is the same data needed in the native functions. Also keep in mind that if the database system is something other then MySQL you would have to change the phptype and possibly add the dbsyntax variable.
Remember me telling you earlier that a user can change database systems and only have to alter 1 or 2 lines of their code to reflect it when they use PEAR DB? Well the DSN is where those lines would be changed to reflect the new database system.
<?php $dsn = array( 'phptype' => 'mysql', /* the php backend being used Ex: mysql, odbc, postgreSQL, (pgsql), sqlite */ 'username' => 'mysql_user', /* the username to access the database */ 'password' => 'mysql_password', /* the password to access the database */ 'hostspec' => 'www.domain.com', /* the webserver that has the database Ex: localhost, www.google.com */ 'database' => 'db_name', /* the name of the database to be used */ ); ?>
Connect - Now comes the easy part - getting the PEAR DB in our script, and connecting to the database. The first thing that you need to do is include the DB package (require_once 'DB.php';) at the top of your code. After that you simply call the connect method. Since my example doesn't use any of the "options" available for the connection the code would look like this:
<?php require_once 'DB.php'; /* access your database information where ever it is */
$db =& DB::connect($dsn, $options); /* if there is a problem connecting to the DB - tell me and display any error messages given then stop the program gracefully */ if (DB::isError($db)) { echo 'Error selecting the datebase. Message returned: '; die($db->getMessage()); } ?>
/ 3 