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

Hi

try this ...

global $db;

$res = $db->query(" YOUR QUERY");    

if(!emptyempty($check))

emptyempty - this is error in your code or just typo in the message?

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

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

}

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 ??

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.

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.