FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How to resolve these URIs?





imagefree
Consider an example of a Web Directory that have URIs like these:

/directory/ (directory)
/directory/software/operating-system/ (directory)
/directory/software/operating-system/windows/ (directory)
/directory/software/operating-system/windows/12346_Actual-Contents
/directory/software/operating-system/windows/vista/ (directory)
/directory/software/operating-system/windows/vista/12345_Actual-Contents

These URIs have multiple levels. Each level is separated by a '/'. The script allows multiple levels of categories and subcategories. While writing script, i have no clue how much different levels of categories may exists in a URI (infact i do not put limit here).

Problem arises when i have to resolve these URIs to get actual contents. I have to validate each section of this URI. For this I have to query database separately to validate each part of the URL, and it would be inefficient because the levels may go deeper than 5 in hierarchy.

I want to know a reliable and standardized way of doing this so that it can validate to Unknown levels, faster.

Please give me suggestions. Thanks.
Fire Boar
I take it you're using a RewriteRule to write all of that into a _GET variable? (If not, you probably should.) It should be quite straightforward: use explode('/', $_GET['q']) to split the query string by / and then you can just check each part of the resulting array. What exactly do you mean by "validate" anyway?
imagefree
By validate i mean i want to check each category, sub category, sub sub category (i call all these levels) whether they actually exist or the URL is forged.

Yes i am using Rewrites.

My main question is how to validate these categories in a better way. If actual contents are say 10 level deeper in the URL, it means i have to run 10 queries to check whether the categories/sub categories are right. I want to do it in just 1 query.

My table structure for the categories is

Example URI: /directory/software/operating-system/windows/vista/12345_Actual-Contents

Code:
id | category_name | category_url_name | parent_id
1 | Software | software | 0
2 | Operating System | operating-system | 1
3 | Windows | windows | 2
4 | Vists | vista | 3
jmraker
You could add another field that stores the full path of each one and write a function that recursively builds the string

Code:
id | category_name | category_url_name | parent_id | full_path
1 | Software | software | 0 | /software/
2 | Operating System | operating-system | 1 | /software/operating-system/
3 | Windows | windows | 2 | /software/operating-system/windows/
4 | Vists | vista | 3 | /software/operating-system/windows/vista/
Fire Boar
jmraker's solution is probably best, but it is possible to do without.

Code:
$path = explode('/', $_GET['q']);
while ($row = $pdo->query("SELECT * FROM categories WHERE category_url_name IN ('" . implode("','", $path) . "')"))
{
  // do stuff
}


However, this doesn't work if you have more than one subcategory with the same url in different categories.
sonam
I don't know is this best solution but I will do something like this:

Code:
<?php
$url ="/directory/software/operating-system/windows/vista/12345_Actual-Contents";
$path = explode('/', $url);
foreach($path as $key => $val){
    $num = $key-2; // this minus is independent of your url;
    if($num < 0) {
       continue;
    } else {
       $where .= $val . "=" . $num . ", ";
    }
}
echo $where;
?>


Then you can use $where in your query.

Sonam
jmraker
It could work with multiple parent categories if you seperated them with a letter that isn't allowed in a category name like "~"

Code:
4 | Vista | vista | 3,10 | ~/software/operating-system/windows/vista/~/hardware/networking/vista/~


and used

Code:
$sql = 'SELECT * FROM category WHERE category_url_name="' . mysql_real_escape_string($lastPart) . '" AND full_path LIKE "%~' . mysql_real_escape_string($path) . '~%"';


$lastPart is the last directory in $path.
Fire Boar
sonam wrote:
I don't know is this best solution but I will do something like this:

Code:
<?php
$url ="/directory/software/operating-system/windows/vista/12345_Actual-Contents";
$path = explode('/', $url);
foreach($path as $key => $val){
    $num = $key-2; // this minus is independent of your url;
    if($num < 0) {
       continue;
    } else {
       $where .= $val . "=" . $num . ", ";
    }
}
echo $where;
?>


Then you can use $where in your query.

Sonam


I'm not sure that does what you think it does. The output of that code is:

Code:
"software=0, operating-system=1, windows=2, vista=3, 12345_Actual-Contents=4, "


And I can't imagine that would be useful in an SQL query.


EDIT: How about this?

Code:
<?php
$url = "/directory/software/operating-system/windows/vista/12345_Actual-Contents";
$path = explode('/', $url);
array_shift($path);

$sql   = "SELECT COUNT(1) FROM categories AS c0";
$where = " WHERE c0.category_url_name='" . urlencode(array_shift($path)) . "'";

foreach ($path as $k => $v) {
  $n = $k + 1;
  $sql   .= " INNER JOIN categories AS c$n ON c$k.id = c$n.parent_id";
  $where .= " AND c$n.category_url_name='" . urlencode($v) . "'";
}

return $sql . $where;


It's the most normalized solution posted so far, performing an arbitrary number of joins and checking each one. The result of the query is 0 if the URL is not valid, or 1 if it is. If more than one, there are more than one paths in the database which should never happen if you're sensible.

In this case, the output is:

Code:
SELECT COUNT(1) FROM categories AS c0 INNER JOIN categories AS c1 ON c0.id = c1.parent_id INNER JOIN categories AS c2 ON c1.id = c2.parent_id INNER JOIN categories AS c3 ON c2.id = c3.parent_id INNER JOIN categories AS c4 ON c3.id = c4.parent_id INNER JOIN categories AS c5 ON c4.id = c5.parent_id WHERE c0.category_url_name='directory' AND c1.category_url_name='software' AND c2.category_url_name='operating-system' AND c3.category_url_name='windows' AND c4.category_url_name='vista' AND c5.category_url_name='12345_Actual-Contents'



EDIT 2: Looks like your database table isn't storing the actual page itself. In which case, just use array_pop near the beginning and check for an empty array.
sonam
Quote:
I'm not sure that does what you think it does. The output of that code is:

Code:
"software=0, operating-system=1, windows=2, vista=3, 12345_Actual-Contents=4, "



And I can't imagine that would be useful in an SQL query.


@Fire Boar
I didn't write how to build query but I have imagination how to separate his url in useful peaces.

Sonam
imagefree
jmraker wrote:
You could add another field that stores the full path of each one and write a function that recursively builds the string

Code:
id | category_name | category_url_name | parent_id | full_path
1 | Software | software | 0 | /software/
2 | Operating System | operating-system | 1 | /software/operating-system/
3 | Windows | windows | 2 | /software/operating-system/windows/
4 | Vists | vista | 3 | /software/operating-system/windows/vista/


Thanks jmarker.
This is perfectly what i wanted. But it has just one problem. Validation of URIs is still a problem. If i match the URI against the full_path field, it will just return true or false whereas i want to know at what level error exists so that i could suggest user the correct URL or show the nearest valid page in the hierarchy.

Suppoer the uri is: /directory/software/operating-system/window/vista (missing s at the end of windows)
i want to show user the page: /directory/software/operating-system/ and at the same time show an error that the exect URL was not found.

I have found another solution that too needs another field in table (but so far it too doesnt match the requirement):

Code:
id | category_name | category_url_name | parent_id | level
1 | Software | software | 0 | 1
2 | Operating System | operating-system | 1 | 2
3 | Windows | windows | 2 | 3
4 | Vists | vista | 3 | 4


Now when i get the request for /directory/software/operating-system/windows/vista/12345_Actual-Contents

1. The script will look for 12345 in the contents table and get the immediate parent ID (that would be 4 (vista) in this case.
2. The script will look for the ID 4 in categories table and will get the level 4. This means the contents are 4 level deep in the hierarchy.

For this I have to save different Queries for different levels, in a data file. For example:

SELECT * FROM categories where id = (SELECT parent_id FROM categories WHERE id = ( SELECT ... )))
(each such query for different levels)

Problem is that this is only helpful when i have to find the information about the first Category in the hierarchy (and also it will validate that the whole path was correct, but not useful for many practicle requirements of the script).
Related topics
Now these are easy...
Taiwan- China or U.S.
What Programs should I unintall from this list?
Windows Vista Official Thread
Whats the point of MMORPGs these days?
Anyone heard of these bands?
Federal response to Katrina was faster than Hugo,
Interesting problem
What is MORALITY, the concept? Let’s be philosophers.
The Middle East Conflict
BSOD... Please help
Will it be possible to end Child labours ever?
Do you have any faith that there is a "soul"
I Resolve Nothing!
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.