[SOLVED] COUNT LIKES

[rarebit]

What follows is a little example setup to test with.

I'm wanting to be able to count how many matches there are in each hit. So in the example search there should be 2 hits in the first, 1 in the second and 1 in the third. How am I supposed to do this?

 

$host = 'localhost';
$user = 'user';
$pass = 'pass';
$db = 'db';
$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db, $conn) or die(mysql_error());

$s = "DROP TABLE test_search";
mysql_query($s, $conn);

$s = "CREATE TABLE test_search (id int not null primary key auto_increment, title varchar(128), info text )";
if(mysql_query($s, $conn))
{
print "Created table<br>";
$s = "INSERT INTO test_search VALUES 
	('', 'Hamlet (Act III, Scene I)', 'To be, or not to be: that is the question'), 
	('', 'Hamlet (Act I, Scene III)', 'Neither a borrower nor a lender be; For loan oft loses both itself and friend, and borrowing dulls the edge of husbandry'), 
	('', 'Hamlet (Act I, Scene III)', 'This above all: to thine own self be true'), 
	('', 'Julius Caesar (Act III, Scene II)', 'Friends, Romans, countrymen, lend me your ears; I come to bury Caesar, not to praise him')
	";
$res = mysql_query($s, $conn) or die(mysql_error());
}
else
{
print "Table creation failed<br>";
}

print "<br><br>";
print "<h2>Search: 1</h2>";

$s = "SELECT * FROM test_search WHERE info LIKE '%be%' ";
$res = mysql_query($s, $conn) or die(mysql_error());

while ($a = mysql_fetch_array($res))
{
print $a['id'].": ".$a['title']."<br>";
}

print "<br><br><h2>DUMP</h2>";

$s = "SELECT * FROM test_search";
$res = mysql_query($s, $conn) or die(mysql_error());
while ($a = mysql_fetch_array($res))
{
print "<b>".$a['title']."</b><br>".$a['info']."<br><hr><br>";
}

[corbin]

So uh, what exactly is wrong?  Have you tried a COUNT() call?

[rarebit]

Yes, i've tried various combinations using COUNT, but i'm wanting to count the occurrences per result, without having to return all the data (because later i'll want to ORDER BY it, then LIMIT).

 

Do you see the distinction, i'm not wanting to count how many results, but the actual number of occurrences per data block (result).

[borabora12]

your trying to count how many times a word within a field matches your search?

[corbin]

Ahhh sorry I misread your first post.

 

 

I don't know if it's [reasonably] possible with MySQL to find out how many times a word was found in a string with a LIKE clause.

[rarebit]

I'm not as yet totally sure and i'm off for me tea, but i've just come across some stuff referring to:

FULLTEXT

 

BRB (Right!)

[rarebit]

OK, me struggles, even though syntax seems right....

I changed the table creation like this:

$s = "CREATE TABLE test_search (id int not null primary key auto_increment, title varchar(128), info text, FULLTEXT (title,info) ) ENGINE=MyISAM DEFAULT CHARSET=latin1";

and revised my search like this:

$s = "SELECT *, MATCH(title,info) AGAINST ('to') AS score FROM test_search";
$res = mysql_query($s, $conn) or die(mysql_error());
while ($a = mysql_fetch_array($res))
{
print $a['id'].": ".$a['title'].": ".$a['score']."<br>";
}

but alas, no results, until out of general interest and reading around I covered stop words, which gave me a thought, so:

$s = "SELECT *, MATCH(title,info) AGAINST ('borrower question') AS score FROM test_search WHERE MATCH(title,info) AGAINST ('borrower question')";