Database Driven Site

Status
Not open for further replies.
ArtDeco

ArtDeco

Ex-lurker
Sep 23, 2007
621
6
0
PA USA
www.roadstersandragtops.com
Hello;

I'd like to see a newbie level thread on building the kind of mysql/php driven sites that Eli talks about in his SEO Empire post, so I thought others might like that as well, especially since we have several DBMS experts on this forum.

I'll start with this site I found - Dave Wooding is a guy doing a Camtasa video tutorial on this on his blog. He's building an affiliate web site by loading a vendors RSS feed into Mysql with a searchable display template and documenting it as he goes.

I don't really like video tutorials ( I'm old school, I read books, type stuff in, and watch it crash), but for newbies it may be helpfull. You can check it out at Dave Wooding.

For those who don't know, the php and Mysql manuals are both online at PHP: Documentation, and MySQL :: MySQL 5.0 Reference Manual.

I'd like to hear what kind of success others are having with database sites and so forth too, but I'm really looking for code based examples, tutorials, and such.
I know basic SQl queries, designing and indexing tables, etc. from my MSSQL server days, but the Mysql server on all our shared hosting is a different beast.

What say you?
 


I think every programmers approach is slightly different and i think the key is finding what works for any particular site.

I find building a 3 level heirarchial format works best..
State/city/page
or
toplevel/sublevel/page
etc

but again this does vary and is not always possible...

I use .htaccess to create friendly urls on all db sites is a must

my 2 cents.
 
I'm searching for the same sort of information. I'm studying up on php and mysql right now, but examples and other stuff would really speed things along. I'll keep watching this thread and post anything i can find of value!
 
Now if you'd just posted some good links / tutorials, that would be a nice post.

Here is what I have found so far on URL rewriting:
mod_rewrite, a beginner's guide (with examples)
Easy Mod Rewrite : mod_rewrite tutorial
Mod rewrite syntax Tutorial - Mod Rewrite Regular Expressions
.htaccess tricks and tips.. part two: url rewriting with mod rewrite.

Go get em, guys.

::emp::


ozonew4m said:
I think every programmers approach is slightly different and i think the key is finding what works for any particular site.

I find building a 3 level heirarchial format works best..
State/city/page
or
toplevel/sublevel/page
etc

but again this does vary and is not always possible...

I use .htaccess to create friendly urls on all db sites is a must

my 2 cents.
Click to expand...
 
I bought a copy of the Sitepoint guide Emp mentioned - Build your own database driven websitw using php & mysql - a while back and thought it was well worth the money. So did one of my programming buddies who borrowed it and never returned it.
+ rep
 
Good thread, gonna read some of these links after I finish with the rest of the new posts here at WF. Especially .htaccess redirects.. that shit is confusing like Sendmail cf files.
 
nAaah, .htaccess is black magic.
The western kind, rituals, pentagrams, drinking blood from holy goblets, demons, succubi, etc... straight out of the bible.

Wolrd of difference, but easy to confuse with voodoo by the beginner.

::emp::
 
ok here goes

emp said:
Now if you'd just posted some good links / tutorials, that would be a nice post.
Click to expand...

yeah sorry about that... im no expert at htaccess and i was in kind of a rush..

let me try and explain how i do things..

heres one of my standard htaccess files..

Code: 
<Files "config.php">
Order Allow,Deny
Deny from All
</Files>
<Files "common.php">
Order Allow,Deny
Deny from All
</Files>
 
ErrorDocument 400 /index.php
ErrorDocument 401 /index.php
ErrorDocument 403 /index.php
ErrorDocument 404 /index.php
ErrorDocument 500 /index.php
RewriteEngine On
rewriteCond %{REQUEST_URI} ^/[^\.]+[^/]$ 
rewriteRule ^(.*)$ [URL]http://%{HTTP_HOST}/$1/[/URL] [R=301,L]
RewriteCond %{HTTP_HOST} . 
RewriteCond %{HTTP_HOST} !^www\.somedomain\.com 
RewriteRule (.*) [url=http://www.somedomain.com/]domains[/url] [R=301,L]
RewriteBase /
 
RewriteRule ^(categorya+)/([^/]+)/([^/]+)/([^/]+)/?$ ./index.php?var1=$1&var2=$2&var3=$3 [L]

Now the following explanation may be completely wrong but this is how i grasp it... hopefully you will too..

Code: 
<Files "config.php">
Order Allow,Deny
Deny from All
</Files>
<Files "common.php">
Order Allow,Deny
Deny from All
</Files>

blocks direct access to these files so that they cant be downloaded and snooped

Code: 
ErrorDocument 400 /index.php
ErrorDocument 401 /index.php
ErrorDocument 403 /index.php
ErrorDocument 404 /index.php
ErrorDocument 500 /index.php

redirects all errors to the index page of your domain... i cant be bothered with custom error pages so i just use this..

Code: 
RewriteEngine On

does exactly what it says on the tin.

Code: 
RewriteEngine On
rewriteCond %{REQUEST_URI} ^/[^\.]+[^/]$ 
rewriteRule ^(.*)$ [URL]http://%{HTTP_HOST}/$1/[/URL] [R=301,L]
RewriteCond %{HTTP_HOST} . 
RewriteCond %{HTTP_HOST} !^www\.somedomain\.com 
RewriteRule (.*) [URL="http://www.somedomain.com/"]domains[/URL] [R=301,L]
RewriteBase /

What this does is redirects any request without www. to yourdomain.com

Code: 
RewriteBase /

what this does is determine where your rewrite rule will start and trigger.. the above means after the first slash

Code: 
RewriteRule ^(categorya+)/([^/]+)/([^/]+)/([^/]+)/?$ ./index.php?var1=$1&var2=$2&var3=$3 [L]

this is where the fun starts..

lets break it down (not dancing)

Code: 
RewriteRule

tells the computer that this is a rewrite rule..

Code: 
^(categorya+)

this is the trigger after the first slash and before the second slash in the url so for example a call for
http://www.somedomain.com/categorya/will trigger this rule but
http://www.somedomain.com/categoryb/
wont


then is your first slash
Code: 
/

next are 3 additonal triggers seperated by slashes that can be used as variables to pass to my redirect page

Code: 
([^/]+)/([^/]+)/([^/]+)/

Code: 
([^/]+)
matches anything
Code: 
/
slash
Code: 
([^/]+)
matches anything
Code: 
/
slash
Code: 
([^/]+)
matches anything
Code: 
/
slash

next is
Code: 
?$
to tell the rewrite rule is ended...

Code: 
./index.php?var1=$1&var2=$2&var3=$3

after is your redirect url where you want people to be sent also containing the required variables from the matchall parts.. whatever is between your slashes will become your variables

so if i was to put a link in my site pointing to

anydomain.com/bill/bob/sam/

it would redirect to

Code: 
[URL="http://www.anydomain.com/index.php?var1=bill&var2=bob&var3=sam"]www.[URL="http://www.anydomain.com/index.php?var1=bill&var2=bob&var"]anydomain.com/index.php?var1=bill&var2=bob&var[/URL][URL="http://www.anydomain.com/index.php?var1=bill&var2=bob&var3=sam"]3=sam[/URL] [/URL]


i have no idea what the
Code: 
[L]
is for..

ok ... there ya go... ive tried my best to explain htaccess to you as i see it but have probably just confused you even more... hopefully an expert will come along and prove me completely wrong..

hope this helps somebody
 
Last edited:
'RewriteRule ^(categorya+)/([^/]+)/([^/]+)/([^/]+)/?$ ./index.php?var1=$1&var2=$2&var3=$3 [L]'

Yep total voodoo. Thank you for posting the code and the explanation. I am going through it line by line right now.

Dave Wooding put a beta copy ( maybe an alpha) of the site DBMS site on my server at buyplants.patiopondgarden.com. Feel free to crash it if you wish, there isn't anything important on that server.

BTW, Dave is planning on giving away a copy of that affiliate site as some kind of link love contest if you are into that type of thing.

See You tomorrow,
 
ozone's htaccess is some good stuff.

as far as db-driven sites go, i agree that it's different for everyone. my personal approach (which is fairly common for people coding by hand) is to create an index.php template and use includes that are all template pages drawing the content from a mysql table. below would be an example.

PHP: 
 <?php  

include('header.php');

include('nav.php');

if($page == "about") {
  include('about.php');
}
elseif($page == "contact") {
  include('contact.php');
}

// etc etc 

include('footer.php');

?>

so you might have a mysql table with the following schema:
table name: Content
field 1: contentID
field 2: contentPageTitle
field 3: contentPageBody

or something like that. and your about.php file would have the script for drawing the info from that table.
 
Designing a database site for affiliate products

OK, If I understand it correctly, the trick is to make sure that your pages at each level of the hierarchy are generated using the same formatting and naming methods, and to use as few link pages as possible – not more than three clicks from home page to purchase page. For example, if you need a three-level set of pages, your top level file leads only to a list of categories as the index page, linking to a set of second tier pages of items within a category. In the second tier page, you will link to third tier detail pages providing the actual product info and/or the links to your affiliate purchase page.
So for a fishing site on the top level you will categories of articles ( fishing equipment, fish recipes, fishing trips, etc.) that link to pages of category specific links. If the user picks “fishing trips” the second page might be by state or region ( fishing guides in New York, chartered fishing trips on Lake Erie, guided fly fishing trips in Montana). If they pick fishing equipment, the links would probably be by product type (heavy rods, medium rods, spinning reels, waders, vests, lures, electronics), etc.. For fish recipes, you link to baked fish, breaded and pan fried, soups and chowders or whatever you have.
According to the “Revenge of the MiniNet” ebook that I recently read, the third tier pages link directly to an affiliate sales page, and EITHER back to the second tier page “Medium weight spinning rods” OR to another third tier page - from “Shimano 65607 Medium Spinning Rod” to “Shakespeare 70078 medium Spinning Rod”, but not both, for link weight and page rank reasons.


With a database, I think this would work best with two tables, 'Categories' and 'Items' linked on a category ID field which is the primary key of the 'Categories' table and a foreign key for 'Items' table. This will allow you to Select * from Items where Items -> CategoryID = Category-> CategoryID for example.
But if the number of items is small (hundreds or even a few thousand, and not a lot of inserts and deletes) just one table of items with a primary key of ItemID and an index on a field called CategoryID would work just as well, and would be a lot simpler to keep updated.
 
Status
Not open for further replies.
Top Bottom