PHP Trim and MYSQL Trim Functions

PHP Trim and MYSQL Trim Functions

Often, when working with PHP / mySQL, you will use data that was sent from forms and data from the database. While the data is often correct, you could end up with errors and surprise output if there happened to be any whitespace before or after you string.

With a mySQL database, it can be hard to detect if a string actually has whitespace attached to it. Luckily, PHP and mySQL have functions that can be used to trim whitespace.

If you plan to insert and retrieve data from a database, using mySQL’s TRIM() function is the way to go.

Meanwhile, any of the PHP functions, trim(), ltrim and rtrim() can be used to remove whitespace from strings or retrieved data. Personally, I like to use mySQL to do the work if that is where the data is coming or going from.

MYSQL Style

 INSERT INTO tablename (id, input, date) VALUES (NULL, TRIM($input_string), now());  

SELECT TRIM(input) FROM tablename where id > 1  

UPDATE tablename set input = TRIM($input_string) where id = '$id'

In addition to trimming whitespace, you can command mySQL to trim text, commas and other unwanted data from the left, right or both ends of a string. The example below shows how to remove a trailing, leading and trailing and leading commas from a database field.

TRIM(TRAILING ',' FROM columnname) 
TRIM(LEADING ',' FROM columnname) 
TRIM(BOTH ',' FROM columnname)

More examples of using the mySQL trim function can be found at https://fullstackwebstudio.com/locations/coding-blog/MYSQL-TRIM-Function.html. In addition to the TRIM() function, the replace() function with mySQL can be used to change anything you want from a string; including removing whitespace and replacing it with nothing. More details about the mysql replace() function can be found at https://fullstackwebstudio.com/locations/coding-blog/mysql-rerplace-function.html.

PHP Style

$trimmed = trim($string);  
$trimmed2 = htmlspecialchars(trim($string));  
$trimmed_left = ltrim($string);  
$trimmed_right = rtrim($string);

September 14, 2014 by kent