Excluding tags from search

MySQL database
#1

Hi.

I created my own markup language annotate text and create links to other areas of my site, but I still want the text to be searchable. The tags are enclosed in braces {}. For example, this is the tag for linking to an entry in the glossary {gloss ID|term|mouseover text}. ID references the ID of the term in the MySQL database, and term is the word or term itself. I have code that parses the text, replacing with a hyperlink to the relevant page and section of that page, with the term enclosed in the anchor tags.

How can I exclude such tags from searches whilst still allowing the term to be part of the search?

#2

Well, how are you searching? In PHP or JS or what? You could assign a class to the tag and tell your code
to skip those classes. Or, if you already have parsing code that replaces the text with a hyperlink, just do
a check for whatever in that code… Not sure what you really are asking…

Perhaps a little snippet of your code might help us…

#3

I want to search using a MySQLi query. For example:
[php]“SELECT * FROM database WHERE text LIKE %”.$srch."% ORDER BY id"[/php]
This takes place before I parse the text returned by the query for tags.

#4

Well, there are a lot of ways to fix it. The simplest is to just remove the tags not wanted. You would have
to know what the tags are first. So, in your sample, you are looking for tags in $srch. If you know for example
that you do not want the tag to contain “Ernie”, you would do it like this:
$srch = str_replace(“Ernie”, " ", $srch); ( Which replaces Ernie with a space. )

Now, if your example from earlier: {gloss ID|term|mouseover text} is ALWAYS the same, then you want to
remove the beginning and end to get just the term(s), you can do something like this:
$temp = explode("|",$srch);
$srch = $temp[1]; ( Basically, it separates it into three values and selects just the middle one! )

Not sure if either is what you need, but, hope so… Let us know!

#5

Thanks for the response. I’m sorry, I didn’t explain it properly. The tags will be part of the text being searched in MySQL database, not in $srch. My apologies.

#6

This is the problem with helping online. The meaning of the words are in the one posting… I am still not
really clear on what you need. You said you wanted to remove tags from search keys? Right? Do you mean
that you want to remove tags from the output of the queries?

Re-reading your first post, you have a database of terms. You replace the terms in your site with tags.
And, you want to search for the items in your database. So, does that mean that your data in the database
for the field “text” is like the sample: {gloss ID|term|mouseover text} and you want to do a query to
search just for the term? If so, you can do it this way…

( Not tested as I do not have that database here… LOL )
“SELECT * FROM database WHERE
SUBSTR(text, LOCATE(’|’, text)+1, (CHAR_LENGTH(text) - LOCATE(’|’, REVERSE(text)) - LOCATE(’|’, text)))
LIKE %” . $srch . “% ORDER BY id”

Basically, it takes the data in the field “text” which is like: {gloss ID|term|mouseover text} and it locates
the starting " | " and ending " | " and takes the sub-string of it and then does the LIKE. It is a very hard
coding to sort out and is a bit complicated. If you break it down into parts, it might be easier to understand.
Get the start of the term, the position after the first “|” , get the position of the last “|” and then take out
the text in between. Then, use that for the LIKE comparision…

Hope that is really what you want… Good luck!

#7

I appreciate you trying to help. I’m not very good at explaining things. There could be text in the database like this, ‘The {gloss 1|rose|See the entry on roses for more details} is a member of the {gloss 4|rosaceae|Check out the family page} family.’ The search would return the entry if you searched for ‘rose’, but not if you searched for ‘rosaceae family’.

#8

Well, we are here to help. Just do not understand what you really need still. (Sorry if it is me!)

Are you saying you have a field named “text” in your database with these in it:

{gloss 1|rose|See the entry on roses for more details}
{gloss 2|rosey|See the entry on rosey stuff for more details}
{gloss 3|rosed|See the entry on rosed stuff for more details}
{gloss 4|rosaceae|Check out the family page}

And you just want to get the term “rose” out of it and get only the “gloss 1” one?
And you just want to get the term “rosed” out of it and get only the “gloss 3” one?

If that is the case, you need to drop the “LIKE” from your query and use the code I just gave you without
the like and match the term instead… Did that make sense?

If not, then please list a few records from the table and point out what you want to get out of it…

#9

No, I have a field in my table called ‘text’ that includes full sentences like the text I posted. The tags can be anywhere in the text, around any word or phrase. So, the ‘text’ field of the table could contain ‘The {gloss 1|rose|See the entry on roses for more details} is a member of the {gloss 4|rosaceae|Check out the family page} family.’ It’s only after the tags are parsed by the php code I have on the webpage that the text would look like ‘The rose is a member of the rosaceae family.’

#10

Thank you Thank you! I now get what you want… Okay, give me a few minutes…

#11

So… The problem is that it is very easy to pull out tags like this using PHP, but, it is not as simple in MySQL.
There are two ways that jump into my mind how to solve this. One, just save a NON-TAGGED version in a
second field and run the searches against that version. That is the easiest way. Then, use that second
one for your searches. The other way would be to use a complicated query. You can not use preg_match
or other functions inside of MySQL queries. You can use regex functions inside of queries, but, that only
checks for matches. There is no direct way to subject or delete multiple sections of text from a field. You
could create a MySQL “STORED-PROCEDURE” that would do the string transformation when called and then
call it in the WHERE clause. It would really depend on how much data you are talking about. If it is small,
then we can give you a routine to create the NON-TAGGED version and you could update your database.
If it is a huge amount of data, then we might be able to create a stored routine that would take the field
and create a version without the tags and pass it back to the WHERE clause.

I spent hours testing and finding out the above info. So, decide which is best for you and then we can help
you sort that way out. Sorry I did not come up with a solid solution…

#12

Thanks for the response, and for your efforts to find the answer.

I think I’ll have to go with the option of making a version without the tags. it isn’t a lot of information, so I think it’ll be the best option.

Thanks.

#13

Yes, that would be the easiest way. There are simple ways to convert them and save them without tags in
PHP. You can loop thru the text and use strpos to locate them and use strreplace to remove them. If you
can’t figure that out, let us know…

#14

Thanks. It’s fine. I have the original data stored in a CSV file. I can just open that in Excel and use the search and replace function.

Sponsor our Newsletter | Privacy Policy | Terms of Service