Seem to be querying the database incorrectly

[oracle765]

SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(city,Countryname) AGAINST ('South shields*' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City
LIMIT 15

hi all I have a query against the database table which uses the full text index's and I am limiting this to 15 rows for the ajax

 

problem is it is returning Africa and other things before south shields

 

is there something I am missing as I know South shields is in there when scrolling through all the results

 

 

here is my query

 

 

[mac_gyver]

the MATCH(city,Countryname) AGAINST ('South shields*' IN BOOLEAN MODE) term produces a relevancy score (a positive (true) value for matches, a zero for non-matches). you need to include this term in your SELECT term (give it an alias name of score) and then use ORDER BY score DESC in your query.

[oracle765]

   $q=$_POST['search'];
    
	$string = '"' .$q. '"';


	    $sql_res=mysql_query("SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(city,Countryname) AGAINST ('+$string' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City");

SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(City) AGAINST ('+"South shields"' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City
LIMIT 15

hi again thanks for that

 

 

I have managed to figure the query out in the backend of the database which works for south shields now and is lightening fast

 

EG

 

but because of the double quotes  around the word south shields I cannot get my head round it in php as my query already has double quotes

 

in php I am trying this but it does not work

 

 

 

 

[jazzman1]

works for me:

$q = 'South shields';

$string = '"' .$q. '"';

echo $sql = "SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(city,Countryname) AGAINST ('+$string' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City";

Result:

 

 

SELECT COUNT( City ) AS Hotel, City, CountryName, Country FROM `activepropertylist` WHERE MATCH(city,Countryname) AGAINST ('+"South shields"' IN BOOLEAN MODE) GROUP BY City,Countryname,Country ORDER BY City

[oracle765]

$q=$_POST['search'];

$string = '"' .$q. '"';

echo $sql = "SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(City,Countryname) AGAINST ('+$string' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City";

yes that's what I am doing now and it does not seem to work it just hangs and hangs but does not show anything

 

even though it works instantly in the database

 

here is what I have

 

 

[jazzman1]

Because a phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed. They are not the quotation marks that enclose the search string itself  - http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

 

Can you provide same sample of data and tell us what result you are expecting to get.

[oracle765]

$q=$_POST['search'];

$string = '"' .$q. '"';

$sql_res=mysql_query("SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(City) AGAINST ('$string' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City
LIMIT 10");

hi Jazzman

 

thanks for this help and yes it is working now I just had to wait for the webserver to refresh but I still have a problem

 

like you said it is because it is enclosed around double quotes now the word has to be exact so

 

it will find south shields

it will find newcastle

Newcastle upon

 

Newcastle upon tyne which is fantastic

 

but I am looking for it to actually auto predict

 

EG

 

if you type in "Newcastle up" or Newcastle upon ty" it will not auto predict "Newcastle upon tyne" until you have typed the full word.

 

is there a way around this

 

you can see for your self on my site which is www compareandchoose.com.au/hotel

 

also here is the updated code

 

finally thanks again in advance (what I have so far now is good, but would be better if it auto predicted the keyword in question)

[mac_gyver]

for your auto predict, it sounds like are asking about a wild card search, that starts with whatever has been entered upto that point, but should match anything after whatever has been entered. if so, you would add an * on the end of the search term.

 

btw - this is all covered in the documentation, which jazzman posted a link to.

[oracle765]

$string = '"' .$q. '*'. '"';

$string = '"' .$q. '*"';

$string = '"' .$q. '"*';

I have tired that and it does not seem to work

 

I have tried the asterix in three different places as shown

 

 

[mac_gyver]

afai can determine, to make this work, using both an exact phrase and a wild-card ending, you could use either of the following methods -

 

1) all but the last word entered, even if there is only one word, are split from the submitted search string and become the exact phrase, then the last/partial word, if there's more than one word, would be added to the search term as +partialwordhere*. for your example, the search $string would be - "Newcastle upon" +ty* edit: if there is only one word/partial word, this should not be used as an exact phrase, only as the +partialwordhere*.

 

this however will match things where the partial word match exists anywhere, not just immediately following the exact phrase. the suggestion found on the web is to add a HAVING clause with a LIKE comparison that matches the complete phrase that was entered - HAVING City LIKE 'Newcastle upon ty%'

 

2) this is a variation on #1, without using the exact phrase at all. you would split the words and make each one required, with the last one adding the wild card. for your example, the search $string would be - +Newcastle +upon +ty*. you would still also use the HAVING clause as described above to filter the resultant rows to those that only have the complete phrase in them.

Edited August 9, 2014 by mac_gyver
better information