php adding blank spaces to mysql result with concat_ws

Question

I have the following dates set and query:


create table test (
  id int unsigned not null auto_increment primary key,
  street1 varchar(32) not null default '',
  street2 varchar(32) not null default '',
  city varchar(32) not null default '',
  state varchar(32) not null default '',
  code varchar(32) not null default '',
  country varchar(32) not null default ''
);

insert into test (street1, street2, city, state, code, country) 
          values ('44 Abc St', '', 'NYC', 'New York', '10016', 'United States');


    select 
     concat_ws('\n', NULLIF(street1, ''), NULLIF(street2, ''),
                     NULLIF(city, ''), NULLIF(state, ''),
                     NULLIF(code, ''), NULLIF(country, '')) o_address
    from test

http://sqlfiddle.com/#!9/b919c4/3/0

But somehow PHP is interpreting this string with extra white spaces like below: I'am sure it's during the fetch, because tested on few MySQL IDE's and the query works fine.

44 Abc St
        NYC
        New York
        10016
        United States

Could you provide a complete code snippet reproducing your situation? — Thakkie
– Thakkie, Commented Dec 2, 2020 at 21:18
It's just a regular PDO fetch, and just echoing to the page right after. — George G
– George G, Commented Dec 2, 2020 at 21:34
Absolutely nothing I did managed to reproduce. You need to give us the full context. "Just echoing" certainly won't produce new lines. — El_Vanja
– El_Vanja, Commented Dec 2, 2020 at 21:36
I don't use any str replacement or whatever, it's just related to new lines, because if I use only \r (carriage return) then it works fine — George G
– George G, Commented Dec 2, 2020 at 21:36

O. Jones · Accepted Answer · 2020-12-02 23:57:21Z

0

This all depends on what you use to render the "plain text" string you get back from CONCAT_WS(). It's impossible to know what that is from your question.

You said it isn't a <pre> tag in a web browser.

It probably isn't a UNIX-style terminal emulator because those treat \n as an instruction to move to the first position of a new line.

Maybe it's a Win cmd.exe or powershell.

Your best bet may be to use

WS_CONCAT( '\r\n', whatever)

That two-character sequence does what you want in a variety of situations.

answered Dec 2, 2020 at 23:57

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George G Over a year ago

Hi, I said It was a content-type plain/text so browser displays all the new lines tabs and spaces. Also I said if I use \r it works fine, even though with \n it's all same for the result checked with some MySQL IDEs like I use DataGrip. Also I stated I use the PDO extension, not sure if anything can be related to the settings though. this was not happening before but started few days ago.

Nimantha · Accepted Answer · 2021-11-21 17:35:29Z

0

It definitely appears to be your code at fault here; I am unable to replicate the behaviour you describe using....

$dbh=mysqli_connect('localhost', 'root');
$res=mysqli_query($dbh, $sql);
$data=mysqli_fetch_assoc($res);
var_export($data);

This gives me:

symcbean@hal9000 $ php -q test2.php
array (
  'o_address' => '44 Abc St
NYC
New York
10016
United States',

And to ensure there was no doubt about what I was seeing on screen (and which you should have done too) I checked what was being written to the screen:

symcbean@hal9000 $ php -q test2.php | xxd
00000000: 6172 7261 7920 280a 2020 276f 5f61 6464  array (.  'o_add
00000010: 7265 7373 2720 3d3e 2027 3434 2041 6263  ress' => '44 Abc
00000020: 2053 740a 4e59 430a 4e65 7720 596f 726b   St.NYC.New York
00000030: 0a31 3030 3136 0a55 6e69 7465 6420 5374  .10016.United St
00000040: 6174 6573 272c 0a29                      ates',.)

edited Nov 21, 2021 at 17:35

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Dec 3, 2020 at 0:35

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

1 Comment

George G Over a year ago

THanks for your time 1st, and also same thing shows me the result with new lines followed with 8 blank space characters, only difference in our code is that I use PDO module. Will create a separate test cases and will post code as well so you can be sure that it's the same

Collectives™ on Stack Overflow

php adding blank spaces to mysql result with concat_ws

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related