IF statement and WHERE clause giving inconsitent results.

[Kaylech]

Hi mates,

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

     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:

     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?

 

[Kaylech]

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.

 

[vacunita]

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

 

[Kaylech]

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.

 

[vacunita]

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.

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.

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