Trim newlines and whitespaces

I have the code before running as a one of quick fix of a dirty database, this is just a small example of the code, it has more fields and the DB has 4000 records in it.

My problem is that some records (always the same IDKey which each run (not random)) seems to not update,

look at my output, the 3rd one down has trimmed the \n off but as in shows between the first 2 X's which would look like the $row['email'] did have a \n in it but now cleaned.

If I run the code a second time I get the same output, checked in phpMySql and the \n is still there.

over the 4000 records about 100 still have \n in them....

I would think running the code a second time would have done the trick but no,

does anyone have a clue why it would be selective with the trim()
$db_conn = new mysqli($host, $user, $pass, $db) or die($errorcon2sql);
	if (mysqli_connect_errno())
		{ echo 'Connection Failed: '.mysqli_connect_error();
	$query= 'select * from members '
		."where IDkey>'2' limit 420,10 ";

	$result= $db_conn->query($query);
	while ($row = mysqli_fetch_assoc($result))


print $IDKey." X".$email."X".$row['email']."X<br/>";

$db_conn->query("UPDATE members set pd='$pd', title='$title', email='$email' where IDKey ='$IDKey'");

//echo output

619 Xsomeone@domain.comXsomeone@domaim.comX
620 Xsomeone@domain.comXsomeone@domaim.comX
621 X
622 Xsomeone@domain.comXsomeone@domaim.comX
623 Xsomeone@domain.comXsomeone@domaim.comX
624 Xsomeone@domain.comXsomeone@domaim.comX

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
The issue seems to be in lines 17 through 20.  You trim() $row['email'] and assign that value to $email on line 17 but you use the untrimmed value on line 20. So eventually if you rerun this successfully, you would get everything trimmed.

So if you rerun this and do not catch all the values, it might be that there are non=unique rows identified by $IDKey.  

Another possibility is that the initial SELECT query failed.  What kind of error would this throw if it did not work"

$result= $db_conn->query($query);

To debug, I would print out all the results from the SELECT query and test both queries for success.
Does that make sense?
Dan-LLAuthor Commented:
makes a little sense but as said, a rerun has no effect as for line 17 and 20, I echo'ed out the trimmed and non-trimmed so should what was happening, the database is updated with the trimmed $, I would except on a rerun that all would be trimmed.

and for IDKey, it is the primary and 100% unique.

Ray PaseurCommented:
Please try this and post the result here.:

To debug, I would print out all the results from the SELECT query and test both queries for success.
Does that make sense?

Thank you, ~Ray
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dan-LLAuthor Commented:
ray, your a man in the know...

I have another odd thing happening with trim() and wonder if the 2 are linked somehow, on my site if someone enters their password, say because the drag/dropped or cut/paste from an email, there is a chance they picked up a whitespace from the front of the password or maybe even put a space in front of the password via the keyboard, my trim() does not trim it (so it seems) but should that happen at the end of the password it does trim it...

I heard that chr(160) may cause this, if so it could be the problem with my original question as someone may have dragged and dropped the record.

what is a chr(160) and could this be the problem, if so, can it be removed.



DB is from the $ out of the database with CD being the $ after trim()

DB617 XDanglerX
CD617 XDanglerX

DB618 XDanglerX
CD618 XDanglerX

DB619 XShore AnglerX
CD619 XShore AnglerX

DB620 XShore AnglerX
CD620 XShore AnglerX

DB621 XShoreAngler X
CD621 XShoreAnglerX

DB622 XDanglerX
CD622 XDanglerX

DB623 XShore AnglerX
CD623 XShore AnglerX

DB624 XDangler X
CD624 XDanglerX

DB625 XDanglerX
CD625 XDanglerX

DB626 XShore AnglerX
CD626 XShore AnglerX

Look at 621 and 624, not updated in DB even after a rerun, really odd, in the export list from phpMyAdmin those 2 records have \n still the others records are cleaned
Ray PaseurCommented:
Check this:

"Note: You might wonder why trim(html_entity_decode('&nbsp;')); doesn't reduce the string to an empty string, that's because the '&nbsp;' entity is not ASCII code 32 (which is stripped by trim()) but ASCII code 160 (0xa0) in the default ISO 8859-1 characterset."

So here is what I might try.  You might use something like $str = str_replace('&nbsp;', ' ', $str);

Another way to verify what is really in there is to use var_dump() to print out the data fields.  Then look at the view source in the browser window.  

You might also try something like this: echo htmlentities($str); to try to visualize the invisible data.

For processing input fields like user names and passwords, I have a requirement that they be composed of letters and numbers only, and I use this "clean_string" function.  I trim the field, then run it through the function.  If the field after the function is different from the field after trim and before the function, then the input was unacceptable.

HTH, ~Ray
<?php // RAY_clean_string.php

function clean_string($str, $len=20)
    $regex = '/[^A-Za-z0-9-]/';

    $str = trim($str);
    $str = substr($str,0,$len);
    $str = preg_replace($regex, '', $str);
    return $str;

if (isset($_GET["e"]))
    $e = clean_string($_GET["e"]);
    echo "<br/>$e";
<form method="get">
<input name="e" />
<input type="submit" />

Open in new window

Dan-LLAuthor Commented:
i'm not getting anywhere with this orginal question, the var dump and the htmlentities just show a single whitespace between the double quotes, guess I am going to have to go through them all by hand, may be quicker in the long run.

Thanks for the function ray, had a play with it, similar to my version but a lot clear to follow, many thanks
Ray PaseurCommented:
If you want to email a segment of the bad data to me, please use the email address in my public profile.  Maybe I can spot something right away if I can see it in HEX in my editors.
Dan-LLAuthor Commented:
ps , part of the var dump, look at title, still has white space, in the database show as \n

array(29) { ["IDKey"]=> string(3) "677" ["title"]=> string(12) "ShoreAngler " ["blog"]=> string(0) ""
Ray PaseurCommented:
Yeah - it is getting translated to a blank in the transition from your location to my location, sorry.
Dan-LLAuthor Commented:
ray if I email you, what data would you like, a var dump or export from mysql? var dump shows the whitespace whilst mysql export show a \n (slash n)
Ray PaseurCommented:
Probably the MySQL export.  Anything that gets us the least risk of munged data.  Thanks, ~Ray
Ray PaseurCommented:
Hi, Dan.  OK, here is what I see in the MySQL dump.  Amazingly the literal backslash-n has gotten into the data base table.  This probably happened because there was a single-quoted variable like this:

$value = 'ShoreAngler\n';

If you wrap a character string in apostrophes you lose the dynamic substitution that you get when you have the double quotes.  Part of that dynamic substitution includes the control character and escape sequences.  Unfortunately this can be hard to see because some browsers are very forgiving about their character sets and so they render this as a blank.  Feh.

My recommendation is to try the trim again like this:

$value = trim($string, '\n'); // NOTE THE APOSTROPHES

Please try that and let me know if it helps.  Best regards, ~Ray
(200, 'ShoreAngler\n', '', ... etc ...

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dan-LLAuthor Commented:
the quick answer to that and many times of different versions of that is, no luck... Ray I have just 65 records to change, my new input form is clean and does not cause this to happen so I will save mine and your time and get the wife to ecit all the records by hand, lol...

thanks so much for all your efforts.
Dan-LLAuthor Commented:
thanks Ray
Ray PaseurCommented:
Thanks for the points - and with only 65 records to fix, I think I would ask your wife to do it, too!


Best regards, ~Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.