Counting keywords within mysql table's field

[jmahdi]

Hi,

 

I'm trying to get a number of different keywords from many table fields, and i want to count them according to the occurrence of each keyword, so it should be like (keyword1 occurred 10 times for example ).

 

the problem is when i use count in for loop:

 

	
      $tags = explode("|", $row[0]);
     $i=1;
$count = 0;
for($i; $i<count($tags); $i++){
    print "$count {$tags[$i]}<br />";
    $count++;
}
    

 

it counts the occurrence of key words in each field separately from the rest (i.e.

 

0 day
1 night

0sun
1 moon

0 clouds
1 rain

)

 

2 keywords in each field (some other fields got more).

 

I want the count to be more like:

1 day
2 night

3 sun
4 moon

5 clouds
6 rain

 

thanks

[AyKay47]

You could store each keyword in an array and then display the count of the array, depending on how many keywords there actually are. This might be better done from the query itself using COUNT(), but without seeing it it's hard. Here's a snippet of what I am talking about for the PHP:

 

$tags = explode("|", $row[0]);
$moon = array();
$sun = array();
for($i = 0; $i < count($tags); $i++)
{
    switch ($tags[$i])
    {
        case "moon":
            $moon[] = $tags[$i];
            break;
        case "sun":
            $sun[] = $tags[$i];
            break;
    }
}

echo count($moon) . "<br />" . count($sun);

[JonnoTheDev]

No, no, no. This is awful code

switch ($tags[$i])
    {
        case "moon":
            $moon[] = $tags[$i];
            break;
        case "sun":
            $sun[] = $tags[$i];
            break;
    }
}

If I had a thousand keywords are you going to add them all to a switch/case statement? What if you don't know what the keywords are?

 

There is a php function for doing what you are after, array_count_values(). All you need is one array containing all of your tags. So if you are using the explode() function on many fields, just use array_merge() to put all the data into one array. Then use array_count_values() on the array and it will give you the number of occurances of each tag in the array. i.e.

 

<?php
$tags = explode("|", $row[0]);
$tags = array_merge($tags,explode("|", $row[1]));
print_r(array_count_values($tags));
?>

 

Here is a link to the manual:

 

http://uk3.php.net/array_count_values

[AyKay47]

No, no, no. This is awful code

switch ($tags[$i])
    {
        case "moon":
            $moon[] = $tags[$i];
            break;
        case "sun":
            $sun[] = $tags[$i];
            break;
    }
}

If I had a thousand keywords are you going to add them all to a switch/case statement? What if you don't know what the keywords are?

 

There is a php function for doing what you are after, array_count_values(). All you need is one array containing all of your keywords. So if you are using the explode() function on many fields, just use array_merge() to put all the data into one array. Then use array_count_values() on the array and it will give you the number of occurances of each keyword in the array.

 

Here is a link to the manual:

 

http://uk3.php.net/array_count_values

 

I said in my post that it depends on a few conditions whether or not my code is viable.

But yes this is certainly a better solution.