php stripping out part of string

Question

I'm trying to build a simple sql statement:

    // build sql statement          
    $sql = "select * from some_tbl where "; 
    if(strlen($mydetails['city']) > 0) { 
        $sql .= "cityname in (".$mydetails['city'].") and "; 
    } 
    $sql .= 'fromdate <= expirydate and expirydate >= curdate() order by rand()';

But $sql is missing everything between < and >. The debugger shows the value of $sql as:

    select * from tbl_adsinfo where fromdate = curdate() order by rand()

This is so basic I'm just lost. I don't think that < or > are special characters right? I've tried escaping them and using double quotes instead and it's the same.

What's up here?

Are you doing this through some kind of web-based editor? Could be something's stripping out what looks to be an invalid HTML tag. Try reversing the order (so it's > ... < instead), or perhaps replacing them with the </> entities and see if that helps. — Marc B
– Marc B, Commented Jan 11, 2011 at 19:05
echo your $mydetails['city'] variable. i'm betting it is empty for some reason. — dqhendricks
– dqhendricks, Commented Jan 11, 2011 at 19:10
Can you please provide all of the code between your $sql lines an when you actually query the DB? Are you calling strip_tags on your sql statement? — jasonbar
– jasonbar, Commented Jan 11, 2011 at 19:18

Joshua Burns · Accepted Answer · 2011-01-11 20:08:48Z

1

You wouldn't happen to be running the $sql variable through the striptags() function, would you? This would be consistent with it stripping out "<= expirydate and expirydate >", as it would assume it to be an HTML tag.

answered Jan 11, 2011 at 20:08

Joshua Burns

8,6725 gold badges52 silver badges63 bronze badges

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

Comments

dlock · Accepted Answer · 2011-01-11 20:03:59Z

I am not sure what is causing the problem. Apparently there is something stripping the html tags off as a sort of security maybe . One suggestion is to try to replace the '<' and '>' with their ASCII codes:

 $sql .= 'fromdate '.chr(60).'= expirydate and expirydate '.chr(62).'= curdate() order by rand()';

edit: You may also use NOT BETWEEN statement like below:

 $sql .= '(expirydate NOT BETWEEN fromdate AND curdate()) AND (expirydate NOT BETWEEN fromdate AND 17530101) ORDER BY rand()

The 17530101 is meant to be the least value for a datetime possible. In the previous code you are checking if the expirydate isn't between fromdate and curdate() AND expirydate isn't less than any of them. That means that expirydate should be greater than fromdate,curdate for the statement to return true, which is what you are trying to achieve.

Phill Pafford · Accepted Answer · 2011-01-11 22:01:11Z

0

Why not use the BETWEEN operator?

$sql .= 'expirydate BETWEEN fromdate AND curdate() ORDER BY rand()';

EDIT:

Looking it over you need the equals as well, try splitting up the actions

$sql .= 'fromdate <= expirydate and expirydate >= curdate() order by rand()';

to:

$sql .= 'expirydate >= curdate() ';
$sql .= 'AND fromdate <= expirydate ';
$sql .= 'ORDER BY rand()';

Or reverse the order of the operator: make the = first

$sql .= 'AND fromdate =< expirydate ';
$sql .= 'ORDER BY rand()';

edited Jan 11, 2011 at 22:01

answered Jan 11, 2011 at 19:27

Phill Pafford

85.5k92 gold badges268 silver badges385 bronze badges

2 Comments

dlock Over a year ago

BETWEEN messes the code logic. expirydate shouldn't be between fromdate and curdata(). expirydate actually is checked to be greater than both fromdata and curdata(). It can be done with NOT BETWEEN: expirydate NOT BETWEEN fromdate AND curdate() AND expirydate NOT BETWEEN fromdate AND {basedate(maybe 0)} ORDER BY rand()

doub1ejack Over a year ago

Nice idea. Unfortunately appending those strings doesn't help either. That var ends up with "select * from tbl_adsinfo where expirydate >= curdate() AND fromdate <" - everything after the lessthan sign is cut off.

doub1ejack · Accepted Answer · 2011-03-17 15:25:02Z

Ok, I got it. The problem had nothing to do with PHP, it was Zend Studio and/or the XDebugger.

Getting Zend to use a debugger at all took me days and I'm sure it probably isn't entirely kosher the way I got it to run.

If you hover over a variable while debugging in Zend Studio a little window pops up to show you the contents of that variable (sometimes). This window does cut off anything that follows '<'. The variable still contains the correct string, but the IDE/Debugger is pretty misleading. Unfortunately since this particular character is a common problem because of html parsing this was a pretty confusing bug to identify. The issue is compounded if you try to compare output from a php page or if you are looking at very long output (for very long strings it seems you can only see 1024 characters in the debug variable window so the string may appear truncated).

Sadly, this is still the best IDE I've come up with for PHP so far.

Collectives™ on Stack Overflow

php stripping out part of string

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related