1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:03 pm Apr 28, 2026

Forum Moderators: coopster

Message Too Old, No Replies

Creating Real PHP from a database

static pages, php, mysql

         

dmike88

8:27 am on May 27, 2010 (gmt 0)

10+ Year Member



Description of issue:
I'm creating a website that going to have a lot of pages, let's say 100,000 for simplicity. All of these pages are going to be mostly the same, save for the name of the state in the title, the name of the city several times in the content, and 51 different versions of content. I provide an example of what a minimal page would look like below. The issue is that I do not want to create 100,000 php files by hand. I want these php pages to be created on the sever automatically, from an sql database. The database will contain a list of all 50 states, 25 select cities from each state, and 51 content templates. This way the only things I will need to enter into the database is 1250 cities, 50 states, and 51 content templates (as opposed to creating 100,000 different files by hand).

Simple example of a content template:
<? $city="blahcity"; ?>
<? $state="blahstate"; ?>
<html>
<head>
<meta name="title" content="blah blah <? echo $city; ?> <? echo $state; ?> blah blah" />
<head>
<body>
<p>blah blah blah <? echo $city; ?> blah blah blah <? echo $state; ?></p>
</body>
</html

51 different content templates similar to the above example should be populated into automatically created files for each of the 1250 cities (50 states times 25 cities per state).

I'm not very much familiar with php and sql, but I'm a quick learner and can make small edits to php/sql scripts.

I would really appreciate some help on this topic.

Thanks!

janharders

10:55 am on May 27, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Why would you even want to create all those php-files?
With mod_rewrite you could route all requests for content-pages to one php-script that than evaluates the requested page, gets the content from the database and outputs it according to your needs.

rocknbil

8:05 pm on May 27, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Welcome aboard dmike88, yeah, that would be the relatively insane way to do it. :-) A simple example:

Database table
id|city|state|description

Let's display all states. Make your database connection, then 



<?php 
$list=null; 
$query = "select id,state from table order by state asc"; 
$result=mysql_query($query); 
while ($row=mysql_fetch_array($result)) { 
 $list .= "<li><a href=\"details.php?st=\"" . $row['id'] . "\">" . $row['state'] . "</a></li>\n"; 
} 
echo "<html><head><title>State List</title></head> 
<body><h1>State List</h1>"; 
if ($list) { echo "<ul>$list</ul>"; } 
else { echo "<p>No states to display</p>"; } 
echo "</body></html>"; 
?>


And in "details.php" 


<?php 
if (isset($_GET['st']) and is_numeric($_GET['st']) and ($_GET['st']>0)) { 
 $query = "select city,state,description from table where id=" . $_GET['st']; 
 $result=mysql_query($query); 
 if ($row=mysql_fetch_array($result)) { 
 $state=$row['state']; 
 $city=$row['city']; 
 $descr=$row['description']; 
 } 
 else { $state=$city=null;} 
 if ($city and $state) { 
 echo "<html><head><title>$city, $state</title></head> 
 <body> 
 <h1>$city, $state</h1><p>$descr</p> 
 </body> 
 </html> 
 "; 
 } 
 else { 
 echo "<html><head><title>No Entry Found</title></head> 
 <body> 
 <h1>No Entry Found</h1><p>Sorry, no entry was found. <a href="select-state.php">try again</a>.</p> 
 </body> 
 </html> 
 "; 
 } 
} 
else { header("Location:select-state.php"); } 
?>


May contain errors, but you can see the logic . . .

g1smd

11:01 pm on May 27, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month



The 'Not Found' logic will also need to send the HTTP 404 header before any of the error page message content is sent.

rocknbil

3:38 am on May 28, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Yes . . . but . . . that's not really a 404. :-)

dmike88

9:43 am on May 28, 2010 (gmt 0)

10+ Year Member



Thank you for all of your replies and the welcome to the site!

The reason I was thinking of generating actual copies of those pages was from the belief that static pages are preferred by search engines.

Essentially, I want the following files names to be generated (only a few examples):

www.domain.com/criminal_law/california.php
www.domain.com/criminal_law/arson/california.php
www.domain.com/criminal_law/arson/california/los-angeles.php
www.domain.com/criminal_law/arson/california/san-jose.php
www.domain.com/criminal_law/arson/california/san-francisco.php
www.domain.com/criminal_law/kidnapping/california.php
www.domain.com/criminal_law/kidnapping/california/los-angeles.php
www.domain.com/criminal_law/kidnapping/california/san-jose.php
www.domain.com/criminal_law/kidnapping/california/san-francisco.php
etc...

rocknbil, if you believe that generating static pages from a database is unnecessary for what I'm trying to accomplish, could you please elaborate on your solution? I'm probably php savvy enough to implement, but not to code it from scratch.

Thanks again!

janharders

11:12 am on May 28, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



you can use rocknbil's script and add mod_rewrite, so the urls will look exactly how you want them to look without requiring actual files on your server.
ask your favorite search engine for mod_rewrite (it's apache-specific, if you don't use apache, there's probably something similar) to find out more about how it works.

Readie

1:30 pm on May 28, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



rocknbil, if you believe that generating static pages from a database is unnecessary for what I'm trying to accomplish, could you please elaborate on your solution? I'm probably php savvy enough to implement, but not to code it from scratch.

As mentioned above, you can use mod rewrite to modify the URLs.

What mod rewrite does is redirect a request to somewhere else, without telling the visitor that it has done so, for example:

Visitor goes to http://www.example.com/foo/3

Mod rewrite actually sends them to http://www.example.com/some_php_script.php?id=3

So, you can then use $_GET as the basis for your query: 

$sql = 'SELECT columns FROM table WHERE id = "' . mysql_real_escape_string($_GET['id']) . '"';

Matthew1980

2:06 pm on May 28, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Hi there,

Just to add to readie's post, if you are going to mod_rewrite/.htaccess path - dont forget that it is how the link is constructed in the first place:-

this:-

<a href="index.php?id=3" >My link</a>

is the same as:-

<a href="yoursite/3/index.html" >My link</a>

Obviously depending on how you have structured your rewrite rule in the .htaccess file..

So the idea is to make it look like a directory structure whereas its just the way as the server has interpreted the rewrite rule and displayed the data according to the info from the link. Pretty cool when you get your head around it ;)

And IMO good for security and SEO friendliness

Cheers,
MRb

rocknbil

7:54 pm on May 28, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



rocknbil, if you believe that generating static pages from a database is unnecessary for what I'm trying to accomplish, could you please elaborate on your solution?


Well, I won't code it for you, this is how I make a living, but I'll give you some ideas. :-) Here's your directive:

the belief that static pages are preferred by search engines.


Search engines "don't care." Really, they don't. It's long query strings, **especially** ones with unique session ID's in them, that are going to hurt you. (In truth, query strings index just fine, there are other reasons you don't want to use them - like being easy to remember, and keyword rich.)

Your statement is a bit contradictory though, although it's a minor point - a file ending with .php inherently implies there's something dynamic going on there. But on to the solution: why use files or extensions at all?

The second point I'd like to raise before you go down this path is that dashes will always "win out" over underscores. I have a site that uses underscores, it does fine, but I've seen it proven again and again by masters here, some who have even posted results of tests between the two, that dashes will index better. Second, there's a usability consideration, the underscore gets lost in a properly underlined link.

So putting this all together, start with something more along these lines:

www.domain.com/criminal-law/california
www.domain.com/criminal-law/arson/california
www.domain.com/criminal-law/arson/california/los-angeles
www.domain.com/criminal-law/arson/california/san-jose

In .htaccess, you can do "lots" of things. You can have specific rewrite rules for categories, or just take the entire request and pass it to a script. I'll give you one simple example, that will **only** work for "criminal-law"

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^criminal\-law\/[a-z\d\/\-]+$ criminal-law.php [NC,L]
</IfModule>

Then in your script you would have a function that splits up the incoming request_uri on the slashes, determining category and article id. Another approach is to send that as a query string. Here we wrap it in (), which stores it in $1

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule ^criminal\-law\/([a-z\d\/\-]+)$ criminal-law.php?request=$1 [NC,L]
</IfModule>

then you can take $_get['request'] and split it up on the slashes to look up your data.

Some notes about the unfamiliar above: the regular expression

[a-z\d\/\-]+

means one or more of any letter a-z, a number, or a -, and the NC flag makes it case-insensitive. Thus you could have a file /criminal-law/general.html and it would not be rewritten to your script because of the dot in the .html.

When dealing with mod rewrite, don't do "if !d or !f" (if not a directory or a file) as this searches the entire file system and is a bit too intensive. Try to be specific in your rules.

dmike88

10:32 pm on Jun 1, 2010 (gmt 0)

10+ Year Member



Rocknbil, can you check you PM?
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / PHP Server Side Scripting 2:03 pm Apr 28, 2026