How to write SEO URLs for MySQL pages

Hello,

I have a database set up like this:

+-------------+----------------------+
| chips        | types                 |
+-------------+----------------------+
| $0.50 Chips | Casino               |
| $0.50 Chips | Poker                |
| $2.50 Chips | Casino               |
| $2.50 Chips | Poker                |
| $20 Chips   | Casino               |
| $20 Chips   | Poker                |
| $100 Chips  | Casino               |
| $100 Chips  | Poker                |
+-------------+----------------------+

PHP:

I am using a "WHILE" loop to list off each item into text and a link URL:

<a href='http://www.mysite.com/". $row['chips']."/". $row['types']."'>

PHP:

I get something like this:
http://www.mysite.com/$0.50 Chips/Casino/
http://www.mysite.com/$0.50 Chips/Poker/
http://www.mysite.com/$2.50 Chips/Casino/
http://www.mysite.com/$2.50 Chips/Poker/ .....

How can I make SEO friendly URLs by getting rid of the dollar signs, turning spaces into "-" ? The entire site is based on MySQL data but I would like to make it look organized like a directory using the "/" symbols. What should I do?

Thanks
Best

 

Awesome thanks!

 

Ah wait a second, I think I have some sort of problem. When I click on the link, it goes nowhere.

I created the nice looking links and the directory structure like: http://www.mysite.com/chips/poker/
Now I do not know how to create a page on the other end of that link out of the database using these SEO links.

I know I can create something like: http://www.mysite.com/product-page.php?chips=casino but how can I make the SEO links while keeping the identifiers needed in order to create these dynamic pages?

Thanks
Best

 

You must go through your .htaccess file for it!
Post that here!

BTW: the $2.50 seems to be a variable!
Better you can rewrite the first link like http://www.mysite.com/0.50-Chips/Casino/
Else you need to modify your mysql DB and put the row name as Chips $0.50

 

Thanks for the responses roopajyothi and sarahk,

I'm actually sort of a noob for PHP and MySQL stuff right now. I have an id column but I haven't been using it. Basically, I have all my data in Excel CSV all nice and organized. I would like to be able to add a new product in the middle of the spreadsheet, but doing this would cause all my products to shift down and the IDs would be connected to different problems and mess up my pages (I would have to 301 redirect).

I reverted the original SEO rewrites and went back to dynamic so I could create pages for each individual thing. Every page is now available but my parent categories seem confusing and so is my URL structure. Maybe I could PM the URL to my site and you could take a look at it? The whole thing seems almost done except for cleaning up my URL paths.

Thanks

Additional Information:

Here is my .htaccess code:

Options +FollowSymLinks
RewriteEngine On
RewriteRule ^/(.*)/(.*)$ type.php?denomination=$1&type=$2 [L]

PHP:

Here is one of my dynamic URLs (yes, there are empty spaces between the words):

http://www.mysite.com/type.php?denomination=Small Cents&type=Indian Head Cent

PHP:

Here is what I am trying to turn it into:

http://www.mysite.com/small-cents/indian-head-cent/

PHP:

The .htaccess code is not doing it though. I'm not sure what's going on.

 

I also have a deep URL like this:

http://www.mysite.com/coins.php?denomination=Small Cents&type=Indian Head Cent&year=1878&mint=P&misc=

PHP:

Which I would like to look like this:

http://www.mysite.com/small-cents/indian-head-cent/1878-P/

PHP:

Notice in my dynamic URL that "misc=" equals nothing. In my database, some of the fields are empty and others have information that needs to be displayed.

For instance:

http://www.mysite.com/coins.php?denomination=Small Cents&type=Indian Head Cent&year=1864&mint=P&misc=Copper-Nickel

PHP:

Should become:

http://www.mysite.com/small-cents/indian-head-cent/1878-P/copper-nickel/

PHP:

What would be a good rewrite code to do this?

Thanks

 

For this

http://www.mysite.com/coins.php?denomination=Small Cents&type=Indian Head Cent&year=1878&mint=P&misc=

Code (markup):

Or

http://www.mysite.com/coins.php?denomination=Small Cents&type=Indian Head Cent&year=1864&mint=P&misc=Copper-Nickel

Code (markup):

if you need like this

http://www.mysite.com/small-cents/indian-head-cent/1878-P/copper-nickel/

Code (markup):

Then the .htaccess goes like

RewriteEngine On
RewriteRule ^([^/]*)/([^/]*)/([^/]*)-([^/]*)/([^/]*)$ /coins.php?denomination=$1&type=$2&year=$3&mint=$4&misc=$5 [L]

Code (markup):

For this

http://www.mysite.com/type.php?denomination=Small Cents&type=Indian Head Cent

Code (markup):

If you need like this

http://www.mysite.com/small cents/indian head cent/

Code (markup):

RewriteEngine On
RewriteRule ^([^/]*)/([^/]*)$ /type.php?denomination=$1&type=$2 [L]

Code (markup):

if you need to do like this http://www.mysite.com/small-cents/indian-head-cent/

Then the Type.php must be modified!
Thanks

 

Thanks for the codes roopajyothi,

Unfortunately, when I put them into my .htaccess file and upload it, nothing happens to the URLs, it's as if nothing happens.

Here is what I have in my file:

Options +FollowSymLinks
RewriteEngine On
RewriteRule ^([^/]*)/([^/]*)$ /type.php?denomination=$1&type=$2 [L]
RewriteRule ^([^/]*)/([^/]*)/([^/]*)-([^/]*)/([^/]*)$ /coins.php?denomination=$1&type=$2&year=$3&mint=$4&misc=$5 [L]

PHP:

Is there something else i need to do to get this to work? Also, my websites are hosted at Dreamhost and they told me that mod_rewrite should be working fine.

Thanks

 

UPDATE

I went over to the Dreamhost forum and found out that the code should be:

RewriteRule ^([^/]*)/([^/]*)/$ /type.php?denomination=$1&type=$2 [L]
RewriteRule ^([^/]*)/([^/]*)/([^/]*)-([^/]*)/([^/]*)/$ /coins.php?denomination=$1&type=$2&year=$3&mint=$4&misc=$5 [L]

PHP:

The only difference was adding a slash after the last "atom" before the dollar sign.

I can manually type in the small urls and the pages will appear but there are a few problems. First, these two URLs will work but they go to different content:

http://www.mysite.com/small cents/indian head cent/
http://www.mysite.com/small-cents/indian-head-cent/

PHP:

The first URL goes to the correct page but I would like it to look like the second URL.

These URLs also only work if I manually type them in. How can I get these URLs to appear for links inside the source code?

Thanks

UPDATE

HOLY HELL! I was looking at this entirely the wrong way, literally COMPLETELY backwards.

So what I am really doing is converting category titles, coin titles and stuff into "friendly URL sections" without all the spaces and capital letters, then print these into the site. THEN .htaccess takes these sections and plugs them into my ugly dynamic URL structure in my .htaccess file, redirecting me to the correct page sort of?

I was creating ugly dynamic links first and thinking that .htaccess would then re-write these into the SEO friendly URLs in source code and everything, which the exact opposite of I should be doing it, right?

I will work on this idea for awhile then, see how this works and then come back later.

 

I just thought I would let everyone know that I almost have everything working perfectly except for a minor issue. Here is the issue:

I have two URLs like this on my site:

<a href='http://www.mysite.com/coins/half-cents/liberty-cap/1794-P/high-relief/'>1794 P</a> - (high relief)
<a href='http://www.mysite.com/coins/half-cents/liberty-cap/1794-P/'>1794 P</a> 

PHP:

The top URL has an additional field called "high-relief", which is part of the (misc) variable of my database column. The bottom URL goes to a page that doesn't have this specific "misc" variation.

My full URL structure is set up something like this with variables being plugged in from the database:

http://www.mysite.com/coins/(denomination)/(coin type)/(year)-(mintmark)/(misc)/               -----> with misc field or "high relief" being used
http://www.mysite.com/coins/(denomination)/(coin type)/(year)-(mintmark)/               -----> misc field is empty or NULL in database - no misc field is needed here

PHP:

I tried to fix this by using the re-write code in my .htaccess file:

RewriteRule ^coins/([^/]*)/([^/]*)/([^/]*)-([^/]*)/([^/]*)/$ /coins.php?demurl=$1&typeurl=$2&year=$3&mint=$4&miscurl=$5 [L]
RewriteRule ^coins/([^/]*)/([^/]*)/([^/]*)-([^/]*)/$ /coins.php?demurl=$1&typeurl=$2&year=$3&mint=$4 [L] 

PHP:

This doesn't seem to be working. If I click on that bottom link, it goes to the same page as the top link. Some of my "misc" (or "miscurl" to be exact using the rewrite variable) fields are going to be empty. How can I fix this?

Thanks

 

Oops so only i did not added a slash there (after dollar sign)
If some times Misc is there the the url will look like

http://www.mysite.com/coins/(denomination)/(coin type)/(year)-(mintmark)/(misc)
else
http://www.mysite.com/coins/(denomination)/(coin type)/(year)-(mintmark)/

Code (markup):

Its done with a single line htaccess instead of two lines of messing htaccess problem )
If you need you can hire me!

 

You could also create an URL mapping table.

 

Issue Resolved????