?
Solved

Trim newlines and whitespaces

Posted on 2010-01-12
15
Medium Priority
?
270 Views
Last Modified: 2012-05-08
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();
		  exit();
		}
	$query= 'select * from members '
		."where IDkey>'2' limit 420,10 ";

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

$memberID=trim($row['memberID']);
$pd=trim($row['pd']);
$email=trim($row['email']);


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 Xsomeone@domain.comXsomeone@domaim.com X
622 Xsomeone@domain.comXsomeone@domaim.comX
623 Xsomeone@domain.comXsomeone@domaim.comX
624 Xsomeone@domain.comXsomeone@domaim.comX

Open in new window

0
Comment
Question by:Dan-LL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26297132
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?
0
 
LVL 1

Author Comment

by:Dan-LL
ID: 26297361
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.

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26304490
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Dan-LL
ID: 26307198
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.

Stummed

HERES THE OUTPUT

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
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26307391
Check this:
http://php.net/manual/en/function.html-entity-decode.php

"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
error_reporting(E_ALL);



// A FUNCTION TO TEST FOR A CLEAN STRING OF SPECIFIC LENGTH
function clean_string($str, $len=20)
{
    // LETTERS AND NUMBERS ONLY
    $regex = '/[^A-Za-z0-9-]/';

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




// DEMONSTRATE THE FUNCTION IN ACTION
if (isset($_GET["e"]))
{
    $e = clean_string($_GET["e"]);
    echo "<br/>$e";
}
// END OF PHP - PUT UP THE FORM
?>
<form method="get">
ENTER A STRING FOR TESTING:
<input name="e" />
<input type="submit" />
</form>

Open in new window

0
 
LVL 1

Author Comment

by:Dan-LL
ID: 26309164
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
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26309179
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.
0
 
LVL 1

Author Comment

by:Dan-LL
ID: 26309189
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) ""
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26309212
Yeah - it is getting translated to a blank in the transition from your location to my location, sorry.
0
 
LVL 1

Author Comment

by:Dan-LL
ID: 26309249
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)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26309497
Probably the MySQL export.  Anything that gets us the least risk of munged data.  Thanks, ~Ray
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 26309679
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

0
 
LVL 1

Author Comment

by:Dan-LL
ID: 26332575
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.
0
 
LVL 1

Author Closing Comment

by:Dan-LL
ID: 31676322
thanks Ray
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 26332589
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
0

Featured Post

WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question