Search for any word in SQL Search

[Robert_Craig]

I am having an issue with my website SQL Search. In my database I have a row called 'Tags' and within that field I enter any keywords that someone would be likely to search. The problem I am having is that I cannot get it to work the way I would like. For example in the tags row I have:

1) Red Dress

2) Blue Dress

My code is

SELECT * FROM videos WHERE tags RLIKE '[[:<:]]{$search_query}[[:>:]]'

Now with this if someone searches red dress or even just red, then it will show the correct results. However if someone searches dress red it will not show any thing because in my tags field in the database, I have it entered as Red Dress in that order.

I have tried this code:

SELECT * FROM videos WHERE tags LIKE '%$search_query%'

But this means the user can search words like dres and the results for dress will appear or even just the letter 'e' and then blue and red will appear because they both have the letter 'e' in them.

What I would like is for the RLIKE code to work as it is but just be able to search any word from the tags row in my database, rather than in a particular order.

If anyone can help it would be most appreciated, thank you.

Robert

[ginerjm]

First of all you don't have a 'row called tags'.  You have a column/field named 'tags'.

Now to the search.  You are using multiple words in a field to add some uniqueness to a record.  Do you have rows wherethe tag field has more than a single descriptive term, such as 'red dress blue shirt' or 'red dress blue dress'??  Cause if you do your db design needs to be re-thought.  In the case you presented ( of which there are many more possibilities) you could change your search to build a where clause that contains two conditions ( the number of words in your search query).  That would look like

	$where = "where tags  like '%$word1 ' and tags like '%$word2' "
	$sql = "select * from videos $where"
	

 

Edited July 8, 2018 by ginerjm

[Robert_Craig]

Thanks for correcting me with regards to the correct terminology. Yes you are correct, currently the tags field has more than a single descriptive term. How do you recommend I add this information in my database?

I am a total beginner in PHP so please excuse the ignorance but where you have written '%$word1' '%$word2' etc. Is this where I would replace the word 'word' with one keyword?

[Barand]

Instead of a sinle table like this

                                                                         product
+-------------+-------------------------+--------------------------------------+
| productid   | description             | tags                                 |
+-------------+-------------------------+--------------------------------------+
|      1      | Childs dress            | blue dress, red dress                |
|      2      | Shirt                   | long sleeve, buttondown collar       |
+-------------+-------------------------+--------------------------------------+

you should have two tables, thus

+-------------+-------------------------+
| productid   | description             |
+-------------+-------------------------+
|      1      | Childs dress            |
|      2      | Shirt                   |
+-------------+-------------------------+
       |
       |
       +------------------------------+
                                      |                             tag
                                +-------------+-----------------------+
                                | productid   |    tag                |
                                +-------------+-----------------------+
                                |      1      |  blue dress           |
                                |      1      |  red dress            |
                                |      2      |  long sleeve          |
                                |      2      |  buttondown collar    |
                                +-------------+-----------------------+

Your query will then be

SELECT p.productid
     , p.desscription
     , t.tag
FROM product p
     INNER JOIN
     tag t USING (productid)
WHERE tag = 'blue dress'

 

[maxxd]

You could also look into the full text search - https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

I know the link is to version 8.0 documentation, but full text has been available since at least 5.5.

[Robert_Craig]

Thank you for the replies. As I am a total beginner I found your helpful recommendations too difficult for me to implement on my own so I therefore went for the easier way of just adding another field in my database called tags2. So now my code looks like this:

"SELECT * FROM videos WHERE title LIKE '%$search_query%' OR tags LIKE '%$search_query%' OR tags2 '%$search_query%' ORDER BY title";

The problem I am getting now is that if someone searches for something in either tags or tags2 the search works but if they search for both it returns nothing. I have tried to add tags AND tags2 but that didn't work either.

[Barand]

Yes, it does get awkward when you don't it correctly.

And what happens in future when an an item requires 3, or more, tags?

[Robert_Craig]

That is exactly what I was thinking. But with my limited knowledge of coding it was the only way I could come up with a work around. I have been on People Per Hour and found someone who said they can do it for $5 but not quite sure how they will do it and also they was not from the UK so could not understand me properly.

[ginerjm]

The last query statement you posted is invalid.  Perhaps that is your new problem?

PS - If you are going to jump into the world of programming perhaps you need to think about how one should approach anything in life.  One doesn't jump into the water without a life jacket and knowing how to keep one's head above  water.  Same with driving a car.  So why do you think that you can just come up with a solution for a programming problem without knowing anything about the language and how programming works?  Do some reading.  RTFM for one thing.  Read up on SQL.  Read up on the subject of normalization.  THEN try a simple project.  Then try a harder project.  And when you get comfortable with writing, testing, debugging those little tiny pieces of work, advance to something like what you are doing right now.

Lots and lots of people are in the IT world making a very good living doing this kind of thing.  Do you think they knew nothing and still got those high-paying jobs?  Programming takes a special kind of mind and a good deal of reading and practice to do anything, let alone do it well.  Good luck.  I strongly suggest doing all that I have outlined if you want to succeed.  What you are doing right now is just wrong.  Wrong. Wrong. Wrong.

Edited July 10, 2018 by ginerjm

[benanamen]

7 hours ago, Robert_Craig said:

I therefore went for the easier way

This will be one of your greatest downfalls in programming. (And in life.)