Re: DB_DataObject - Brakcets with Where Add

On 9/21/06, Ian Warner <iwarner@triangle-solutions.com> wrote:

Hi When I use the functions: $sql1 = DB_DataObject::factory(TBL_SMS_CENTER_STATIC); $sql1->whereAdd($sql1->__table . '.service_id = '. $_GET['service_id']);

         if (isset($_SESSION['view_user'])) {
             $sql1->whereAdd($table . '.user_id = ' .

$_SESSION['view_user'] .'(');

         } else {
             $sql1->whereAdd($table . '.user_id = ' .

$_SESSION['admin_user_id']);

             if (count($_SESSION['admin_user_id_child']) > 1) {
                 foreach ($_SESSION['admin_user_id_child'] AS $row_sub) {
                     if ($_SESSION['admin_user_id'] != $row_sub) {
                         $sql1->whereAdd($table . '.user_id =  ' .

$row_sub, 'OR');

                     }
                 }
             }
         }

I get the following SQL make up - now the issue is the brackets with the OR SELECT * FROM sms_static WHERE ( sms_static.service_id = 1 ) AND (sms_static.user_id = 1 ) OR ( sms_static.user_id = 2 ) OR ( sms_static.user_id = 24 ) I really want SELECT * FROM sms_static WHERE ( sms_static.service_id = 1 ) AND (sms_static.user_id = 1 OR sms_static.user_id = 2 OR sms_static.user_id = 24) This way I get the correct effect, of not selecting all the items based on the serivce id or the user ids - I want serviceID that pertains to these users so someone cannot hack in a service id. Is there any way to add this logic in or do I need to hack it and add brackets in myself - if so how do I do that: I can hack it by $sql1 = DB_DataObject::factory(TBL_SMS_CENTER_STATIC); $sql1->whereAdd($sql1->__table . '.service_id = '. $_GET['service_id']);

         if (isset($_SESSION['view_user'])) {
             $sql1->whereAdd('(' . $table . '.user_id = ' .

$_SESSION['view_user'] .'(');

         } else {
             $sql1->whereAdd('(' . $table . '.user_id = ' .

$_SESSION['admin_user_id']);

             if (count($_SESSION['admin_user_id_child']) > 1) {
                 foreach ($_SESSION['admin_user_id_child'] AS $row_sub) {
                     if ($_SESSION['admin_user_id'] != $row_sub) {
                         $sql1->whereAdd($table . '.user_id =  ' .

$row_sub, 'OR');

                     }
                 }

                 $sql1->whereAdd('1 )');
             }
         }

which gives: SELECT * FROM sms_static WHERE ( sms_static.service_id = 1 ) AND ( (sms_static.user_id = 1 ) OR ( sms_static.user_id = 2 ) OR ( sms_static.user_id = 24 ) AND ( 1 ) )

You're not seeing the forest for the trees here..... You don't have to (in fact, shouldn't) call whereAdd for *every* condition. Loop over your OR clauses and create a string with them combined with an OR then whereAdd() the whole thing at once. $or = ''; for ($i = 0; $i < 3; ++$i) { $or .= ($or ? ' OR ' : '').'field = '.$i; } $do->whereAdd($or); -- Justin Patrin