check for whitespace fails

Published 1 month ago by arecuk

Hey guys,

I have external database that I connect to from laravel. This external table has a field: rsID varchar40 utf8_general_ci

The right format of rsID is 'rs123'. However, in some case based of user-error when entering new raws the ID contains a space:

' rs123' - the space at the beginning

'rs123 ' - the space at the end

The space is stored in the table and I can see it using phpmyadmin.

Then, in my laravel app I query the table and indeed I get the rsID rendered with a space.

I want to identify those rsIDs that contain space but Im having hard time with it.

when I hardcode the variable all works just fine!
$x = ' xx';
if (strlen($x) != strlen(trim($x)))
 there is space in the string!

but when I get the variable as a result of database query it fails:
foreach ($data as $rsID => $alleles):
if (strlen($rsID ) != strlen(trim($rsID )))
 although rsID contains space the condition is evaluated as false

Does any one has some idea? I've been playing with it for hours but somehow cant figure it out.

Thanks a lot, Tomas

Best Answer (As Selected By arecuk)
m-rk
  • Try http://www.fontspace.com/unicode/analyzer/ and paste it in there. Maybe it shows you want kind of character it is. It could be anything.
  • I think there is no easy mysql update query to do a preg_replace function. So you could just run a simple select query. Detect the malformed ones and update them with the response of what preg_replace('/[^a-zA-Z0-9]/','', $snp->rs_id) gives you.
  • And to prevent this in the future you should strip all unwanted characters before you save the object: $input = preg_replace('/[^a-zA-Z0-9]/','', $input)

I just opened your sql file and changed the character encoding of the file to something else. For windows-1250 it gives me:  rs1800795o it is definitely not a space.

Ismailm

I think you are using the strlen of the key you should use the value instead

​```

if (strlen($alleles ) != strlen(trim($alleles )))

​```

Ismailm

i don't know why the code is not showing correctly

arecuk

thank you but I need to check the value of $rsID indeed

check the screenshot here: https://drive.google.com/open?id=1DKpA5j6Y8IOYzAnTlxcX6T4xfHW5xjGm

Ismailm

Actually i just saw what you did, for example

$var = "a";
strlen($var); // RETURN 1;
$var2 = "a      ";
strlen($var2); // RETURN 6;
strlen(trim($var2)); // RETURN 1;
arecuk

yes this code works but it does not when I retrieve the variable as a result of database query

Ismailm

Yes, can i see the database query?

m-rk
m-rk
1 month ago (37,020 XP)

I don't know if this is helpful for you but if you just want to remove all whitespaces around the character you could use this query if you have the rights to do that:

UPDATE your_table SET rsID = TRIM(rsID)

That will remove all leading and trailing whitespaces from your value. See for more options and details https://www.w3resource.com/mysql/string-functions/mysql-trim-function.php

Ismailm

Dont forget too that you are looping, so the if will return the result of the condition of the last value

arecuk

@m-rk - well the query returns 0 raws affected, although there are rsID with spaces

@ismailm - good point but I use this code:

if (strlen($rsID) != strlen(trim($rsID))): dd ($rsID); endif;

m-rk
m-rk
1 month ago (37,020 XP)

@arecuk Your code looks ok at first sight. Are you 100% sure that the rsID isn't already trimmed somewhere? Can you maybe dump($rsID) of a value that you are 100% sure that must be incorrect?

There is no real need for doing the strlen check btw. You could also just compare the trimmed value with the untrimmed value.

if (trim($rsID) !== $rsID)) {

}

@arecuk, reply on your query with 0 results... That is odd. Than it looks like the space you see is not a real space but some other fancy utf-8 character maybe?

update 2 you could also try the following. This removes all non alphanumeric values and compares it with the original:

if (preg_replace('/[^a-zA-Z0-9]/','', $rsID) !== $rsID) {

}
Snapey
Snapey
1 month ago (922,945 XP)

cant you use like to find each variant?

->where('rsID', 'like', ' %')
Cronix
Cronix
1 month ago (325,600 XP)

Are you sure it's actually "spaces" in the db and not  ?

shez1983

it might not be spaces.. like cronix say.. u should do a var_dump() to see what it actually is... damn he beat me to it..

arecuk

thank you for all your comments ... I think it could something else than a space, although I can not figure it what else could it be?

in the database there is only space, not   ... var_dump does not help either

here is a screenshot from phpmyadmin: https://drive.google.com/open?id=1qTcQ_2_mKUekBpz-S7wJyeiHh_JiIYVq

and here dump of my table with the a few entries with spaces as shown on the picture above: https://drive.google.com/open?id=1uZIjod-Yqu9t0Bewj5Mw_d34LrCKHunl

arecuk

@m-rk great, that helps somehow!

following code identify the rsID with a space:

if (preg_replace('/[^a-zA-Z0-9]/','', $snp->rs_id) !== $snp->rs_id):
                dd ($snp->rs_id);
              endif;    

now the question is:

  • what is the space actually? it could be some fancy utf-8 character, as mentioned by @m-rk ...
  • how to remove it?

thanks a lot, I feel we are getting there!

Please sign in or create an account to participate in this conversation.