?
Solved

MySQL exact match (with PHP script)

Posted on 2008-11-14
19
Medium Priority
?
1,246 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:localhost
19 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22965340
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
 
LVL 1

Author Comment

by:localhost
ID: 22965364
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22965378
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:localhost
ID: 22965407
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22965432
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
 
LVL 1

Author Comment

by:localhost
ID: 22965506
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22965765
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
 
LVL 1

Author Comment

by:localhost
ID: 22965837
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 600 total points
ID: 22965869
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
 
LVL 1

Author Comment

by:localhost
ID: 22965919
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22965969
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
 
LVL 1

Author Comment

by:localhost
ID: 22965991
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22966012
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
 
LVL 1

Author Comment

by:localhost
ID: 22966101
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
 
LVL 1

Author Comment

by:localhost
ID: 22966161
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
 
LVL 17

Expert Comment

by:psimation
ID: 22966618
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22967423
> 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
 
LVL 3

Expert Comment

by:kurijov
ID: 22968086
2localhost:
You has a wrong db structure, you have to read something about db normalization.
0
 
LVL 1

Author Closing Comment

by:localhost
ID: 31517014
Alright. Thanks for all the advice and help. Appreciate it!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question