Getting PHP to Talk to MySQL - Including Database Login Details
(Page 3 of 4 )

You're going to create a file to hold the information for logging into MySQL. Storing this information in a file you include is recommended. If you change the database password, there is only one place that you need to change it regardless of how many PHP files you have that access the database.

You don't have to worry about anyone directly viewing the file and getting your database login details,. The file, if requested by itself, is processed as a PHP file and returns a blank page.

Let's call this file db_login.php and place it in the same directory as your other PHP files. The file is represented in Example 9-1.

Figure 9-1.  The interaction between functions and resources when using the database

Example 9-1.   PHP file format

  <?php
  $db_host='hostname of database server';
  $db_database='database name';
  $db_username='username';
  $db_password='password';
  ?>

In Example 9-2, we created this file to use a database on the same machine as the web server. We assign it a database name, username, and password.

Example 9-2.  The db_login.php file with values filled in

  <?php
  $db_host='localhost';
  $db_database='test';
  $db_username='test';
  $db_password='yourpass';
  ?>

Figure 9-2 illustrates how you're going to use this file with other PHP files. You're going to continue using the database that you started to set up in Chapter 7.

Example 9-3 is an abbreviated dump of the database created from the mysqldump command. 

 

Figure 9-2.  Reusing the login details in multiple files

Example 9-3.   The SQL to recreate the test objects

--
-- Table structure for table `authors`
--
DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `author_id` int(11) NOT NULL auto_increment,
  `title_id` int(11) NOT NULL default '0',
  `author` varchar(125) default NULL,
  PRIMARY KEY (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `authors`
--
INSERT INTO `authors` VALUES (1,1,'Ellen Siever'),(2,1,'Aaron Weber'),(3,2,
'Arnold Robbins'),(4,2,'Nelson H.F. Beebe');
--
-- Table structure for table `books`
--
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `title_id` int(11) NOT NULL auto_increment,
  `title` varchar(150) default NULL,
  `pages` int(11) default NULL,
  PRIMARY KEY (`title_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `books`
--
INSERT INTO `books` VALUES (1,'Linux in a Nutshell',476),(2,'Classic Shell Scripting',256);
--
-- Table structure for table `purchases`
--
DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
  `id` int(11) NOT NULL auto_increment,
  `user` varchar(10) default NULL,
  `title` varchar(150) default NULL,
  `day` date default NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `purchases`
--
LOCK TABLES `purchases` WRITE;
INSERT INTO `purchases` VALUES (1,'Mdavis','Regular Expression Pocket Reference',
'2005-02-15'),(2,'Mdavis','JavaScript & DHTML Cookbook','2005-02-10');

If you didn't create the tables in the last chapter, the code in Example 9-3 can be saved as backup.sql and run from the command prompt with the following:

  mysql -u username -p password D database_name < backupfile.sql

The database is called test, and it consists of three tables called books, authors, and purchases. Each table has a few sample rows. That's enough to get us started querying from PHP.