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,

Parents

0 Mehul Bhandari over 9 years ago

Hi
try this ...
global $db;
$res = $db->query(" YOUR QUERY");
0 Roman R. over 9 years ago in reply to Mehul Bhandari

if(!emptyempty($check))
emptyempty - this is error in your code or just typo in the message?
0 Roland Cadavos over 9 years ago in reply to Roman R.

Hi Autchara Chaiprom,
Can you paste the whole function of your code? so we could know if you are really getting the correct parameters.
Thanks,
Longki
0 Angel Magana over 9 years ago in reply to Roland Cadavos

What is $focus set to?
If you are doing this in a hook associated with the Cases module, you should be using $bean instead of $focus. For example, $bean->id instead of $focus->id.
This assumes your method is defined similar to the following:
function myQueryMethodForHook($bean)
{
//The query code
}
0 Autchara Chaiprom over 9 years ago in reply to Angel Magana

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 ??
0 Sheikh Rahat Ali over 9 years ago in reply to Angel Magana

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.
0 Autchara Chaiprom over 9 years ago in reply to Sheikh Rahat Ali

Yes I always print log to check.
Do you think it may problem from logic hook event like before/after save ?
0 David López over 9 years ago in reply to Sheikh Rahat Ali

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.

Reply

0 David López over 9 years ago in reply to Sheikh Rahat Ali

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.

Children

0 Autchara Chaiprom over 9 years ago in reply to David López

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.
0 Autchara Chaiprom over 9 years ago in reply to David López

Now I know why no data return because cases still on creating and when I query in before save no data return case and email not create relation. So easier solution for me is modified core instance of Sugar, bad solution but work in InboundeEmail create case.