Find output of a query result

SuiteCRM Forum - English Language Developer Collaboration
1

Hello,
I am attempting to auto-generate an account code to help us uniquely identify each of our Accounts. I have created an After-Save Hook which generates a code and assigns it to an account, but I need it to also verify that the code being generated has not been previously generated and assigned to another account.
To do this I wanted to run a simple Select statement against existing accounts and see if it returns any records which match the newly generated account code. I have been able to run the query, but whenever I try to save the results to a variable and then save a record, I receive the message “This page isn’t working. Ipaddress is currently unable to handle this request. HTTP Error 500 “. What do I need to do to review the result to check for a duplicate? The account code is a custom code I created and is in the account_cstm database table. Following is the code I have so far:

       global $db;
      // temporarily assign the code a number to verify the code can determine if we have a duplicate. This number does not exist.
      $code = ‘091111’;
      if (empty($account->account_code_c)) {
             $codecheck = $db->query("select count(*) from accounts_cstm where account_code_c = '$code'");
             if (!$codecheck) {
                   $data .= " No Data Returned";
                 } else {
                   $data .= " Record Found. Generate another code ";
//Following is the code which causes my problem. If I take this out, then it doesn’t crash, but the results are incorrect. It should write No Data Returned to my output text file but it always fails the IF test and records that it was found.
                   $data .= $codecheck;
                 }
          }
file_put_contents($file,$data);

I also tried IF ($codecheck == 0) { and IF (empty($codecheck)) {, but no matter what condition I put in, it always shows that it returned something. I have tried returning an account_code_c instead of count(*) and testing to see if it was empty, but it still says that it found a record. I would like to know what it is returning.
If I run select account_code_c from accounts_cstm where account_code_c = '091111'; from the command line on the server, I receive:
Empty set (0.01 sec)

Thanks

2

Have you tried your query first in MySQL? for example via phpMyAdmin?

3

Yes, I am able to do it in MySQL. I have been able to verify I get a result, but can’t seem to use it in a condition. For example, after the $codecheck I added $result = $db->fetchByAssoc($codecheck)) and then used file_put_contents to put the $result to a file, the result in the file is correct, showing the count. However, when I use
if ($result == 0) {
$data .= "No Data Returned"
} else {
$data .= "Record Found"
}
it will always return that a record was found (whether the result is 0, 1, 6, or any other number). Not sure why I can show the correct $result when putting it to a file but can’t use it as a condition in my IF stmt. Just trying to see if the new code I created already exists.

4

per https://stackoverflow.com/questions/6907751/select-count-from-table-of-mysql-in-php
I had to use Select count(*) as total for my query.
then I had to update my IF stmt to if( $result[‘total’] == 0) {
This worked for me.
Hope this helps someone else. Let me know if you have any questions.

Thanks

1 Like