MySQL exact match (with PHP script)

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);

Select allOpen in new window

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).

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.

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...

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']);

Select allOpen in new window

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 :)

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'";

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?

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.

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.

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.

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

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?

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?

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 \ ...

> 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

2localhost:
You has a wrong db structure, you have to read something about db normalization.

Alright. Thanks for all the advice and help. Appreciate it!