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 '<br/><br/><b>Counting Words</b>';
echo '<br/>'.$sql_text;
$result = $mysqli->query($sql_text);
while($r = $result->fetch_assoc()) {
	$num_words += str_word_count($r['bodytext']);
}
 
echo '<br/><br/><br/><b>Totals</b>';
echo '<br/>Num Words: ' . number_format($num_words, 0, '.', ',');
 
$mysqli->close();
 

One Response to “PHP: Count words in a MySQL column”

1. Ben Lobaugh Online » MySQL: Count Words in Column says:

   April 27, 2010 at 10:45 am

   [...] it. If you are using a large dataset I recommend looking at an alternative method, such as using PHP to count words in a column. Use that link to see the method I [...]