检查数据库中的表的URL

Question

i am currently using url rewrite via IIS which is working and i am capturing the url with the below variable

$compiled_url= $_GET["params"];

however i need to check whats in that url with whats in the pages table in MySQL. the table looks like the below

tbl_pages
page_id | page_slug | page_parent
--------+-----------+------------
   1    |   posts   | 0
   2    |   daily   | 2

http://www.domain.com/posts/daily

what possible methods are there to check the above domain and passed parameters against the database and make sure they exist in that order so if the url was typed backwards daily/posts it would fail to a 404 as they don't reflect that way in the database

i have started with this method but just as an example my end result i would like to be a class or a neater option

$compiled_url = explode("/",$compiled_url); 
$compiled_parent=0;
foreach($compiled_url as $url_decompiled)
{                   
    $url_results = mysqli_query($webapp_db,"SELECT * FROM tbl_pages WHERE page_slug='" . $url_decompiled . "' AND page_parent ='" . $compiled_parent . "'");
    if(mysqli_num_rows($url_results) > 0)
    {
    echo "page found <br>";
    $result = mysqli_fetch_array($url_results);                 
    $compiled_parent=$result['page_id'];
    }
    else
    {
    echo "page not found <br>"; 
    break;
    }

}

who has done something like this before? what methods are available without using a framework?

Answer 1

You can use something like this (have not tested it), but you get the gist of it...

<?php
$compiled_url = explode("/",$compiled_url);

// using alphabet characters for table aliases
$alphabet = explode("","abcdefghiklmnopqrstuvwxyz");

$sql_query = "SELECT * FROM tbl_pages ".$alphabet[0];

// loop to build all the JOINs 
for($i=0; $i<count($compiled_url); $i++)
{
    $sql_query .= " INNER JOIN tbl_pages ".$alphabet[$i+1]." ON  ".$alphabet[$i+1].".page_id = ".$alphabet[$i].".page_parent";
}


// loop to build all the WHERE filters
$where = array();
for($i=0; $i<count($compiled_url); $i++)
{
    $where[] = $alphabet[$i].".page_slug = '".$compiled_url[$i]."'";
}

$sql_query .= " WHERE ".implode(" AND ",$where);

// if results are found print "page found" else print "page not found"
if(mysqli_num_rows($url_results) > 0)
{
   echo "page found <br>";
   $result = mysqli_fetch_array($url_results);                 
   $compiled_parent=$result['page_id'];
}
else
{
   echo "page not found <br>"; 
   break;
}