View Full Version : keyword searching

01-07-2004, 09:08 AM
I've created a web site using MySQL and PHP to interact with the database and with several days of blood, sweat, and many tears I have been successful in being able to set up the database and tables with several search options.

However, I can't seem to get a "keyword" search to pull up the results based on entering a keyword.

I thought this would work but it doesn't:

$get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords and prod_desc like 'prod_keywords%' order by prod_name, city";

Can anyone tell me if I'm close or far away from success?

Thank you kindly for any responses!

01-07-2004, 09:38 AM
Take a look at:


Your query will throw an error because of improper use of 'and'.

Your query should look like
$get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords LIKE '%" . $keyword . "%' and prod_desc like '%" . $prod_keywords . "%' order by prod_name, city";

where $keyword is a PHP variable, containing one keyword from the searchstring --> if your searchstring contains multiple keywords, then you need to explode it on spaces and build a collection with it, excluding words like 'the', 'a', 'like', 'then' + validating that the string doesn't contain wildcards (like %, _ etc) + taking logical operators into account etc.

But there i no point in reinventing all this. The fulltext search will probably deliver whatyour after.

01-07-2004, 10:52 AM
Thank you raf... this looks like it will indeed do what I require. I am trying to get it to work and am stumbling on how to make the against value a constant when I am passing the user's inputted keyword as a variable from a previous page...

can I create a constant from a variable? The reason for this is that in order for the match() against() function to work, the value of against must be a constant.

eg. can i define the constant by doing this?
define("KEYWORD", "$prod_keywords");

also, I use PHPMyAdmin and I simply went into the table and made the necessary columns "fulltext" but when I test my query I get the message "Can't find FULLTEXT index matching the column list"

any help would be hugely appreciated

01-07-2004, 11:40 AM
It's just

... MATCH (yourfultextcol1, yourfulltextcol2) AGAINST ('" . $_POST['searchbox'] . "') ...

(assuming that the textfield on your searchform has name="searchbox" and that the action="post" inside the formtag)

To create the index: never done it throug phpMyAdmin. But just open the sql tab and enter this

ALTER TABLE yourtablename ADD FULLTEXT (yourfultextcol1, yourfulltextcol2);

replace the bold by your tablename and the columnnames that the index needs to be created on. (maybe the semicolon isn't needed)

01-07-2004, 11:49 AM
Thanks again raf.... too many late nights trying to get this thing to work. I have all kinds of books here on PHP and MySQL and I've searched the net, but this site, with your help, is by far saving me a ton of time!

01-07-2004, 12:08 PM
Thats the idea;)

We all needed a few good pointers when we started out (or take on another language) and if we can save you a few hours by spending a few minutes, then we'll gladly do so.

01-07-2004, 09:23 PM
I discovered this site just a couple of weeks ago and it is the BEST resource I have found... I just have to learn how to get involved more.

I am a graphic designer trying to expand my web skills and I will be spending time in other areas of this site hopefully I will be able to help others out with graphic stuff. I do believe that giving is just as important as receiving... and obviously the people in this site do as well... otherwise it would not work.

It's pretty exciting when I put in the time (tons of it) and when I hit "enter" it works!

01-07-2004, 10:24 PM
Good attitude.

I hope you stick around and pick some stuff up. If you wanna get involved, then just jump in. There are plenty of eople around here needing help on design issues.

Have fun!