2

I've been struggling with 2 problems when creating a CMS where I want to implement friendly urls, using PHP5 and MySQL 5.

The basic database structure is as follows:

-------------------------------
| ID    | PageName | ParentID |
-------------------------------
| 1 | Page1        | 0        |
| 2 | Page2        | 0        |
| 3 | SubPage1     | 1        |
| 4 | SubSubPage1  | 3        |
-------------------------------

I then want to generate urls like the following:

Page with ID=1 will have URL: /Page1
Page with ID=3 will have URL: /Page1/SubPage1
Page with ID=4 will have URL: /Page1/SubPage1/SubSubPage1

When visiting the page I have a .htaccess rule to dispatch the url to a querystring like: ?page=/Page1/SubPage1 etc

How can I query the database to compare the URL with the database entries?

Fredrik
  • 93
  • 1
  • 6
  • Maybe this link will help for you: [enter link description here][1] [1]: http://stackoverflow.com/questions/20422497/mysql-modified-preorder-tree-to-create-url –  May 07 '15 at 01:40

2 Answers2

2

Say your route is /Page1/SubPage1/SubSubPage1, your query would be:

SELECT p1.id
    FROM pages AS p1, pages AS p2, pages AS p3
    WHERE p1.PageName = 'SubSubPage1' AND p1.parentID = p2.ID AND
          p2.PageName = 'SubPage1' AND p2.parentID = p3.ID AND
          p3.PageName = 'Page1'
nobody
  • 10,599
  • 4
  • 26
  • 43
0

Do you want to compare the whole route or just select data for the actual page?
If you only want to select data for the actual page (last PageName in your URL) you simply use explode on your $_GET['page'] variable (use / as delimiter) and use the last array value.
Next you simply insert this value into your sql query (f.e. SELECT * FROM pages WHERE PageName = 'yourLastValue')

shaggy
  • 1,708
  • 2
  • 15
  • 17
  • I want to compare the whole route, since if I only compare the last value (which is my solution at the moment) i can type whichever url as long as the last part of the URL is in the DB. – Fredrik Aug 07 '11 at 09:51