01-07-2004, 08: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!
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, 09: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?
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
... 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, 10: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!
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, 08: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!
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.