blackhatzen

A BlackHatWorld user made a thread yesterday asking how to dynamically generate a large number of “pages” from a database that could potentially be indexed by a search engine.

Basically I have a DB which contains huge number of names and I would like to create a website where all those names will be made into pages and the page title should be the DB value or etc.

Basically a main website and from there You can view a list or etc and see all those names. And all pages have the same content basically but with some variables (links). What I need is that those pages are static and seam to be useful for google etc.

I hope u understand what I need? Also when I have 100K different HTML files or maybe Wordpress posts, then wont my host f**k me in the a**?

Thanks!

Of course, the solution to his question is much more simple than creating a large number of HTML files. I spent a few minutes this afternoon making some example code to show him how something along these lines would work using MySQL, PHP, and the Apache module, mod_rewrite. Big thanks go out to taky for creating his mini PHP MySQL DB class which I’ve modified and used on multiple projects recently.

Disclaimer: This code is not secure and is not meant to be run on a production server. It does the job, but it could be much more secure and efficient. It just shown here only as an example.

We’ll be working with four files:

• info.sql – A MySQL export of the database we’re querying.
• .htaccess – Dictates the RewriteRule’s so that our user is forwarded correctly.
• query.php – Parses the GET arguments and dictates our SQL queries and displays appropriate information.
• db.php – A slightly modified version of taky’s PHP MySQL DB class.

To begin, you’ll want to upload all of the files with the exception of info.sql to a directory on a web server running Apache with at MySQL, PHP, and mod_rewrite installed. You’d want to create a MySQL database, associate a user with it, and import info.sql to generate a table in that DB called info.

info.sql:

-- phpMyAdmin SQL Dump
 
51&q=SET&lr=lang_en">SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
-- --------------------------------------------------------
 
--
-- Table structure for table `info`
--
 
51&q=CREATE&lr=lang_en">CREATE 51&q=TABLE&lr=lang_en">TABLE 51&q=IF%20NOT%20EXISTS&lr=lang_en">IF 5.1/en/non-typed-operators.html">NOT EXISTS `info` (
  `firstname` 51&q=VARCHAR&lr=lang_en">varchar(10) 5.1/en/non-typed-operators.html">NOT 51&q=NULL&lr=lang_en">NULL,
  `content` 51&q=VARCHAR&lr=lang_en">varchar(15) 5.1/en/non-typed-operators.html">NOT 51&q=NULL&lr=lang_en">NULL
) 51&q=ENGINE&lr=lang_en">ENGINE=MyISAM 51&q=DEFAULT&lr=lang_en">DEFAULT 51&q=CHARSET&lr=lang_en">CHARSET=latin1;
 
--
-- Dumping data for table `info`
--
 
51&q=INSERT&lr=lang_en">INSERT 51&q=INTO&lr=lang_en">INTO `info` (`firstname`, `content`) 51&q=VALUES&lr=lang_en">VALUES
('chuck', 'Chuck''s text.'),
('chris', 'Chris'' text.'),
('ben', 'Ben''s text.'),
('andy', 'Andy''s text.'),
('alan', 'Alan''s text.');

.htaccess:

Options +FollowSymlinks
RewriteEngine On
 
RewriteRule ^$ query.php [L,NC]
RewriteRule ^([a-zA-z]+)/$ query.php?first=$1 [L,NC]
RewriteRule ^([a-zA-z]+)/([a-zA-z]+)/$ query.php?first=$1&full=$2 [L,NC]

db.php:

< ?php
/**
 * taky php/mysql database class
 * designed to behave like codeigniter
 *
 * taky@takyhat.com
 *
 * Minor edits by osh/bhz
 * http://blackhatzen.com - zen@blackhatzen.com
 */
 
class db
{
	function __construct($host,$user,$pw,$db)
	{
		$conn = mysql_connect($host, $user, $pw) or die (mysql_error());
		mysql_select_db($db);
	}
 
	function fetch($table,$where=null,$like=null,$orderbyrand=null,$orderbyid=null,$limit=null)
	{
		$q = "SELECT * FROM ".$table;
 
		/**
		 * check for extras and modify the query
		 */
		if($where!=null)
		{
			$parts=explode('=',$where);
			$parts[1]='"'.$parts[1].'"';
 
			$where=$parts[0].'='.$parts[1];
			$q.=" WHERE ".$where;
		}
 
		if($like!=null)
		{
			$parts=explode('LIKE',$like);
			$parts[1]='"'.$parts[1].'"';
 
			$like=$parts[0].'LIKE'.$parts[1];
			$q.=" WHERE ".$like;
		}
 
		if($orderbyrand!=null)
		{
			$q.=" ORDER BY firstname";
		}
 
		if($orderbyid!=null)
		{
			$q.=" ORDER BY id desc";
		}
 
		if($limit!=null)
		{
			$q.=" LIMIT ".$limit;
		}
 
		/**
		 * return the array
		 */
		$result = mysql_query($q) or die(mysql_error());
 
		$return=array();
 
		while($row = mysql_fetch_array($result))
		{
			array_push($return,$row);
		}
 
		return($return);
	}
 
	function __destruct()
	{
		mysql_close();
	}
}
?>

query.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

< ?php
	include('db.php');
	$db=new db('localhost','db_user','db_pass','db_name');
	$user['first']=$_GET['first'];
	$user['full']=$_GET['full'];
 
	if(is_null($user['first']) == '1' && is_null($user['full']) == '1')
		{	
			echo '<a href="a/">a<br />';
			echo '<a href="b/">b</a><br />';
			echo '<a href="c/">c</a><br />';
		} elseif (isset($user['first']) && is_null($user['full']) == '1')
		{
			$rows=$db->fetch('info','','firstname LIKE'.$user['first'].'%');
			foreach($rows as $row)
				{
					echo '<a href="../'.$user['first'].'/' . $row['firstname'] . '/">' . $row['firstname'] . '</a><br />';
				}
		} elseif(isset($user['full'])){ 
		$rows=$db->fetch('info','firstname='.$user['full']);
		foreach($rows as $row)
			{
				echo($row['content']);
			}
		}
?>

The only changes you’ll have to make to get this working will involve changing line 3 of query.php:

$db=new db('localhost','db_user','db_pass','db_name');

You should replace localhost, db_user, db_pass, and db_name with your hostname, MySQL username, MySQL username’s password, and your MySQL DB’s name, respectively.

In the simplest terms, what the PHP code and RewriteRule’s do is grab strings passed along with the URL and use them to seed a MySQL query. So, when the user goes to http://www.domain.com/a/ they are forwarded to http://www.domain.com/query.php?first=a which in turn perform a MySQL query for ‘a%’ on the database table ‘info’ which results in the two records (Andy and Alan) to be displayed as links. When clicked, these links, formatted like http://www.domain.com/a/alan/, direct the user to http://www.domain.com/query.php?first=a&full=alan which displays the contents of the ‘content’ field for the ‘alan’ record on the ‘info’ table.

I just bought and started playing with Camtasia on a virtual machine install of XP, so here is a totally unnecessary and short video I made showing browser behavior:

Post a comment if you have any questions!