Ever wonder how many pages, words or tt_content element you have in your Typo3 install? I am working with two large Typo3 sites and I was curious so I wrote a little piece of code. It works really well and is super quick. Here is what the output looks like:
Counting Pages
SELECT COUNT(uid) AS num_pages FROM `pages`Counting Pages Not Deleted
SELECT COUNT(uid) AS num_pages FROM `pages` WHERE deleted='0'
Counting Content
SELECT COUNT(uid) AS num_content FROM `tt_content`
Counting Words
SELECT bodytext FROM `tt_content`
Totals
Num Pages: 4505
Num Pages Not Deleted: 2948
Num Content: 5152
Num Words: 28,394,588
If you would like to implement this yourself I provided the code below, instead of reinventing the wheel yourself.
(I put mine in the typo3conf folder for ease of use)
<?php
/* gather the db connection info from typo3 */
require_once('localconf.php');
error_reporting(E_ALL);
$mysqli = new mysqli($typo_db_host, $typo_db_username, $typo_db_password, $typo_db);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
/* db queries */
$sql_count_pages = "SELECT COUNT(uid) AS num_pages FROM `pages`";
$sql_count_pages_not_deleted = "SELECT COUNT(uid) AS num_pages FROM `pages` WHERE deleted='0'";
$sql_count_content = "SELECT COUNT(uid) AS num_content FROM `tt_content`";
$sql_bodytext = "SELECT bodytext FROM `tt_content`";
/* useful vars */
$num_pages = 0;
$num_pages_not_deleted = 0;
$num_content = 0;
$num_words = 0;
echo '<b>Counting Pages</b>';
echo '<br/>'.$sql_count_pages;
$result = $mysqli->query($sql_count_pages);
$result = $result->fetch_assoc();
$num_pages = $result['num_pages'];
echo '<b>Counting Pages Not Deleted</b>';
echo '<br/>'.$sql_count_pages_not_deleted;
$result = $mysqli->query($sql_count_pages_not_deleted);
$result = $result->fetch_assoc();
$num_pages_not_deleted = $result['num_pages'];
echo '<br/><br/><b>Counting Content</b>';
echo '<br/>'.$sql_count_content;
$result = $mysqli->query($sql_count_content);
$result = $result->fetch_assoc();
$num_content = $result['num_content'];
echo '<br/><br/><b>Counting Words</b>';
echo '<br/>'.$sql_bodytext;
$result = $mysqli->query($sql_bodytext);
while($r = $result->fetch_assoc()) {
$num_words += str_word_count($r['bodytext']);
}
echo '<br/><br/><br/><b>Totals</b>';
echo '<br/>Num Pages: ' . $num_pages;
echo '<br/>Num Pages Not Deleted: ' . $num_pages_not_deleted;
echo '<br/>Num Content: ' . $num_content;
echo '<br/>Num Words: ' . number_format($num_words, 0, '.', ',');
$mysqli->close();
?>
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();
I have been working on a rather large database that contains mostly textual page content. Out of curiosity I decided to see how many words are contained in the column with my content. I did not find a simple MySQL solution to finding the word count, but here is what I did come up with.
SELECT SUM( LENGTH( bodytext ) - LENGTH( REPLACE( bodytext, ' ', '' ) ) +1 )
FROM tt_content
It worked great on the one page I tried it on. There were 5864 words. It has been running for about 5 minutes on the entire set now. Not the most efficient method out there I am sure, but I found it quick and it works.
To use it replace 'bodytext' with the column you are looking in, and 'tt_content' with the table the column is in.
Update
The query ran for 40 minutes before I terminated 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 implemented
I have a lookup table where I am using a composite key which must be unique. I was not sure if MySQL was able to do this or not, but I very quickly found out. It is quite a simple operation actually. I already had a table in existence so I simply altered it.
ALTER TABLE `TableName` ADD UNIQUE KEY (KeyOne, KeyTwo, ...);
There ya go. Use it, have fun with it. Share it. Try not to eat it. Your boss will not appreciate having to purchase a new monitor.
The topic of MySQL and NULLs came up in my IRC room today. It turns out that MySQL tends to handle NULLs in a rather wonky way at times. Personally, I usually do not like allowing NULLs, and the only time they are ever present would be in a RIGHT or LEFT JOIN that had missing information. There are many people out there, however, that do use NULLs, and love them. I am not knocking the use of NULLs in any way, but before you sit down and use NULLs again be sure you take some time and study this bit of trivia one of the guys in the IRC showed us.
mysql> SELECT if(NULL=NULL,"yep","nope");
+----------------------------+
| if(NULL=NULL,"yep","nope") |
+----------------------------+
| nope |
+----------------------------+
If you think this is strange behavior you are right. According to the MySQL manual on control flow the correct response should be 'yep'.
Comments (0) 
