Page 1 of 1

count words

Posted: Thu Aug 24, 2006 10:46 am
by Think Pink
hello everybody.
could someone tell me if there is a way of counting a word in a filed from a table using a subquery?

examle.
let's say i have the following text in $row[description]
"Before posting on these forums, please read the Rules and Information page. It will make your life here easier. Is that clear or is not clear. Maybe you need to make a change in your life. Bla bla"
Ok, now the sql

Code: Select all

$sql=mysql_query("select * fields form $table where description like %$word%");
$results = mysql_num_rows($sql);
let's say we have 3 results, (1, 2, 3) but in the first result the $word appears 5 times and in 2, 3 the $word apears less times.
The question is hou could I order the results DESC ?
hope I made my self clear.
thx

Posted: Thu Aug 24, 2006 10:57 am
by JayBird
MySQL has no "wordcount" function. it would be best to do this in PHP

Posted: Thu Aug 24, 2006 11:01 am
by Think Pink
yes, i know, but how am i supposed to order results by word occurency?

Posted: Thu Aug 24, 2006 11:04 am
by feyd
Think Pink wrote:yes, i know, but how am i supposed to order results by word occurency?
:arrow:
JayBird wrote:it would be best to do this in PHP
Possible sources of interest are str_word_count() and one of the sort() functions.

Posted: Thu Aug 24, 2006 11:26 am
by Think Pink

Code: Select all

order by char_length(thecolumn)-char_length(replace(thecolumn,'$word','')) desc
this is how is done.

the problem with this is that if a you searche for 2 words it counts exact those words
Ex
this is the text i want to search and not other text.
if you search for "this text" = > 0 results
Ideeas?

Posted: Thu Aug 24, 2006 11:30 am
by feyd
break them up into individual words or change to full text indexes.

Posted: Fri Aug 25, 2006 1:28 am
by dibyendrah
yes, making the field fulltext will make you search faster with better result using match against query.

Posted: Sat Aug 26, 2006 9:21 pm
by Think Pink
hello, thx for your posts.
sorry for not being able to answer back, very soon, but i was out ...
... in the mean time I have solved the problem both with displaying results highlited and with searching for manny words.
I still have a question though.
I thought of complicateing the script a little more, and in the same search to search for more fileds.
example

Code: Select all

$field = array("f_name", "l_name", "description");
foreach ($field as $co_name) {
 $sql = ....
}
.. but is not working
this is my code so far (just a part of it) and is working great, but i want to search aftr all fields

Code: Select all

$sql = "SELECT *
    	                    FROM table
        	                WHERE (valid = 1) ";

	                for ($i = 0; $i < $size; $i++ )
    	            {
        	            if (($i == 0) && ($i == $size - 1)) {
            	            $sql .= " and '".$col_name."' like '%".$post[$i]."%' ";
                	        $cnd .= "  - (char_length(CONCAT(replace(lower('".$col_name."'), lower('".$post[$i]."'), '')))) ";
                    	}
	                    elseif ($i == 0) {
    	                    $sql .= " and ('".$col_name."' like '%".$post[$i]."%' ";
        	                $cnd .= "  - (char_length(CONCAT(replace(lower('".$col_name."'), lower('".$post[$i]."'), '')))) ";
            	        }
                	    elseif ($i == $size-1) {
                    	    $sql .= " or '".$col_name."' like '%".$post[$i]."%') ";
                        	$cnd .= "  + (char_length(CONCAT(replace(lower('".$col_name."'), lower('".$post[$i]."'), '')))) ";
	                    }
    	                else {
        	                $sql .= " or '".$col_name."' like '%".$post[$i]."%' ";
            	            $cnd .= "  - (char_length(CONCAT(replace(lower('".$col_name."'), lower('".$post[$i]."'), '')))) ";
                	    }
	                }

	            $sql .= "ORDER BY char_length(CONCAT('".$col_name."'))";
    	            $sql .= $cnd;
        	    $sql .= "DESC";

Posted: Sat Aug 26, 2006 10:33 pm
by Think Pink
back again.
tried a little something but stil not workig.
I'll post it here hoping that someone has an ideea.
thx

this is what i have (a part of it) and works.

Code: Select all

$sql = "SELECT * FROM table WHERE (valid = 1) ";

for ($i = 0; $i < $size; $i++ )
{
if (($i == 0) && ($i == $size - 1)) {
$sql .= " and field like '%".$post[$i]."%' ";
$cnd .= "  - (char_length(replace(lower(field), lower('".$post[$i]."'), ''))) ";
}
elseif ($i == 0) {
$sql .= " and (field like '%".$post[$i]."%' ";
$cnd .= "  - (char_length(replace(lower(field), lower('".$post[$i]."'), ''))) ";
}
elseif ($i == $size-1) {
$sql .= " or field like '%".$post[$i]."%') ";
$cnd .= "  + (char_length(replace(lower(field), lower('".$post[$i]."'), ''))) ";
}
else {
$sql .= " or field like '%".$post[$i]."%' ";
$cnd .= "  - (char_length(replace(lower(field), lower('".$post[$i]."'), ''))) ";
}
}

$sql .= "ORDER BY char_length(field)";
$sql .= $cnd;
$sql .= "DESC";
$sql  = mysql_query($sql);
what i tryed to do is ...

Code: Select all

$sql = "select concat_ws(' ', $filed1, $field2, $fileld3) as merged_cols where (valid = 1) and (merged_cols like '%".$field[$i]."%' ) ORDER BY char_length(prezentare) - (char_length(replace(lower(merged_cols), lower('".$field[$i]."'), ''))) as results DESC";
... but is not working. Now, I played a little (actualy a lot) with it and I can't seem to find where i'm wrong.
So pls, if there is someone .....
thx