PHP: Count words in a MySQL column

April 27, 2010

Last week I posted a query that will return the number of words in a column of a table in a MySQL database. I was looking through a huge database and the query took a really really long time. Today I decided to rewrite it in PHP and see if it was faster. Much Much Much faster. PHP has a built in function called str_word_count that is super quick at counting. If you have a huge database and access to PHP I definitely recommend this method over the query I  had before.

/* gather the db connection info */
$db_host = 'localhost';
$db_username = '';
$db_password = '';
$db = '';

$mysqli = new mysqli($db_host, $db_username, $db_password, $db);

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* db queries */
$sql_text = "SELECT text_to_look_through FROM `table_containing_text`";

/* useful vars */
$num_words = 0;

echo '

Counting Words'; echo '
'.$sql_text; $result = $mysqli->query($sql_text); while($r = $result->fetch_assoc()) { $num_words += str_word_count($r['bodytext']); } echo '


Totals'; echo '
Num Words: ' . number_format($num_words, 0, '.', ','); $mysqli->close();