homepage Welcome to WebmasterWorld Guest from 54.196.62.132
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Querying MYSQL with keywords
by storing url string in database
master w bates III




msg:3643488
 2:40 am on May 7, 2008 (gmt 0)

Hello again Web Master World!

So far in my adventures with PHP/MYSQL i have been structuring sites the following way. I have recently come across the subjects of more in depth normalization and creating indexes. It seems my current approach leaves a bit to be desired in regard to these subjects. I am wondering if anyone might have a better technique to handling this functionality.

On a massive posts table, wouldn't it not be better to create sort posts by an INT id index instead of a possible 200 character string? How much speed could be gained from using a iNT id over a url keyword string?


URL to hit

example.com/blog/title-of-my-post


From the .htaccess file

RewriteEngine on
RewriteRule ^([a-z0-9\-]+)/([a-z0-9\-]+)$ index.php?section=$1&post=$2

General PHP and Mysql

$page_found = false;
$url = $_GET['post'];
$url = addslashes(htmlspecialchars($url));
$query = "
SELECT *
FROM posts
WHERE url = '$url'
AND section = '$section'
";
$result = $mysqli->query($query) or die ("Error in query: $query. ".$mysqli->error);
$validpage = $result->num_rows;
if ($validpage == 0) {
$app404 = true;
include_once 'errors/404.php';
} else {
$title = "";
while ($row = $result->fetch_array()) {
$id = $row['id'];
$url = $row['url'];
$title = stripslashes($row['title']);
$text = stripslashes($row['text']);
}
$page_found = true;
if ($title == ""){$page_found = false;}
$result->close();
}
if ($page_found == true)
echo the post into the template;
else
echo 404 header and template

As always thanks for any help. I look forward to seeing others approaches to this matter.

[edited by: master_w_bates_III at 2:48 am (utc) on May 7, 2008]

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved