strip carriage returns from code 2

[BlindPete]

Hello all,

I have what I thought would be simple issue to reslove but I have been struggling with it all morning and I'm hoping someone can help me.

I have a class that manipulates data. The class takes an SQL statement as a part of its constructor. The problem is that I heavily format my SQL (see below) to make it easier to read in code. If I remove all the carriage returns and tabs from the string ($sql) the class works fine. If I don't it fouls up the $sql. the class parses the SQL and does a lot of manipulations - the tabs and/or carriage returns are not properly recognized or ignored

I'd like to change the constructor to strip out the carriage returns and/or tabs in string. But I'll be d*mned if I can figure out how? Is there a string command to do this?

If I use <b>str_replace()</b> What string(s) is the $needle?

$sql = '
  SELECT 
  CONCAT(LTRIM(RTRIM(firstname))," ", LTRIM(RTRIM(lastname)),) as Name, 
    YEAR(CURRENT_DATE) - YEAR(DOB) AS Age, 
    ID AS Commands 
  FROM '.$catalog.' 
  WHERE INSTR("'.$filterCatalog.'",UPPER(LEFT(LASTNAME,1)))>0 
    AND DOB!="0000-00-00"  
    AND RIP ="0000-00-00" 
  ORDER BY lastname, firstname ';

Thanks in advance... a star awaits you!

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[BlindPete]

oops should have previewed before posting...

If I use str_replace() What string(s) is the $needle?


-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[sleipnir214]

If you want to strip out both tabs and returns, you could do it with two invokations of str_replace(). The first, using '"\t"'^*, will remove the tabs. The second will use '"\n"'^*, '"\r\n"'^* or '"\r"', depending on whether you're editing your scripts using a text editor on a unix-like OS, Win32 or Mac, respectively.

Just don't remember to replace the offending returns with spaces, not just removing them.

I'm curious about the actual problem you're having. It looks from your query that this script interacts with MySQL. I format queries the same way you do, but I've never had a problem with the formatting messing with the query.


^* Minus the outer single quotes.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BlindPete]

Thanks I'll let you know,

I use a windows editor, Komodo. The class is basically a vcr style controller for paginating mySql data.

[first][prev] [ page X ] of Y [next][last] search for [ ]

The class paginates, jumps, filters and searches, all based on the sql string passed to it. It is introspective and determines searches by table structure and fulltext index's. I wrote it 3 months ago and am still tweaking bits of it here and there.

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[DRJ478]

sleipnir214
Intended double negation?

Just don't remember to replace the offending returns with spaces, not just removing them.

BlindPete
You could also use a regular expression with preg_replace that turns all \s into spaces.
As sleipnir214 says, many people format SQL statements like you do but don't run into problems with white space. Maybe you should look at where the query class messes up...

 

[sleipnir214]

DRJ478:
Nope. Conflicting, unresolved partial edits.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[BlindPete]

added this to constructor:

    $sql = str_replace("\r\n"," ",$sql);
    $sql = str_replace("\n"," ",$sql);
    $sql = str_replace("\t"," ",$sql);
    $sql = str_replace("\r"," ",$sql);

Works like a champ. Thanks!

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[BlindPete]

Regular expressions...
I do not find them all that usefull. I guess I have not invested enough time in them. It seems I spend a lot of time massaging them into working as I need, when I could have done the same thing without them in a few minutes.

I've read that there is actually a performance loss to regular expressions... is that true?

Certainly once you "get them" the code is pretty clean and easy to read back. But I'm not there yet.

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/

 

[DRJ478]

What I'd do is the following expression:

$cleanSQL = preg_replace('/\s/',' ',$originalSQL);

There's an article about benchmarking str_replace() and preg_replace()

http://www.php-scripts.com/php_diary/011303.php3

According to the results it depends on the number of iterations. I would consider that the difference will only show if the script is in extremely heavy use or your server has high load.
Personally, I don't have to take efficency into consideration as I have the luxury of a server farm.

 

[BlindPete]

Thanks!

-Pete
Do you get a little guilty pleasure when a celebrity has a bad day?
Well then The Dead Pool is for you!

http://www.nowthatsodd.com/deadpool/

http://www.nowthatsodd.com/tssi/