count words

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

count words

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

MySQL has no "wordcount" function. it would be best to do this in PHP
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post by Think Pink »

yes, i know, but how am i supposed to order results by word occurency?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

break them up into individual words or change to full text indexes.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

yes, making the field fulltext will make you search faster with better result using match against query.
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post 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";
User avatar
Think Pink
Forum Contributor
Posts: 106
Joined: Mon Aug 02, 2004 3:29 pm

Post 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
Post Reply