Query not return any result in logic hook but query work in MySQL workbench

My code in Cases module hook:

$sql = "select c.id from cases c
        left join emails e on e.parent_id = c.id and e.parent_type = 'Cases'
        left join emails_text et on et.email_id = e.id
        where c.id = '{$focus->id}' and et.to_addrs like '%_string_%'";
$result = $focus->db->query($sql,true);
$check =  $focus->db->fetchByAssoc($result);
if(!empty($check)){
     $GLOBALS['log']->fatal("Hello World ".$check);
}

I search solution in google many people suggest to using SugarQuery but it not more complex for my query or I do wrong with my code?

Please suggest me for solution.

Sugar 7.6 Ent

Thanks,

M

  • I using like this: It work for other case.

    function myQueryMethodForHook($focus)

    {

         //The query code

    }

    in this case I should not using $focus but should be $bean ??

  • I do like this, it's work log sql then run in workbench.

  • When email incoming

    then create case but for some email address should be set to specify user 

    I create case hook(before save) to re-assigned to user.

    function schedule(&$focus, $event){

    if($focus->team_id == '_teamid_'){

                            unset($sql);

                            $sql = "SELECT c.name as 'cname' FROM cases c ".

                                    "LEFT JOIN emails e ON e.parent_id = c.id and e.parent_type = 'Cases' ".

                                    "LEFT JOIN emails_text et ON et.email_id = e.id ".

                                    "WHERE c.id = '{$focus->id}' AND et.to_addrs LIKE '%_emailaddr_%' ";

                            $check = $focus->db->query($sql,true);

                            while($row = $focus->db->fetchByAssoc($check)){

                                    $name = $row['cname'];

                                    $GLOBALS['log']->fatal("Get:  ".$row['cname']);

                            }

                            $GLOBALS['log']->fatal('Get: '.$name .' SQL: ' . $sql);

                            if(!empty($name)){

                                    $GLOBALS['log']->fatal('Yes');

                                    $focus->assigned_user_id = '_userid_';

                            }else{

                                    $GLOBALS['log']->fatal('Not set');

                            }

                    }

    }

    I copy sql in log then run in workbench Its work but when I try to fetch value from sql data always null

  • Have you tried to log the values passed to the query as variables like $focus->id for confirming that the data passed to the query is valid and in correct format and are you searching for emails containg '%_emailaddr_%' as text in its toit becuase if i am not getting it wrong it seems to me as a string literal instead of a variable name.

  • Yes I always print log to check.

    Do you think it may problem from logic hook event like before/after save ?

  • I always do queries to the database like this and they work properly:


    <?php

    global $sugar_config;

    $server = $sugar_config['dbconfig']['db_host_name'];

    $user = $sugar_config['dbconfig']['db_user_name'];

    $password = $sugar_config['dbconfig']['db_password'];

    $db = $sugar_config['dbconfig']['db_name'];

    $db_handle = mysqli_connect( $server, $user, $password, $db );

    if( !$db_handle ) {

       echo "Connection to DB failure";

       $GLOBALS['log']->fatal('Connection to DB failure');

    }

    else {

         //Here your custom code

         $sql = 'SELECT ...'; //Your SQL Query

         $res = mysqli_query( $db_handle, $sql );

         if( $res ) {

           $GLOBALS['log']->fatal('Query successful');

                

              //Loop the result

              while( $row = mysqli_fetch_array( $res ) ) {

                   //...

              }

         }

         else {

            $GLOBALS['log']->fatal('Query failed');

         }

    }

    ?>

    Hope that helps.

    David.

  • Thanks,

    But I have already one I try with another method by using CURL and return JSON then I decode JSON into value.

    <?php

    include('../config.php');

    //JSON function...

    mysql_connect($sugar_config['dbconfig']['db_host_name'], $sugar_config['dbconfig']['db_user_name'], $sugar_config['dbconfig']['db_password']) or die(mysql_error());

    mysql_select_db($sugar_config['dbconfig']['db_name']) or die(mysql_error());

    mysql_query("SET NAMES UTF8");

    unset($sql);

                            $sql = "SELECT count(*) as 'cc' FROM cases c ".

                                    "LEFT JOIN emails e ON e.parent_id = c.id and e.parent_type = 'Cases' ".

                                    "LEFT JOIN emails_text et ON et.email_id = e.id ".

                                    "WHERE c.id = '{$id}' AND et.to_addrs LIKE '%_emailaddr_%' ";

            $result = mysql_query($sql);

                    while($row = mysql_fetch_array($result)){

                    $Json->add('cc',$row['cc']);

            }

    When I run It's work perfect but when I put in logic hook not work.

    copy CURL url and run return 1 but when using logic hook return 0 or It might not any data.

  • Hi Autchara Chaiprom,

    Insert global $db; at first line of the function

    HTH,

    Longki

  • I try with after_save and add global $db, both not work.

    I think might be from event time or functional somewhere.

    I will try another solution. Now I try to use CURL get return value from another page.

    Thanks for your help