×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

IF statement and WHERE clause giving inconsitent results.

IF statement and WHERE clause giving inconsitent results.

IF statement and WHERE clause giving inconsitent results.

(OP)
Hi mates,

If I run the following query alone, I get the results I am after:




CODE

if (isset($_GET["bidStatus"])  && isset($_GET["bidType"])) {
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime, replace(b.BidTitle,',','-') BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status,
   			cat.Category,t.Team
             FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID left join Teams t on b.TeamID = t.TeamID left join Categories cat on b.CategoryID = cat.CategoryID inner join Dept d on da.DeptCode =d.DeptCode inner join Status s on b.BidStatus=s.StatusId where b.BidType = '".ms_escape_string($_GET["bidType"])."' AND b.BidStatus = '".ms_escape_string($_GET["bidStatus"])."'
   			) AS c
   	"; 

The result I am after in the query above is based on
where b.BidType = '".ms_escape_string($_GET["bidType"])."' AND b.BidStatus = '".ms_escape_string($_GET["bidStatus"])."'

However, if I run these two queries together:

CODE

if (isset($_GET["bidStatus"])  && $_GET['bidStatus'] != '' ) {
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime, replace(b.BidTitle,',','-') BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status,
   			cat.Category,t.Team
             FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID left join Teams t on b.TeamID = t.TeamID left join Categories cat on b.CategoryID = cat.CategoryID inner join Dept d on da.DeptCode =d.DeptCode inner join Status s on b.BidStatus=s.StatusId where b.BidStatus = '".ms_escape_string($_GET["bidStatus"])."'
   			) AS c
   	";
   }
     elseif (isset($_GET["bidStatus"])  && isset($_GET["bidType"])) {
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime, replace(b.BidTitle,',','-') BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status,
   			cat.Category,t.Team
             FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID left join Teams t on b.TeamID = t.TeamID left join Categories cat on b.CategoryID = cat.CategoryID inner join Dept d on da.DeptCode =d.DeptCode inner join Status s on b.BidStatus=s.StatusId where b.BidType = '".ms_escape_string($_GET["bidType"])."' AND b.BidStatus = '".ms_escape_string($_GET["bidStatus"])."'
   			) AS c
   	";
   } 

only the first IF statement where b.BidStatus = '".ms_escape_string($_GET["bidStatus"])."'
runs. The second is ignored.

Any ideas why?

RE: IF statement and WHERE clause giving inconsitent results.

(OP)
Ok, I moved the code around.

Instead of having them next to each other, I moved the second elseif further down and it seems to work now..

Doesn't make sense to me at all but it seems to be working now.

RE: IF statement and WHERE clause giving inconsitent results.

Your 2 conditionals are essentially excluding each other. Your first conditional checks to see if $_GET["bidStatus"] is set and not an empty string. If it is it will run it.

Having your second conditional as an elseif of the first one means it will only run if the first conditional evaluates to false, i.e either $_GET["bidStatus"] is not set or is in fact an empty string.
However, if $_GET["bidStatus"]is not set, then your else if will evaluate to false also, and not run the code inside it.

The Else if condition is only tested if the first if statement is not true. But when your first condition is not true, your else if condition is also not true. So the code inside it will not run.

If you separate the conditions into 2 Ifs rather than an if and elseif then they will both be tested independently.

The location of the second test is irrelevant, it just needs to not be an elseif of the first one.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: IF statement and WHERE clause giving inconsitent results.

(OP)
Ok,thanks for your response vacunita,

Question, more framed out of curiosity.

If I have this:

if (isset($_GET["bidStatus"]) && isset($_GET["bidType"])) {
....
;
}
elseif (isset($_GET["bidStatus"]) && $_GET['bidStatus'] != '' ) {
...
;
}
else
{};

What do you think happens?

In other words, if my querystring contains values for only bidStatus, don't you think that the first if will be ignored?

Alternatively, if my querystring has values for both bidStatus and bidType, then only results that meet the WHERE predicate for bidStatus and bidType will be displayed, no?

After rearranging the order of the IF statements, with bidStatus and bidType being evaluated first, I have not had any more issues.

I have tested with various test scenarios and it seems to be working.

Again, I am curious because I am missing something here.

RE: IF statement and WHERE clause giving inconsitent results.

Quote:


What do you think happens?

In other words, if my querystring contains values for only bidStatus, don't you think that the first if will be ignored?

Yes. If bidType is not set, then it will ignore that block, and move on to the elseif part.


Quote:


Alternatively, if my querystring has values for both bidStatus and bidType, then only results that meet the WHERE predicate for bidStatus and bidType will be displayed, no?

Again correct. If they are set, then it will run that code block but not the elseif part.

Quote:


After rearranging the order of the IF statements, with bidStatus and bidType being evaluated first, I have not had any more issues.
Yes, because in the event bidType is not set, bidStatus might still be. so the elseif part can evaluate to true and thus run the code inside it.


The way you had them before, you were testing for the existence of bidStatus first, and only if bisStatuis did not exist did you attempt to check for the existence of both bidStatus and bidType.

You see the issue? If bidStatus does not exist and you check for it again in your followup condition, its still not going to exist.

Basically the way you had it before, it was saying:

If bidStatus exists and is not empty, do something. If it does not exist or is empty, check again that it exists along with bidType. You were wanting something to exist when it was already established it did not exist.
And if it did exist, then that elseif statement would never run. since it would run the first IF block only.


if [A condition is met]
run some code
elseif [if previous condition was not met, test for this condition and if its met]
run other code
else [if previous conditions were not met]
run this third code.

In other words, in an [if elseif else] block only one of the sections will ever be run.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close