Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Full Text Search - What have I done wrong?

    Hi all

    I have tried to do a full-text search with the following code that doesn't work. I know the $keywords string is getting the input from the form as I've echo'd it back and if you remove MATCH(caption) AGAINST('%keywords') then the query will run off a list of all the links. So what am I missing or am I way off base?

    Code:
    <p>
    Full text search <br />
    <form action="index.php" method="post">
    Keywords:<br />
    <input type="text" name="keywords" size="20" maxlength="40" value="" /><br />
    <input type="submit" value="Search!" />
    </form>
    </p>
    
    <?php
    
    // If the form has been submitted with supplied keywords
    if (isset($_POST['keywords'])) {
    $keywords = ($_POST['keywords']);
    
    	
    $query = ("SELECT * FROM photographs MATCH(caption) AGAINST('%keywords') ORDER BY city_town, pic_title DESC");
    $result = mysql_query($query);
    $row = mysql_num_rows($result);
    	
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    	?>	
    <p><a href="photographs.php?id=<?php echo $row['id']; ?>"><?php echo ($row['pic_title']); ?> - <?php echo ($row['pic_date']); ?></a></p>
    <?php
    }
    }
    mysql_free_result($result);
    ?>

    Cheers
    Steve

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    change
    '%keywords'
    into
    '$keywords'

    ==> a full text search doesn't need the wildcard and the '%keywords' will be taken literal instead of that the posted keywords are injected in the query.
    maybe you're just confusing full-text searches with keyword searches (where you woul have "caption LIKE '%$keywords%'")

    for debugging, change your query into

    $query = ("SELECT *, MATCH(caption) AGAINST('$keywords') AS match_score FROM photographs MATCH(caption) AGAINST('$keywords') ORDER BY city_town, pic_title DESC");
    $result = mysql_query($query);

    and then echo out the $row['match_score']
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts
    %keywords to $keywords made no difference. Incidently it was a typo.

    I changed the query and nothing is output to match_score. Does this highlite a problem to you?

    I have searched for text that I know exsist in multiple records.

    Any how when you run a search it simply refreshes the page. If I echo $keywords in the if else statement then on refresh you'll get the search box and below what you entered. (So I know the string is getting the input)

    As mentioned before if you remove the MATCH(caption) AGAINST('$keywords') from the SELECT statement the if else loop will spill the entire contents of the table (suggesting there is no problem with the connection to the Dbase)

    With regards confusion over full-text/keywork searches. What I'm after is a simple search box on the site where if you search for the word Fred then the script would search every field, which I assume would be MATCH(*) AGAINST('$keywords') and echo back all the links to these pages, so would the LIKE statement be the better option. And would it work in the case of Fred Bloggs where it would kick up links to Fred Bloggs - Fred - or just Bloggs.

    Cheers
    Steve

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Question-- did you create a FULLTEXT index on the Caption column?

  • #5
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have, and for most of the others

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Becoming more confused!

    Ok as mentioned before I have had no joy with the following query:

    SELECT * FROM photographs WHERE MATCH (caption) AGAINST ('some text')

    I have been to my PHPmyAdmin and run the query there.... No Joy.

    Then I tried the following, through PHPmyAdmin:

    SELECT id, MATCH (caption) AGAINST ('Town Name') FROM photographs

    And I get a result it echos the id number and then a numerical value (which I assume is MATCH_SCORE)

    Brilliant I assume (there's a lot of that I know!) that I can order my results by MATCH_SCORE getting the most relevant up top!

    Ok now I run this:
    SELECT id, MATCH (pic_title) AGAINST ('Town Name') FROM photographs

    It works - Ok smug smile now growing.....
    and finally this:
    SELECT id, MATCH (pic_town,caption) AGAINST ('Town Name') FROM photographs

    And get error message!!!
    #1191 - Can't find FULLTEXT index matching the column list
    What the hell. MySQL manual states match can be MATCH (col1,col2,....) and both individual selects run!!

    Head hurts - may need a lie down!

    Steve

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    In order to use a FULLTEXT match across more than one column then you have to create that index across more than one column. You can't just create a fulltext index on column a and a separate one on column b and then try to use fulltext on column a, column b.

    Post the results of the following:

    Code:
    show create table photographs

  • #8
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Smile

    Hi guelphdad

    The result is as follows, and indeed all cols are index separately.

    Table Create Table
    photographs CREATE TABLE `photographs` (\n `id` int(11) NOT NULL auto_increment,\n `image_ref` varchar(7) NOT NULL,\n `country` varchar(50) NOT NULL,\n `county_state` varchar(50) NOT NULL,\n `city_town` varchar(50) NOT NULL,\n `pic_title` varchar(100) NOT NULL,\n `pic_date` text NOT NULL,\n `pic_year` year(4) NOT NULL default '0000',\n `caption` longtext NOT NULL,\n `additional_info` longtext NOT NULL,\n `orig_file_name` varchar(50) NOT NULL,\n `orig_file_size` varchar(50) NOT NULL,\n `ava_purchase` char(3) NOT NULL,\n `hits` int(11) NOT NULL,\n `commentsNumber` int(3) NOT NULL,\n PRIMARY KEY (`id`),\n FULLTEXT KEY `country` (`country`),\n FULLTEXT KEY `county_state` (`county_state`),\n FULLTEXT KEY `pic_title` (`pic_title`),\n FULLTEXT KEY `pic_date` (`pic_date`),\n FULLTEXT KEY `caption` (`caption`),\n FULLTEXT KEY `additional_info` (`additional_info`),\n FULLTEXT KEY `orig_file_name` (`orig_file_name`),\n FULLTEXT KEY `orig_file_size` (`orig_file_size`),\n FULLTEXT KEY `city_town` (`city_town`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
    Jumping ahead slightly if I remove all single index colums and replace with one
    Table Create Table
    photographs CREATE TABLE `photographs` (\n `id` int(11) NOT NULL auto_increment,\n `image_ref` varchar(7) NOT NULL,\n `country` varchar(50) NOT NULL,\n `county_state` varchar(50) NOT NULL,\n `city_town` varchar(50) NOT NULL,\n `pic_title` varchar(100) NOT NULL,\n `pic_date` text NOT NULL,\n `pic_year` year(4) NOT NULL default '0000',\n `caption` longtext NOT NULL,\n `additional_info` longtext NOT NULL,\n `orig_file_name` varchar(50) NOT NULL,\n `orig_file_size` varchar(50) NOT NULL,\n `ava_purchase` char(3) NOT NULL,\n `hits` int(11) NOT NULL,\n `commentsNumber` int(3) NOT NULL,\n PRIMARY KEY (`id`),\n FULLTEXT KEY `fullindex` (`country`,`county_state`,`city_town`,`pic_title`,`pic_date`,`caption`,`additional_info`,`orig_file_ name`,`orig_file_size`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
    Then it errors even when I do a single select:
    SELECT id, MATCH (pic_title) AGAINST ('Town Name') FROM photographs
    or assuming you match the index:
    SELECT id, MATCH (fullindex) AGAINST ('Town Name') FROM photographs

    Assuming one index is required PHPmyAdmin requested a size on text fields. Is this for word size to be index or a space size in Kb?

    Cheers

  • #9
    New Coder
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I think were getting some were!

    Ok thanks to all so far, I seem to be getting my head round this a bit more!
    But two more questions as of this morning.

    I have created a new index for 3 colums and have altered the code to that below which now seems to produce 'some' results. But here's the rub! If I search for the town Drax it echos back the id and the city_town. Great! If I enter Drax and Airmyn it kicks up both records. (There's that big smile coming..!). If I search for Goole (of which there are many records) I get nothing! Which is bloody strange as when you search for tower (as there's are records on Goole's Water Tower, it with throw up 1Goole, 4Goole, 8Goole etc (Which is the id, city_town).

    Ok question two the query fires back results in score order, which is good. but is there any way of getting the score number that you see through PHPmyAdmin as I could use it to make a 'relativity bar'.

    Cheers
    Code:
    <p>
    Full text search <br />
    <form action="index.php" method="post">
    Keywords:<br />
    <input type="text" name="keywords" size="20" maxlength="40" value="" /><br />
    <input type="submit" value="Search!" />
    </form>
    </p>
    <?php
    
    include('../phpmysql/config.php');
    include('../phpmysql/connect.php');
    
    if (isset($_POST['keywords'])) {
    
    $keywords = ($_POST['keywords']);
    
    $query = ("SELECT id, city_town, MATCH (city_town, pic_title,caption) AGAINST ('$keywords') AS match_score FROM photographs WHERE MATCH (city_town, pic_title,caption) AGAINST ('$keywords')");
    $result = mysql_query($query);
    $row = mysql_num_rows($result);
    
    if ($row == 0) {
    } else {
    		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    		echo $row['id']; echo ($row['city_town']); ?><br></br><?php
    		}
    }
    }
    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •