MySQL trim usage example

I was trying to do a statistic about which posts are never visited in a day, the method is select all published articles and also calculate all the posts that had been visited at least once in the range of a day. And then filter out all the visited article from the first collection. But the result is completely wrong.

The reason is for some unknown reasons, the slug title that was used to compare ends with an extra whitespace in the post table, but the visitor stat table which gets its data from HTTP request header didn't contains the whitespace.

 
function scratchOutallVisiblePostsSql() {
    $sql = "select concat('/', seotitle) as seotitle  from posttable where publishstate <> 0 order by insertdate asc";
 
    $allVisiblePosts = d()->querysql($sql);
    $allVisiblePostsExcludeVisited = array();
 
    $day = date("Y-m-d", strtotime("-" . 1 . " days"));
    $sql = "select requesturi , count(*) as count from stattable where " .   "  (insertdate > '".$day." 00:00:00' and  insertdate  <  '".$day." 23:59:50') and refer  like '%google%'  group by requesturi order by count desc";
    $visitedVisiblePosts = d()->querysql($sql);
 
    foreach($allVisiblePosts as $item) {
        //println("-".$item->seotitle."-");
        $seotitle = $item->seotitle;
        $found = false;
        foreach($visitedVisiblePosts as $visited) {
            if($seotitle == $visited->requesturi) {
                //println("found a match, skipping " . $seotitle);
                $found = true;
                break;
            }
        }
        if($found == false) {
            $allVisiblePostsExcludeVisited[] = $item;
        }
 
    }
    printtable($allVisiblePostsExcludeVisited);
 
}
 

There are two methods to solve the problem, trim the column in SQL statement or trim it in php, I thought it will be better to do it in MySQL in this case. MySQL provided the handy function trim to do it. Here is how to use it.

 
mysql> SELECT TRIM('  a string   ');  -- delete leading and trailing whitespace
        -> 'a string'  
 
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxmystringxxx'); -- delete specified leading character
        -> 'mystringxxx'  
 
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxmystringxxx'); -- delete specified leading and trailing character
        -> 'mystring'  
 
mysql> SELECT TRIM(TRAILING 'x' FROM 'xxxmystringxxx'); -- delete specified trailing character
        -> 'xxxmystring'
 
mysql> SELECT concat(LTRIM('  foobar   '), '-');  -- delete leading whitespace
        -> 'foobar   -'
 
mysql> SELECT RTRIM('   foobarbar   ');     -- delete trailing whitespace
        -> '   foobar'        
 

In this example, I just need to trim the selected column

 
    $sql = "select concat('/', trim(seotitle)) as seotitle  from post where publishstate <> 0 order by insertdate asc";