check for whitespace fails
Published 7 months 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)
- 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.
I think you are using the strlen of the key you should use the value instead
```
if (strlen($alleles ) != strlen(trim($alleles )))
```
i don't know why the code is not showing correctly
thank you but I need to check the value of $rsID indeed
check the screenshot here: https://drive.google.com/open?id=1DKpA5j6Y8IOYzAnTlxcX6T4xfHW5xjGm
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;
yes this code works but it does not when I retrieve the variable as a result of database query
Yes, can i see the database query?
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
Dont forget too that you are looping, so the if will return the result of the condition of the last value
@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) {
}
cant you use like to find each variant?
->where('rsID', 'like', ' %')
Are you sure it's actually "spaces" in the db and not ?
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..
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
@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.