MySQL exact match (with PHP script)

Got the following php script to extract out 'actualtag' from URL input as follows:

http://www.website.dev/tag/actualtag

<?php $url = $HTTP_SERVER_VARS["REQUEST_URI"]; list($trash, $directory, $tag) = split('[/]', $url);?>

Next, I have an sql query to lookup table 'entry_keywords' for the text 'actualtag' (or whatever that it entered in the URL).

Using a 'LIKE %$tag%' will cause any character that matches 'actualtag' to display the results.

Question:
1. Is there anyway to do an exact match? (i.e. if you entered actual, no results will be displayed. it must be actualtag)

2. The table 'entry_keywords' consist of many other keywords (i.e. movies video trailer  ... and many more), seperated by space. Is there anyway to match any of the keywords?

For example, if keyword 'movies' or 'video' or 'trailer' is entered, it'll display the result accordingly, but subjected to exact matching as stated above.
LVL 1
localhostAsked:
Who is Participating?
 
Roger BaklundConnect With a Mentor Commented:
What? The code to fetch the tag finds the word after the last /. What do you mean by ...if I enter "entry/keyword", it matches the FIRST word!? It should find the LAST word. Show your code or explain how you enter these keywords, if they are not taken from the url.

You can not match a forward slash, as this is the separator character. The full url would become the keyword.

Backslashes are very special... are you sure you want to match them? They are used as an escape character in both mysql and php. For instance \n means a newline. In general, you would have to enter two backslashes to match a single backslash. Use this to escape the backslashes (transform \ to \\) so they can safely be used in the query:

$tag = ... (like before)
$tag = mysql_real_escape_string($tag);
$sql = ... (like before)
0
 
Roger BaklundCommented:
To get an exact match in the sql query, you would normally use something like this:

$sql = "SELECT * FROM entry_keywords WHERE keyword='$tag'";

From your second question, it seems as you have multiple keywords in the same column, separated by spaces. Then you cuold use a query like this:

$sql = "SELECT * FROM entry_keywords
   WHERE keyword LIKE '$tag %' OR
                keyword LIKE '% $tag %' OR
                keyword LIKE '% $tag'";

The first LIKE matches rows where the tag is in the first position, the last matches when the tag is at the end, and the middle LIKE matches when the tag is neither at the start nor at the end, but somewhere between.

Your second question is how to match either of three tags? Then the query would be something like this:

$sql = "SELECT * FROM entry_keywords
   WHERE keyword LIKE '$tag1 %' OR
                keyword LIKE '% $tag1 %' OR
                keyword LIKE '% $tag1' OR
                keyword LIKE '$tag2 %' OR
                keyword LIKE '% $tag2 %' OR
                keyword LIKE '% $tag2' OR
                keyword LIKE '$tag3 %' OR
                keyword LIKE '% $tag3 %' OR
                keyword LIKE '% $tag3'";

This query could be built dynamically:
function TagExpression($tags) {
  $expr  =array();
  foreach($tags as $tag)
    $expr[] = "keyword LIKE '$tag %' OR 
               keyword LIKE '% $tag %' OR
               keyword LIKE '% $tag'";
  return implode(' OR ',$expr);
}
$tags = array('movies', 'video', 'trailer');
$sql = "SELECT * FROM entry_keywords WHERE ".TagExpression($tags);

Open in new window

0
 
localhostAuthor Commented:
For the second part, instead of specifying $tags = array('movies', 'video', trailer') and matching with LIKE for each tag, is there anyway to make it dynamic?

It should not restrict to only 3 tags (in this example) but instead, it should be dynamic enough to cater for large amount of keywords (along the line of 8 to 10 tags).
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Roger BaklundCommented:
Yes, my example function TagExpression() was for exactly such cases... you can put as many tags as you like in the $tags array. It matches each tag three times, like in the first example: when it is first, when it is in the middle and when it is last.
0
 
localhostAuthor Commented:
Hmm, what I meant was, the tags should not be fixed in the array (since it is taken from database entry_keywords). Instead, it should be extracted. Hence, I should not be specifying 'movies', 'video' and 'trailers' as each entry_id will have different entry_keywords.

Example:

entry_id 1 will have entry_keywords of "movies trailers darkknight batman"
entry_id 2 will have entry_keywords of "movies videos youtube shortclip"

and so on...
0
 
Roger BaklundCommented:
So, you do not want a query at all, you want a way to extract existing keywords from the table?

If the column is named 'entry_keywords', and the row is fetched into the php variable $row, then you can make an array containing all the keywords with this php statement:
$tags = explode(' ',$row['entry_keywords']);

Open in new window

0
 
localhostAuthor Commented:
Ok, maybe there's some confusion here. Let me try and rephrase in a clearer context :)

1. There's a table data_entry. In it contains many fields such as entry_id, entry_title, entry_text, entry_date, entry_keywords etc.

2. Each entry is tied to an entry_id and it's associated values in the fields.

Example:
entry_id 1 has entry_title of "Hello World", entry_keywords of "hello world sample"
entry_id 2 has entry_title of "Movie Title", entry_keywords of "movies darkknight batman"

and so on...

Requirements:

1. I'm trying to extract the keywords from the database and match it specifically based on what's entered on the URL like http://www.website.dev/tag/entry_keywords.

[Example: If user enters http://www.website.dev/tag/batman OR http://www.website.dev/tag/darkknight OR http://www.website.dev/tag/movies ; result will display entry_id 2 contents).

2. If user enter for example 'batmans' or 'bat' or 'man' or 'movie', nothing will be displayed. It must be specific match.

3. Each entry_keywords field in the table consists of more than 1 keyword, separated by space.

Thanks :)
0
 
Roger BaklundCommented:
I thought you allready had the answer for the first part, to extract the tag from the url? If it's not working, try one of these:

$tag = array_pop(explode('/',$HTTP_SERVER_VARS["REQUEST_URI"]));
$tag = array_pop(explode('/',$_SERVER["REQUEST_URI"]));

To fetch the correct record, use this (from my first post, but with modified table and field names):

$sql = "SELECT * FROM data_entry
   WHERE entry_keywords LIKE '$tag %' OR
                entry_keywords LIKE '% $tag %' OR
                entry_keywords LIKE '% $tag'";

This will not match "bat" or "batmans" if the tag is "batman". It will find the record when there are multiple keywords in the entry_keywords column. The phrase entry_keywords='$tag' will find the keyword when there is only one keyword. If the table also have records with single keywords, you can combine these two methods:

$sql = "SELECT * FROM data_entry
   WHERE entry_keywords LIKE '$tag %' OR
                entry_keywords LIKE '% $tag %' OR
                entry_keywords LIKE '% $tag' OR
                entry_keywords = '$tag'";
0
 
localhostAuthor Commented:
Alright. I managed to get it sorted out. Apologies for the confusion.

Still, there's one bug to iron out... if I entered something like "entry.keyword" in the URL, it works and echo it couldn't find tag "entry.keyword". However, if I enter "entry/keyword", it only matches the first word which is "entry". Is there anyway to make sure the forward slash "/" or backwards slash "\" is matched as well?
0
 
localhostAuthor Commented:
This is what I meant:

1. http://www.website.dev/tag/entry.keyword (OK)
2. http://www.website.dev/tag/entry/keyword (Not OK as it shows only 'entry').

Technically, each entry_keyword should be after ../tag/<entry_keyword>

Hence, if a user enters "entry/keyword" (a string) after ../tag/, it should return no match.

Example without the quote " : http://www.website.dev/tag/"entry/keyword" should echo:

Sorry, "entry/keyword" not found instead of only "entry" not found.

As for the backslash, none of the keywords that I'll use contain backslash. I simply want it to match so that it'll also display "entry\keyword" not found instead.
0
 
Roger BaklundCommented:
When you access this url:

http://www.website.dev/tag/entry/keyword

What is the value of $_SERVER['REQUEST_URI'] ? (Just echo it)

It should be the full url. When we split it and fetch the last word, why is it not 'keyword'?

What do you mean by the user enters "entry/keyword"? Is there an input form involved, or are you talking about the user entering this in the address bar of the browser? Double quotes are not allowed in an url/uri, the browser will send %22, so I would expect that this input:

http://www.website.dev/tag/"entry/keyword"

...would output  "keyword%22 not found"

If you included the mysql_real_escape_string() function call, entering backslashes should work the way you want it to, i.e. they are escaped and legal to use in the query.
0
 
localhostAuthor Commented:
When I access http://www.website.dev/tag/entry/keyword, it returns "keyword".

What I'm trying to do it to treat anything after http://www.website.dev/tag/ as keywords, regardless of the characters used. I'm also trying to convert the characters entered into a string so that it doesn't display things like %20 (space) %5 etc.

No, there's no input form involved. It's taken directly when user enters it into the address bar of the browser.
0
 
Roger BaklundCommented:
Ah, now i get it. I thought you wanted the LAST word after the last / to be the keyword...

This code:

$tag = array_pop(explode('/',$_SERVER["REQUEST_URI"]));

... fetches the word after the last /

You don't want that, you have a fixed number of slashes to match:

$tag = array_pop(explode('/',$_SERVER["REQUEST_URI"],5));

As for converting the special characters, use urldecode()

http://php.net/manual/en/function.urldecode.php
0
 
localhostAuthor Commented:
Alright, almost done.

Even after I used urldecode($tag) and entered funny characters like %20 or <> or * into the address bar after /tag/, it gives me a "Forbidden" page. Enter a single quote ' returns page not found as well.

Is there anyway to ensure that if users enter the following characters # % ' + it'll return empty result?
0
 
localhostAuthor Commented:
Ok. Here's an update.

I did a $tag = urldecode(array_pop(explode('/',$HTTP_SERVER_VARS["REQUEST_URI"],3)));

Then, followed by a $tag = mysql_real_escape_string($tag);

Now, when user enters a single quote into the URL, it shows \'

Only characters still invalid are + * %20 < > etc.

Anyway to match these characters and return it as string?
0
 
psimationCommented:
I'm a bit late to this discussion, but personally I think that the way you are trying to implement this "search" by using the browser's URL line as your user input area is looking for trouble, as you have just seen for yourself, and which has already been highlighted by cxr.

Certain character are NOT allowed in URL's, for instance, the ' - you have no control over that sine it is a browser function - so it is quite correct that you get "Forbidden" errors when trying to add special characters to your URL.

The URL is first and foremost the address to point your browser to - trying to use it as a form input in the same what people are acustomed to do with google search is never going to work unless you write your own extension for Firefox, and then what about IE users?

If your URL is not properly formatted, the browser will never get to the point where your script will be able to see what is appended after the last \ ...
0
 
Roger BaklundCommented:
> Now, when user enters a single quote into the URL, it shows \'

That is correct. Otherwise, it would be a syntax error in the sql when you do

  ... LIKE '$tag %' OR ...

You can not have a single quote within single quotes in the sql. It must be escaped with a backslash. You can remove the backslash when you output it to the screen:

echo stripslashes($tag).' not found';

http://php.net/manual/en/function.stripslashes.php
0
 
kurijovCommented:
2localhost:
You has a wrong db structure, you have to read something about db normalization.
0
 
localhostAuthor Commented:
Alright. Thanks for all the advice and help. Appreciate it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.