Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
AddWhere sqlbuilder function
How do i create double parenthesis
I want this sql code
notice their are 2 parenthesis after the first AND logic
I have this so far
which gives me this
cannot use $sql->StartWhereGroup() as below
cause then the AND logic disappears. and i get this
which isn't a valid query. Its because AddWhere function won't add the AppendMethod (AND) if it detects a starting parenthesis, Thats why using $sql->StartWhereGroup() makes the query invalid.
here is the offending code in the AddWhere()
this is correct if you DON'T have any existing WHERE and a fresh where group is being started, However, if their is an existing WHERE than this function results in invalid query
I want this sql code
WHERE (CalendarRoleBlock.Blocked = '0' or CalendarRoleBlock.Blocked = 0 or CalendarRoleBlock.Blocked is null )
AND ((Event.StartDate >= '2007-11-01' AND Event.StartDate <= '2007-11-30' ) OR (Event.EndDate >= '2007-11-01' AND Event.EndDate <= '2007-11-30' ))
notice their are 2 parenthesis after the first AND logic
I have this so far
$sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 0, '=', 'and', '', 1, 1);
$sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 0, '=', 'or', '', 0);
$sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 'null', 'is', 'or', '', 0);
$sql->EndWhereGroup();
$sql->AddWhere('Event', 'StartDate', '', '2007-11-01' , '>=', 'AND', '', 1,1);
$sql->AddWhere('Event', 'StartDate', '', '2007-11-30', '<=', 'AND', '', 1,0);
$sql->EndWhereGroup();
$sql->AddWhere('Event', 'EndDate', '', '2007-11-01', '>=', 'OR', '', 1,1);
$sql->AddWhere('Event', 'EndDate', '', '2007-11-30', '<=', 'AND', '', 1,0);
$sql->EndWhereGroup();
which gives me this
WHERE (CalendarRoleBlock.Blocked = '0' or CalendarRoleBlock.Blocked = 0 or CalendarRoleBlock.Blocked is null )
AND (Event.StartDate >= '2007-11-01' AND Event.StartDate <= '2007-11-30' ) OR (Event.EndDate >= '2007-11-01' AND Event.EndDate <= '2007-11-30' )
cannot use $sql->StartWhereGroup() as below
$sql->StartWhereGroup() ;
$sql->AddWhere('Event', 'StartDate', '', '2007-11-01' , '>=', 'AND', '', 1,1);
$sql->AddWhere('Event', 'StartDate', '', '2007-11-30', '<=', 'AND', '', 1,0);
$sql->EndWhereGroup();
$sql->AddWhere('Event', 'EndDate', '', '2007-11-01', '>=', 'OR', '', 1,1);
$sql->AddWhere('Event', 'EndDate', '', '2007-11-30', '<=', 'AND', '', 1,0);
$sql->EndWhereGroup();
$sql->EndWhereGroup();cause then the AND logic disappears. and i get this
WHERE (CalendarRoleBlock.Blocked = '0' or CalendarRoleBlock.Blocked = 0 or CalendarRoleBlock.Blocked is null )
((Event.StartDate >= '2007-11-01' AND Event.StartDate <= '2007-11-30' ) OR (Event.EndDate >= '2007-11-01' AND Event.EndDate <= '2007-11-30' ))which isn't a valid query. Its because AddWhere function won't add the AppendMethod (AND) if it detects a starting parenthesis, Thats why using $sql->StartWhereGroup() makes the query invalid.
here is the offending code in the AddWhere()
if (!empty($sWheres) && substr($sWheres,strlen($sWheres)-1) != '(') {
$sWheres .= $AppendMethod.' ';
}this is correct if you DON'T have any existing WHERE and a fresh where group is being started, However, if their is an existing WHERE than this function results in invalid query
0
This discussion has been closed.
Comments
$sql->StartWhereGroup() ; $sql->AddWhere('Event', 'StartDate', '', '2007-11-01' , '>=', 'AND', '', 1,1); $sql->AddWhere('Event', 'StartDate', '', '2007-11-30', '<=', 'AND', '', 1,0); $sql->EndWhereGroup(); $sql->AddWhere('Event', 'EndDate', '', '2007-11-01', '>=', 'OR', '', 1,1); $sql->AddWhere('Event', 'EndDate', '', '2007-11-30', '<=', 'AND', '', 1,0); $sql->EndWhereGroup(); $sql->EndWhereGroup(); $sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 0, '=', 'and', '', 1, 1); $sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 0, '=', 'or', '', 0); $sql->AddWhere('CalendarRoleBlock', 'Blocked', '', 'null', 'is', 'or', '', 0); $sql->EndWhereGroup();?It **may be** get
WHERE ((Event.StartDate >= '2007-11-01' AND Event.StartDate <= '2007-11-30' ) OR (Event.EndDate >= '2007-11-01' AND Event.EndDate <= '2007-11-30' )) AND (CalendarRoleBlock.Blocked = '0' or CalendarRoleBlock.Blocked = 0 or CalendarRoleBlock.Blocked is null ), sorry I didn't try, just guess.they should both append nicely. Its a bug in AddWhere
here is the offending code in the AddWhere()
if (!empty($sWheres) && substr($sWheres,strlen($sWheres)-1) != '(') { $sWheres .= $AppendMethod.' '; }this is correct if you DON'T have any existing WHERE and a fresh where group is being started, However, if their is an existing WHERE than this function results in invalid query by NOT adding the necessary append method
So the fucntion needs to be changed so it works for "WHERE (blah) AND (" but not this "WHERE ("
no need to detect the opening parenthesis. without it we will get this "WHERE AND blah", which is wrong but then i won't use AND in my AddWhere function parameters.
Simple either get ride of it, which will might break existing code that uses an appendmethod when their is no existing clause (sloppy coding)
or figure out a more complex way of checking that allows an opening bracket if a closing bracket already exists, essentially looking for ") ("
I just think, if the code in GetWheres() which
if ($StartWhereGroup) $sWheres .= '(';change toif ($StartWhereGroup) $sWheres .= str_repeat('(', $StartWhereGroup);and remove$StartWhereGroup = ForceBool($StartWhereGroup, 0);We can create multi-parenthesis as wish use AddWhere. (but it may be ugly.)
function StartWhereGroup($AppendMethod = '') { $this->Wheres[] = ' ' . $AppendMethod . ' ('; }Then use StartWhereGroup as many times as necessary to get the desired groupings.